CODEWITHSUNDEEP
vbaexcel
ASH
ASH

Reputation: 20322

Excel VBA pass parameter to OLEDBConnection

In an Excel workbook I have several connections to a SQL Server DB. I thought I could run a small script like the one below, but it's not doing what I want. I want to refresh the OLEDBConnection, and pass in a StartDate and EndDate, but it's not actually communicating with the connection.

Sub RefreshWithDates()

Dim StartDate As Date
Dim EndDate As Date

StartDate = Sheets("Pivot_Summary").Range("B1").Value
EndDate = Sheets("Pivot_Summary").Range("B2").Value

    With ActiveWorkbook.Connections("FMDDATA_HIST_SPLIT").OLEDBConnection
        .CommandText = "SELECT * FROM RECONCILIATION.dbo.TBL_FMDDATA_HIST_SPLIT Where AsOfDate between '" & StartDate & "' & " And " & '" & EndDate & "'"
        ActiveWorkbook.Connections("FMDDATA_HIST_SPLIT").Refresh
    End With

End Sub

Upvotes: 2

Views: 2072

Answers (1)

John F
John F

Reputation: 276

Your .CommandText string has an error around the AND (notice how it is blue in color... it is resolving as the VBA keyword And).

It should read:

"EXEC SELECT * FROM RECONCILIATION.dbo.TBL_FMDDATA_HIST_SPLIT 
WHERE AsOfDate between '" & StartDate & "'" & " AND " & "'" & EndDate & "'"

Upvotes: 4

Related Questions