JKwan
12-01-2004, 04:46 PM
I am having problems making this into a "variable" query, hope someone can help.
As the way the query sits, it will only retrieve data from Jan 01 2000 to Feb 15 2000. I started on getting monkeying around with the parameters, just kept on getting SQL error. Cell A2 is from date and B2 is the end date.
:confused:
sub test
Dim FromDate, ToDate As Date
Range("A3:B1000").Select
Selection.Delete
FromDate = Format(Cells(2, 1).Value, "yyyy-mm-dd") & " 00:00:00"
ToDate = Format(Cells(2, 2).Value, "yyyy-mm-dd") & " 00:00:00"
With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
"ODBC;DSN=FetchDEV;Description=Fetch DEV;UID=SYSTEM;APP=Microsoft Office XP;WSID=DEV;DATABASE=DevDB;Trusted_C" _
), Array("onnection=Yes")), Destination:=Range("A3"))
.CommandText = Array( "SELECT NonHourlyPrice.PriceDate, NonHourlyPrice.ClosePrice" & Chr(13) & "" & Chr(10) & "FROM _
FetchDEV.dbo.NonHourlyPrice NonHourlyPrice" & Chr(13) & "" & Chr(10) & "WHERE (NonHourlyPrice.PriceKeyID=65) AND (NonHourlyPrice.Price" , _
"Date>={ts '2000-01-01 00:00:00'} And NonHourlyPrice.PriceDate<={ts '2000-02-15 00:00:00'})" & Chr(13) & "" & Chr(10) & _
"ORDER BY NonHourlyPrice.PriceDate")
.Name = "Fetch"
.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
As the way the query sits, it will only retrieve data from Jan 01 2000 to Feb 15 2000. I started on getting monkeying around with the parameters, just kept on getting SQL error. Cell A2 is from date and B2 is the end date.
:confused:
sub test
Dim FromDate, ToDate As Date
Range("A3:B1000").Select
Selection.Delete
FromDate = Format(Cells(2, 1).Value, "yyyy-mm-dd") & " 00:00:00"
ToDate = Format(Cells(2, 2).Value, "yyyy-mm-dd") & " 00:00:00"
With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
"ODBC;DSN=FetchDEV;Description=Fetch DEV;UID=SYSTEM;APP=Microsoft Office XP;WSID=DEV;DATABASE=DevDB;Trusted_C" _
), Array("onnection=Yes")), Destination:=Range("A3"))
.CommandText = Array( "SELECT NonHourlyPrice.PriceDate, NonHourlyPrice.ClosePrice" & Chr(13) & "" & Chr(10) & "FROM _
FetchDEV.dbo.NonHourlyPrice NonHourlyPrice" & Chr(13) & "" & Chr(10) & "WHERE (NonHourlyPrice.PriceKeyID=65) AND (NonHourlyPrice.Price" , _
"Date>={ts '2000-01-01 00:00:00'} And NonHourlyPrice.PriceDate<={ts '2000-02-15 00:00:00'})" & Chr(13) & "" & Chr(10) & _
"ORDER BY NonHourlyPrice.PriceDate")
.Name = "Fetch"
.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