Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
John
 
Posts: n/a
Default Problem with Query

I am only a novice at code but have recorded and then added bits to the
following code which extracts data from a MS Access database. I have placed
it with 'This Workbook' to fire on open and everything works correctly. My
problem starts is when I try to run the code again (to simply refresh the
data) when the following actions will not run i.e. it does not copy down to
cells below

x = 2
Do Until Cells(x, 7).Value = ""
Cells(x, 19).PasteSpecial xlPasteFormulas
x = x + 1
Loop

The full code (which is exactly) what fires on open - and works great is as
follows). Are there parts to this code which I don't need, maybe that is
stopping the code (refresh) from working? That said everthing within the
'refresh' code works except the part that copies down the formula from S2.
BTW there is data in G2:G.

I have posted in our groups but I'm lost and baffled as to why the code will
work in fully on opening but not when forced

Thanks



Sub Refresh_Timepoint()
Sheets("Database").Select
Cells.Select
Selection.ClearContents
Range("A1").Select
Sheets("Database").Select
With ActiveSheet.QueryTables.Add(Connection:=Array(Arra y( _

"ODBC;DBQ=C:\timepoint\Timepoint_be.MDB;DefaultDir =C:\timepoint;Driver={Micr
osoft Access Driver (*.mdb)};DriverId=281;FIL=MS Access;M" _
), Array( _

"axBufferSize=2048;MaxScanRows=8;PageTimeout=5;Saf eTransactions=0;Threads=3;
UID=admin;UserCommitSync=Yes;" _
)), Destination:=Range("A1"))
.CommandText = Array( _
"SELECT Employees.StaffNum, Employees.DeptNum, Employees.PayrollNum,
Employees.ContractType, Employees.EmployeeType, Employees.Forename,
Employees.Surname, Employees.EmpAddress1, Employees.EmpAddress2," _
, _
" Employees.EmpAddress3, Employees.EmpAddress4,
Employees.DateOfBirth, Employees.TerminationDate,
Employees.TerminationPeriod, Employees.CommencementDate,
Employees.CommencementPeriod, Employees.PayRat" _
, _
"e, Employees.NatInsNum" & Chr(13) & "" & Chr(10) & "FROM
`C:\timepoint\Timepoint_be`.Employees Employees" & Chr(13) & "" & Chr(10) &
"ORDER BY Employees.Surname" _
)
.Name = "Query from Timepoint"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = True
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=True
End With

Application.ScreenUpdating = False

With Application
.Calculation = xlManual
.MaxChange = 0.001
End With

Sheets("Database").Select

Range("A1").Select

Columns("L:M").Select
Application.CutCopyMode = False
Selection.NumberFormat = "DD/MM/YY"

Columns("N:N").Select
Application.CutCopyMode = False
Selection.NumberFormat = "####-##"

Columns("o:o").Select
Application.CutCopyMode = False
Selection.NumberFormat = "DD/MM/YY"

Columns("P:P").Select
Application.CutCopyMode = False
Selection.NumberFormat = "####-##"

Columns("Q:Q").Select
Application.CutCopyMode = False
Selection.NumberFormat = "?#,##0.00"



Columns("B:B").Select
Selection.Replace What:="1", Replacement:="Crew", LookAt:=xlPart _
, SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="99", Replacement:="Mgr", LookAt:=xlPart _
, SearchOrder:=xlByRows, MatchCase:=False

Columns("D:D").Select
Selection.Replace What:="10", Replacement:="Crew F/T", LookAt:=xlPart _
, SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="11", Replacement:="Crew P/T", LookAt:=xlPart _
, SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="12", Replacement:="Mgr F/T", LookAt:=xlPart _
, SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="13", Replacement:="Mgr P/T", LookAt:=xlPart _
, SearchOrder:=xlByRows, MatchCase:=False

ActiveWorkbook.PrecisionAsDisplayed = False
Sheets("Database").Select
Range("S2").Select
ActiveCell.Formula = "=PROPER(F2&"" ""&G2)"

Range("S2.S2").Copy
x = 2
Do Until Cells(x, 7).Value = ""
Cells(x, 19).PasteSpecial xlPasteFormulas
x = x + 1
Loop

With Application
.Calculation = xlAutomatic
.MaxChange = 0.001
End With
ActiveWorkbook.PrecisionAsDisplayed = False
Application.ScreenUpdating = True

Sheets("Database").Select
Range("A1").Select
Sheets("Home").Select
Range("A1").Select

End Sub


  #2   Report Post  
Bill Manville
 
Posts: n/a
Default

John wrote:
Range("S2.S2").Copy


That statement should cause an error.
Range("S2").Copy
would be fine.

You have asked it to carry out the query in the background which means
that the query may not have completed by the time you try to copy and
paste the formula.

The code could be tightened up quite a bit as there is quite a lot of
unnecessary selecting being done, but that's the way the macro recorder
works.

For the record, I would reduce it to something like this:

Sub Refresh_Timepoint()
Sheets("Database").Select
ActiveSheet.UsedRange.ClearContents
With ActiveSheet.QueryTables.Add( _
Connection:="ODBC;DBQ=C:\timepoint\Timepoint_be.MD B", _
Destination:=Range("A1"))
.CommandText = "SELECT StaffNum, DeptNum, PayrollNum, " & _
"ContractType, EmployeeType, Forename, Surname, EmpAddress1, " & _
"EmpAddress2, EmpAddress3, EmpAddress4, DateOfBirth, " & _
"TerminationDate, TerminationPeriod, CommencementDate, " & _
"CommencementPeriod, PayRate, NatInsNum " & _
"FROM Employees ORDER BY Surname"
.Name = "Query from Timepoint"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = True
.BackgroundQuery = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With

Application.ScreenUpdating = False

Application.Calculation = xlManual

Columns("L:M").NumberFormat = "DD/MM/YY"

Columns("N:N").NumberFormat = "####-##"

Columns("o:o").NumberFormat = "DD/MM/YY"

Columns("P:P").NumberFormat = "####-##"

Columns("Q:Q").NumberFormat = "?#,##0.00"

With Columns("B:B")
.Replace What:="1", Replacement:="Crew", LookAt:=xlPart _
, SearchOrder:=xlByRows, MatchCase:=False
.Replace What:="99", Replacement:="Mgr", LookAt:=xlPart _
, SearchOrder:=xlByRows, MatchCase:=False
End With

With Columns("D:D")
.Replace What:="10", Replacement:="Crew F/T", LookAt:=xlPart _
, SearchOrder:=xlByRows, MatchCase:=False
.Replace What:="11", Replacement:="Crew P/T", LookAt:=xlPart _
, SearchOrder:=xlByRows, MatchCase:=False
.Replace What:="12", Replacement:="Mgr F/T", LookAt:=xlPart _
, SearchOrder:=xlByRows, MatchCase:=False
.Replace What:="13", Replacement:="Mgr P/T", LookAt:=xlPart _
, SearchOrder:=xlByRows, MatchCase:=False
End With

Range("S2", Cells(Range("G2").End(xlDown).Row, "S")).Formula = _
"=PROPER(F2&"" ""&G2)"

Application.Calculation = xlAutomatic

Sheets("Home").Select
Range("A1").Select

Application.ScreenUpdating = True

End Sub


Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

  #3   Report Post  
John
 
Posts: n/a
Default

Thanks Bill

I have given up, broken it down in to an 'add query' and then a Refresh one.
I had been running the 'add' over and over again, but all I required
apparently was refresh. Thanks for the tightened code


"Bill Manville" wrote in message
...
John wrote:
Range("S2.S2").Copy


That statement should cause an error.
Range("S2").Copy
would be fine.

You have asked it to carry out the query in the background which means
that the query may not have completed by the time you try to copy and
paste the formula.

The code could be tightened up quite a bit as there is quite a lot of
unnecessary selecting being done, but that's the way the macro recorder
works.

For the record, I would reduce it to something like this:

Sub Refresh_Timepoint()
Sheets("Database").Select
ActiveSheet.UsedRange.ClearContents
With ActiveSheet.QueryTables.Add( _
Connection:="ODBC;DBQ=C:\timepoint\Timepoint_be.MD B", _
Destination:=Range("A1"))
.CommandText = "SELECT StaffNum, DeptNum, PayrollNum, " & _
"ContractType, EmployeeType, Forename, Surname, EmpAddress1, " & _
"EmpAddress2, EmpAddress3, EmpAddress4, DateOfBirth, " & _
"TerminationDate, TerminationPeriod, CommencementDate, " & _
"CommencementPeriod, PayRate, NatInsNum " & _
"FROM Employees ORDER BY Surname"
.Name = "Query from Timepoint"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = True
.BackgroundQuery = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With

Application.ScreenUpdating = False

Application.Calculation = xlManual

Columns("L:M").NumberFormat = "DD/MM/YY"

Columns("N:N").NumberFormat = "####-##"

Columns("o:o").NumberFormat = "DD/MM/YY"

Columns("P:P").NumberFormat = "####-##"

Columns("Q:Q").NumberFormat = "?#,##0.00"

With Columns("B:B")
.Replace What:="1", Replacement:="Crew", LookAt:=xlPart _
, SearchOrder:=xlByRows, MatchCase:=False
.Replace What:="99", Replacement:="Mgr", LookAt:=xlPart _
, SearchOrder:=xlByRows, MatchCase:=False
End With

With Columns("D:D")
.Replace What:="10", Replacement:="Crew F/T", LookAt:=xlPart _
, SearchOrder:=xlByRows, MatchCase:=False
.Replace What:="11", Replacement:="Crew P/T", LookAt:=xlPart _
, SearchOrder:=xlByRows, MatchCase:=False
.Replace What:="12", Replacement:="Mgr F/T", LookAt:=xlPart _
, SearchOrder:=xlByRows, MatchCase:=False
.Replace What:="13", Replacement:="Mgr P/T", LookAt:=xlPart _
, SearchOrder:=xlByRows, MatchCase:=False
End With

Range("S2", Cells(Range("G2").End(xlDown).Row, "S")).Formula = _
"=PROPER(F2&"" ""&G2)"

Application.Calculation = xlAutomatic

Sheets("Home").Select
Range("A1").Select

Application.ScreenUpdating = True

End Sub


Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup



  #4   Report Post  
John
 
Posts: n/a
Default

Bill

Just trying to run your code, and a message comes back saying its looking
for the Data Source. One thing that is different with your code is the
following, which it doesn't have, does the code need it?

DefaultDir=C:\timepoint;Driver={Microsoft Access Driver
(*.mdb)};DriverId=281;FIL=MS Access;M" _), Array( _

"axBufferSize=2048;MaxScanRows=8;PageTimeout=5;Saf eTransactions=0;Threads=3;
UID=admin;UserCommitSync=Yes;" _
)),


"Bill Manville" wrote in message
...
John wrote:
Range("S2.S2").Copy


That statement should cause an error.
Range("S2").Copy
would be fine.

You have asked it to carry out the query in the background which means
that the query may not have completed by the time you try to copy and
paste the formula.

The code could be tightened up quite a bit as there is quite a lot of
unnecessary selecting being done, but that's the way the macro recorder
works.

For the record, I would reduce it to something like this:

Sub Refresh_Timepoint()
Sheets("Database").Select
ActiveSheet.UsedRange.ClearContents
With ActiveSheet.QueryTables.Add( _
Connection:="ODBC;DBQ=C:\timepoint\Timepoint_be.MD B", _
Destination:=Range("A1"))
.CommandText = "SELECT StaffNum, DeptNum, PayrollNum, " & _
"ContractType, EmployeeType, Forename, Surname, EmpAddress1, " & _
"EmpAddress2, EmpAddress3, EmpAddress4, DateOfBirth, " & _
"TerminationDate, TerminationPeriod, CommencementDate, " & _
"CommencementPeriod, PayRate, NatInsNum " & _
"FROM Employees ORDER BY Surname"
.Name = "Query from Timepoint"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = True
.BackgroundQuery = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With

Application.ScreenUpdating = False

Application.Calculation = xlManual

Columns("L:M").NumberFormat = "DD/MM/YY"

Columns("N:N").NumberFormat = "####-##"

Columns("o:o").NumberFormat = "DD/MM/YY"

Columns("P:P").NumberFormat = "####-##"

Columns("Q:Q").NumberFormat = "?#,##0.00"

With Columns("B:B")
.Replace What:="1", Replacement:="Crew", LookAt:=xlPart _
, SearchOrder:=xlByRows, MatchCase:=False
.Replace What:="99", Replacement:="Mgr", LookAt:=xlPart _
, SearchOrder:=xlByRows, MatchCase:=False
End With

With Columns("D:D")
.Replace What:="10", Replacement:="Crew F/T", LookAt:=xlPart _
, SearchOrder:=xlByRows, MatchCase:=False
.Replace What:="11", Replacement:="Crew P/T", LookAt:=xlPart _
, SearchOrder:=xlByRows, MatchCase:=False
.Replace What:="12", Replacement:="Mgr F/T", LookAt:=xlPart _
, SearchOrder:=xlByRows, MatchCase:=False
.Replace What:="13", Replacement:="Mgr P/T", LookAt:=xlPart _
, SearchOrder:=xlByRows, MatchCase:=False
End With

Range("S2", Cells(Range("G2").End(xlDown).Row, "S")).Formula = _
"=PROPER(F2&"" ""&G2)"

Application.Calculation = xlAutomatic

Sheets("Home").Select
Range("A1").Select

Application.ScreenUpdating = True

End Sub


Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup



  #5   Report Post  
Bill Manville
 
Posts: n/a
Default

John wrote:
One thing that is different with your code is the
following, which it doesn't have, does the code need it?


Most of the connection string can be omitted as they are default values
anyway. Looks like I was a bit too mean with what I left in!

Try adding
;Driver={Microsoft Access Driver (*.mdb)};UID=Admin
back into the connection string

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Problem with Chart Title Formatting KvnLynch Charts and Charting in Excel 1 January 12th 05 04:15 AM
Using Microsoft Query data in a pivot table. slapana Charts and Charting in Excel 0 January 11th 05 06:03 PM
Problem with xlusrgal.xls file Alfred S C Lee Charts and Charting in Excel 2 December 29th 04 06:54 PM
Problem with date base units for x axis Peter Carr Charts and Charting in Excel 1 December 15th 04 10:11 AM
Problem drawing lines on charts Susan Lambert Charts and Charting in Excel 7 December 13th 04 09:29 AM


All times are GMT +1. The time now is 11:17 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"