Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I use variable dates with a SQL Query
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=Microsof t 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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I use variable dates with a SQL Query
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 & "'})" .. . . -- Regards, Tom Ogilvy "MegaWatt" wrote in message ... 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=Microsof t 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I use variable dates with a SQL Query
Tom
Thanks for the help. It works great. "Tom Ogilvy" wrote: 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 & "'})" .. . . -- Regards, Tom Ogilvy "MegaWatt" wrote in message ... 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=Microsof t 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Variable passed to database query | Excel Discussion (Misc queries) | |||
Deleting variable web query name | Excel Programming | |||
With QUERY how to input a variable | Excel Discussion (Misc queries) | |||
put query result into variable | Excel Programming | |||
Query that returns a value to variable | Excel Programming |