VBA Excel SQL query with date parameters linked to cell values

Question

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

Andriy M · Accepted Answer · 2013-06-23 13:43:24Z

2

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.

edited Jun 23, 2013 at 13:43

Andriy M

78k18 gold badges100 silver badges157 bronze badges

answered May 17, 2013 at 5:33

Gary

411 silver badge3 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

chris neilsen · Accepted Answer · 2013-05-11 07:18:10Z

1

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") & "'} ))"

edited May 11, 2013 at 7:18

answered May 11, 2013 at 6:03

chris neilsen

53.3k10 gold badges91 silver badges127 bronze badges

1 Comment

Gary Over a year ago

I'm getting an error message Run-time error '-2147217908 (80040e0c)': Command text was not set for the command object.

Collectives™ on Stack Overflow

VBA Excel SQL query with date parameters linked to cell values

2 Answers 2

Comments

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Related