How do I use variable dates with a SQL Query

[Guest]

I recorded a macro in Excel that will import data from a SQL Table . The
data imprted will be between two dates. Does anyone know how I can set the
two dates in two seperate cells on a worksheet and then run the macro. I
have tried several things and had no luck. I included the code with this
message;

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 5/18/2006
'

With ActiveSheet.QueryTables.Add(Connection:= _
"ODBC;DSN=sqlserver;UID=sa;PWD=sa;APP=Microsoft Office
XP;WSID=SGRENELL-XP;DATABASE=Coal" _
, Destination:=Range("A1"))
.CommandText = Array( _
"SELECT coal_data.date, coal_data.truck_number,
coal_data.delivery_no, coal_data.Tare, coal_data.Gross, coal_data.Netlbs,
coal_data.NetTons" & Chr(13) & "" & Chr(10) & "FROM coal.dbo.coal_data
coal_data" & Chr(13) & "" & Chr(10) & "WHERE (coal_data.date>={t" _
, _
"s '2006-05-01 00:00:00'} And coal_data.date<={ts'2006-05-18
00:00:00'})" & Chr(13) & "" & Chr(10) & "ORDER BY coal_data.date,
coal_data.delivery_no" _
)


.Name = "Query from sqlserver"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
End Sub

 

[Tom Ogilvy]

Dim s as String, s1 as String
With Worksheets("Sheet1")
s = Format(.Range("A1").Value,"yyyy-mm-dd hh:mm:ss")
s2 = Format(.Range("B1").Value,"yyyy-mm-dd hh:mm:ss")
End With


then
.. . .
"WHERE (coal_data.date>={ts'" & s & _
"'} And coal_data.date<={ts'" & s1 & "'})"
.. . .

 

[Guest]

Tom

Thanks for the help. It works great.

Dim s as String, s1 as String
With Worksheets("Sheet1")
s = Format(.Range("A1").Value,"yyyy-mm-dd hh:mm:ss")
s2 = Format(.Range("B1").Value,"yyyy-mm-dd hh:mm:ss")
End With


then
.. . .
"WHERE (coal_data.date>={ts'" & s & _
"'} And coal_data.date<={ts'" & s1 & "'})"
.. . .