Problem with varaible dates in SQL query
 

I have a macro in Excel that will import data from a SQL Table this data I
want to be data between two dates/times. Need some code to set the
two dates in two seperate cells on a worksheet and then run the macro. I
have tried posting below from MegaWatt but get compile errors.
Included is the original the code;

'
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={ts '2007-11-29 06:00:00'} And " _
, _
"vwOvenData.PushDateTime<{ts '2007-11-30 06:00:00'})" & 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 = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
End Sub

Problem with varaible dates in SQL query
 

Hello Jim

That looks like recorded macro code - on which line does it error?

Richard

On 3 Dec, 16:07, jim hardwick
wrote:
I have a macro in Excel that will import data from a SQL Table this data I
want to be data between two dates/times. Need some code to set the
two dates in two seperate cells on a worksheet and then run the macro. I
have tried posting below from MegaWatt but get compile errors.
Included is the original the code;

'
With ActiveSheet.QueryTables.Add(Connection:= _
"ODBC;DSN=DL BATT;UID=sa;PWD=panelview900;APP=Microsoft(R)
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={ts '2007-11-29 06:00:00'} And " _
, _
"vwOvenData.PushDateTime<{ts '2007-11-30 06:00:00'})" & 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 = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
End Sub

Problem with varaible dates in SQL query
 

Full Code below - in this version I've included the extra code to use adtes
set in the workbook - error comes from area within ** - Starts at CommandText
= Array( _

Sub GetData()
'
' GetData Macro
' Macro recorded 30/11/2007 by
'
Worksheets("Push Data").Activate
'
Dim s As String, s1 As String
With Worksheets("Push Data")
s = Format(.Range("A1").Value, "yyyy-mm-dd hh:mm:ss")
s2 = Format(.Range("A2").Value, "yyyy-mm-dd hh:mm:ss")
End With

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={ts'" & s & _
"'} And
vwOvenData.PushDateTime<{ts'" & s1 & "'})" & 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 = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
End Sub


"RichardSchollar" wrote:

Hello Jim

That looks like recorded macro code - on which line does it error?

Richard

On 3 Dec, 16:07, jim hardwick
wrote:
I have a macro in Excel that will import data from a SQL Table this data I
want to be data between two dates/times. Need some code to set the
two dates in two seperate cells on a worksheet and then run the macro. I
have tried posting below from MegaWatt but get compile errors.
Included is the original the code;

'
With ActiveSheet.QueryTables.Add(Connection:= _
"ODBC;DSN=DL BATT;UID=sa;PWD=panelview900;APP=Microsoft(R)
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={ts '2007-11-29 06:00:00'} And " _
, _
"vwOvenData.PushDateTime<{ts '2007-11-30 06:00:00'})" & 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 = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
End Sub