VBA Excel SQL query with date parameters linked to cell values

2
votes

I'm trying to pull data into Excel from a SQL database with date parameters. The following VB query works. Instead of manually changing the date values in the TS of the VB query, I want to have the query use cell values from the spreadsheet. Cell A1 has the date for the >= TS, and cell A2 has the date for the < TS

Sub vba_query_01()

    Dim oCon As ADODB.Connection
    Dim oRS As ADODB.Recordset
    Set oCon = New ADODB.Connection
    oCon.ConnectionString = "DRIVER=SQL Server;SERVER=GSEYBERTHNB7
         \SQLEXPRESS;UID=gseyberth;Trusted_Connection=Yes;APP=2007 Microsoft Office 
         system;WSID=GSEYBERTHNB7;DATABASE=DATA_LOGGER"
    oCon.Open
    Set oRS = New ADODB.Recordset
    oRS.ActiveConnection = oCon
    oRS.Source = "Select * FROM DATA_LOGGER.dbo.LYLE LYLE WHERE (( [Date] >= {TS '2013-04-24 
        07:00:00'} )) AND (( [Date] < {TS '2013-04-24 15:00:00'} ))"
    oRS.Open
    Range("A10").CopyFromRecordset oRS
    oRS.Close
    oCon.Close
    If Not oRS Is Nothing Then Set oRS = Nothing
    If Not oCon Is Nothing Then Set oCon = Nothing

End Sub
2
excelvba

2 Answers

2
votes

Thanks for the help. Got it to work with the following:

In Excel, used the formula

=TEXT(B1,"YYYY-MM-DD hh:mm:ss")

to convert excel date to text. Start date is in cell B3 and finish date is in cell B4

Changed VB to the following:

oRS.Source = "Select * FROM DATA_LOGGER.dbo.LYLE_CH2 LYLE_CH2 WHERE (( [Date] >= {TS '" & Range("B3") & "'} )) AND (( [Date] < {TS '" & Range("B4") & "'} )) ORDER BY [Date]"

Had to add ORDER BY to keep data from query in proper chronological order for spreadsheet calculations.

1
votes

Try this (assuming data in the sheet is are Date Serial numbers, and in the active sheet)

oR.SSource = "Select * FROM DATA_LOGGER.dbo.LYLE LYLE WHERE (( [Date] >= {TS '" & _
Format(Range("A1"), "yyyy-mm-dd hh:nn:ss") & _
"'} )) AND (( [Date] < {TS '" & _
Format(Range("A2"), "yyyy-mm-dd hh:nn:ss") & "'} ))"