如何用VBA修改Excel中SQL查询的日期参数？

问题背景 #

我通过Excel的「获取数据>自数据库>自SQL Server数据库」导入了带固定日期参数的SQL查询，原查询代码如下：

set @eomdate = '2022-10-31'
set @startdate = '2016-01-01'
set @enddate = '2022-10-31'

希望用Sheet1的A1、B1、C1单元格的日期分别替换@eomdate、@startdate、@enddate这三个固定参数，点击VBA按钮时先修改SQL语句里的日期参数，再刷新查询。目前我的VBA只能执行刷新，代码如下：

Private Sub CommandButton1_Click()
Dim ws As Worksheet
Set ws = Worksheets("Query1")
ActiveWorkbook.RefreshAll
End Sub

解决方案 #

替换原有VBA代码为以下内容，注意根据实际查询名称调整代码中的标识：

Private Sub CommandButton1_Click()
    Dim sqlTemplate As String
    Dim newSql As String
    Dim eomDate As String, startDate As String, endDate As String
    Dim qry As WorkbookQuery
    
    ' 提取Sheet1单元格日期并转为SQL兼容的YYYY-MM-DD格式
    eomDate = Format(Sheets("Sheet1").Range("A1").Value, "YYYY-MM-DD")
    startDate = Format(Sheets("Sheet1").Range("B1").Value, "YYYY-MM-DD")
    endDate = Format(Sheets("Sheet1").Range("C1").Value, "YYYY-MM-DD")
    
    ' 获取目标查询的SQL模板
    Set qry = ThisWorkbook.Queries("Query1") ' 替换为你的实际查询名称
    sqlTemplate = qry.Formula
    
    ' 替换SQL中的固定日期参数
    newSql = Replace(sqlTemplate, "set @eomdate = '2022-10-31'", "set @eomdate = '" & eomDate & "'")
    newSql = Replace(newSql, "set @startdate = '2016-01-01'", "set @startdate = '" & startDate & "'")
    newSql = Replace(newSql, "set @enddate = '2022-10-31'", "set @enddate = '" & endDate & "'")
    
    ' 更新查询的SQL语句
    qry.Formula = newSql
    
    ' 刷新指定查询（也可用ActiveWorkbook.RefreshAll刷新所有）
    Sheets("Query1").ListObjects(1).Refresh
End Sub

关键注意事项 #

• 确保Sheet1的A1、B1、C1单元格输入的是Excel可识别的有效日期，避免格式错误导致参数替换失败
• 代码中的"Query1"需替换为你在「查询和连接」面板中看到的实际查询名称
• 若你的原始SQL中固定日期的写法与示例不同，需对应调整Replace函数里的匹配字符串
• 若无需刷新所有查询，优先使用指定查询的ListObject刷新，减少不必要的资源消耗

内容的提问来源于stack exchange，提问作者Kkoruni