J
jim hardwick
I have a query which I wnat to run between two dates (yyyy-mm-dd hh.mm.ss
format) which are in two specified cells on a workbook, tried the routine
below but when macro gets to " .Refresh BackgroundQuery:=False" line at
bottom I get Run Time error 1004 - SQL Syntax Error (Full code below).
Please help.
Sub GetData()
'
' GetData Macro
' Macro recorded 30/11/2007 by
'
Worksheets("Push Data").Activate
Dim StartDate As Date, EndDate As Date
StartDate = Range("A1").Value
EndDate = Range("A2").Value
With ActiveSheet.QueryTables.Add(Connection:= _
"ODBC;DSN=DL BATT;UID=sa;PWD=panelview900;APP=Microsoft®
Query;WSID=DGD7K22J;DATABASE=SerialLinks" _
, Destination:=Range("A3"))
.CommandText = Array( _
"SELECT vwOvenData.PushDateTime, vwOvenData.OvenID,
vwOvenData.MaxPush1, vwOvenData.MaxPush2" & Chr(13) & "" & Chr(10) & "FROM
SerialLinks.dbo.vwOvenData vwOvenData" & Chr(13) & "" & Chr(10) & "WHERE
(vwOvenData.PushDateTime>={'" & Format(StartDate, "yyyy-mm-dd hh:mm:ss") &
"'} And " _
, _
"vwOvenData.PushDateTime<{'" & Format(EndDate, "yyyy-mm-dd
hh:mm:ss") & "'})" & Chr(13) & "" & Chr(10) & "ORDER BY
vwOvenData.PushDateTime" _
)
.Name = "Query from DL BATT"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = False
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
End Sub
format) which are in two specified cells on a workbook, tried the routine
below but when macro gets to " .Refresh BackgroundQuery:=False" line at
bottom I get Run Time error 1004 - SQL Syntax Error (Full code below).
Please help.
Sub GetData()
'
' GetData Macro
' Macro recorded 30/11/2007 by
'
Worksheets("Push Data").Activate
Dim StartDate As Date, EndDate As Date
StartDate = Range("A1").Value
EndDate = Range("A2").Value
With ActiveSheet.QueryTables.Add(Connection:= _
"ODBC;DSN=DL BATT;UID=sa;PWD=panelview900;APP=Microsoft®
Query;WSID=DGD7K22J;DATABASE=SerialLinks" _
, Destination:=Range("A3"))
.CommandText = Array( _
"SELECT vwOvenData.PushDateTime, vwOvenData.OvenID,
vwOvenData.MaxPush1, vwOvenData.MaxPush2" & Chr(13) & "" & Chr(10) & "FROM
SerialLinks.dbo.vwOvenData vwOvenData" & Chr(13) & "" & Chr(10) & "WHERE
(vwOvenData.PushDateTime>={'" & Format(StartDate, "yyyy-mm-dd hh:mm:ss") &
"'} And " _
, _
"vwOvenData.PushDateTime<{'" & Format(EndDate, "yyyy-mm-dd
hh:mm:ss") & "'})" & Chr(13) & "" & Chr(10) & "ORDER BY
vwOvenData.PushDateTime" _
)
.Name = "Query from DL BATT"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = False
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
End Sub