添加WHERE筛选后，从关闭工作簿查询数据无输出的问题求助

我编写了一套从关闭工作簿查询数据集的VBA代码，仅在查询整张工作表时能正常运行，但添加筛选条件后，目标文件无任何数据导入，且没有错误提示弹出。

示例数据说明 #

数据包含日期列（Date）、备注列（Remarks）等常规表格字段。

测试用VBA代码（ADODB方式） #

Sub QueryA()
    Dim conn As ADODB.Connection, rst As ADODB.Recordset
    Dim wss As Worksheet

        Set wss = ThisWorkbook.Worksheet("Revenue")
        Set conn = New ADODB.Connection
        Set rst = New ADODB.Recordset

        Filename = C:\Users\PC\Documents\Revenue.xlsx
        wss.Cells.Clear
    
        conn.ConnectionString = _
            "Provider=Microsoft.ACE.OLEDB.12.0;" & _
            "Data Source=" & Filename & ";" & _
            "Extended Properties='Excel 12.0 Xml;HDR=NO;IMEX=1';"
        'IMEX=1 用于兼容多种数据类型

        conn.Open
        
        rst.Open _
            Source:="SELECT * FROM [Sheet1$] WHERE YEARWEEK(Date) = YEARWEEK(NOW() - INTERVAL 1 WEEK)", _
            ActiveConnection:=conn
            Debug.Print rst.Source
            wss.Range("A1048576").End(xlUp).CopyFromRecordset rst
            
        rst.Close
        conn.Close

        MsgBox "数据导入成功。"
End Sub

排查测试情况 #

为定位问题，我做了以下测试：

• 尝试仅查询指定列：Source:="SELECT [Remarks] FROM [Sheet1$]"，无数据导入
• 尝试按备注字段筛选：Source:="SELECT * FROM [Sheet1$] WHERE Remarks = 'SMTH1'"，同样无数据导入

只有当查询语句为Source:="SELECT * FROM [Sheet1$]"或Source:="[Sheet1$]"时，才能正常导入整张表的数据。

Debug.Print能输出预期的查询语句，但始终没有数据导入。我的代码存在什么问题？

补充：PowerQuery方案尝试 #

我还尝试了用PowerQuery实现，代码如下：

Sub PowerQueryA()

    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Revenue")
    
    Dim qt As QueryTable
    Dim qName As String
    qName = "PowerQueryData"
    
    For Each qt In ws.QueryTables
        If qt.Name = qName Then
            qt.Delete
            Exit For
        End If
    Next qt
    
    Dim LastMonday As Date, LastSunday As Date
    LastMonday = DateAdd("ww", -1, Now - (Weekday(Now, vbMonday) - 1))
    LastSunday = LastMonday + 6

    StartDate = Format(LastMonday, "dd/mm/yyyy")
    EndDate = Format(LastSunday, "dd/mm/yyyy")
    
    Dim connString As String
    connString = "OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\PC\Documents\Book1 - Copy.xlsx;Extended Properties=""Excel 12.0 Xml;HDR=YES"""
    Set qt = ws.QueryTables.Add(Connection:=connString, Destination:=ws.Range("A1"), Sql:="SELECT * FROM [Sheet1$] WHERE Date >= StartDate AND Date <= EndDate")
    qt.Name = qName
    qt.RefreshStyle = xlOverwriteCells
    qt.Refresh
End Sub

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