Excel VBA中如何修改SQL语句实现单元格日期的区间查询？

你需要将原代码中基于订单号的筛选逻辑，改为通过工作表「UM」的G7、G8单元格日期，用BETWEEN语句筛选Oracle表的a.date字段。核心问题是Excel日期格式与Oracle日期格式的匹配以及SQL语句的正确拼接，以下是修正后的完整代码及关键说明：

修改后的完整代码 #

Sub giveMeData()
    ' 清理A列空格（保留原逻辑，去掉冗余的Select操作）
    Columns("A:A").Replace What:="  ", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

    Dim sql As String
    Dim startDate As String, endDate As String
    
    ' 将Excel日期转换为Oracle认可的'YYYY-MM-DD'格式字符串
    startDate = Format(Worksheets("UM").Range("G7").Value, "yyyy-mm-dd")
    endDate = Format(Worksheets("UM").Range("G8").Value, "yyyy-mm-dd")

    ' 拼接规范的SQL语句，使用BETWEEN筛选日期
    sql = "SELECT" & Chr(10) & _
          "    a.order AS Order_no," & Chr(10) & _
          "    a.client AS Client," & Chr(10) & _
          "    a.date AS Delivery_Date" & Chr(10) & _
          "FROM table a" & Chr(10) & _
          "WHERE a.date BETWEEN TO_DATE('" & startDate & "', 'YYYY-MM-DD') " & _
          "AND TO_DATE('" & endDate & "', 'YYYY-MM-DD')"

    With ActiveWorkbook.Connections("query").ODBCConnection
        .BackgroundQuery = False
        .CommandText = sql
        .CommandType = xlCmdSql
        .Connection = "ODBC;SERVER=myserver;DRIVER={Microsoft ODBC for Oracle};uid=" & _
                      ThisWorkbook.Sheets("uwagi").Range("L1").Value & ";pwd=" & _
                      ThisWorkbook.Sheets("uwagi").Range("L2").Value
        .RefreshOnFileOpen = False
        .SavePassword = False
        .SourceConnectionFile = ""
        .SourceDataFile = ""
        .ServerCredentialsMethod = xlCredentialsMethodIntegrated
        .AlwaysUseConnectionFile = False
    End With
    ActiveWorkbook.Connections("query").Refresh

End Sub

关键修改说明 #

1. 日期格式适配
   Excel存储的日期是数值类型，直接拼接会导致Oracle无法识别，用Format函数将其转换为YYYY-MM-DD格式的字符串，再配合Oracle的TO_DATE函数明确指定格式，彻底避免日期格式不匹配的报错。

2. SQL语句修正

   • 将原WHERE子句的订单筛选替换为BETWEEN ... AND ...的日期范围筛选，语法符合Oracle规范
   • 整理了SQL拼接的换行和空格，让语句结构更清晰，减少语法错误概率

3. 冗余代码优化
   去掉了原代码中Columns("A:A").Select的冗余操作，直接调用Replace方法，简化执行逻辑。

内容的提问来源于stack exchange，提问作者Mateusz Koś