Excel VBA将参数传递给OLEDBConnection [英] Excel VBA pass parameter to OLEDBConnection

问题描述

在Excel工作簿中，我与SQL Server DB有多个连接.我以为我可以运行一个像下面的脚本一样的小脚本，但是它并没有满足我的要求.我想刷新OLEDBConnection，并传入StartDate和EndDate，但实际上并没有与该连接通信.

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

推荐答案

您的.CommandText字符串在AND周围有一个错误(注意它的颜色是蓝色...正在解析为VBA关键字).

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

它应显示为:

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

这篇关于Excel VBA将参数传递给OLEDBConnection的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！