Excel VBA按钮生成查询触发1004错误，求问题排查

1. 直接触发1004错误的核心原因 #

你使用xlSrcRange作为SourceType创建ListObject，生成的是普通Excel表格，并非绑定外部数据源的查询表，因此该ListObject不存在QueryTable属性，执行.QueryTable.CommandText = query时必然抛出对象定义错误。

正确的做法是直接通过QueryTable导入数据，再按需转换为ListObject，或者使用xlSrcExternal作为SourceType创建带查询逻辑的列表对象。

2. SQL语句语法错误 #

columnList中的FORMAT函数语法不完整，缺失闭合括号：
原错误代码片段：

columnList = "[Invoice Id], [Invoice Date], FORMAT([Invoice Date], ""mmm-dd"" AS [Formatted Date], [Serial], [Purch Id], [Item Id], [Sell Price], [Revenue Share], [Vendor]"

修正后：

columnList = "[Invoice Id], [Invoice Date], FORMAT([Invoice Date], ""mmm-dd"") AS [Formatted Date], [Serial], [Purch Id], [Item Id], [Sell Price], [Revenue Share], [Vendor]"

（注：需为FORMAT函数的参数部分添加闭合)，再指定别名）

3. 潜在的同名列表对象冲突问题 #

如果目标工作表qry_ABB1中已存在名为qry_ABB1的ListObject，执行.Name = "qry_ABB1"时会触发重名错误，需提前删除原有表格：

On Error Resume Next
targetWorkbook.Sheets("qry_ABB1").ListObjects("qry_ABB1").Delete
On Error GoTo 0

4. 日期筛选逻辑优化建议 #

使用MONTH()和YEAR()函数会导致数据库无法利用索引（针对外部数据源），且可能因系统区域设置引发日期解析问题，建议改用日期范围查询：

Dim startDate As Date, endDate As Date
startDate = DateValue(targetMonth & " 1, " & targetYear)
endDate = DateAdd("m", 1, startDate)
query = "SELECT " & columnList & " FROM [tbl_Cons_Rpt] WHERE [Invoice Date] >= #" & Format(startDate, "mm/dd/yyyy") & "# AND [Invoice Date] < #" & Format(endDate, "mm/dd/yyyy") & "# AND [Vendor] = 'V005501 - Atlantic Broadband ARP'"

完整修正后的代码 #

Sub GenerateQuery()
    ' Get user inputs
    Dim targetMonth As String
    targetMonth = InputBox("Enter the target month (e.g., January):")

    Select Case LCase(targetMonth)
        Case "january", "february", "march", "april", "may", "june", "july", "august", "september", "october", "november", "december"
            ' Valid month entered
        Case Else
            MsgBox "Please enter a valid month name."
            Exit Sub
    End Select

    Dim targetYear As String
    targetYear = InputBox("Enter the target year (e.g., 2023):")

    If Not IsNumeric(targetYear) Or Len(targetYear) <> 4 Then
        MsgBox "Invalid year format. Please enter a four-digit year.", vbExclamation
        Exit Sub
    End If

    ' Define the list of column names to include in the query result
    Dim columnList As String
    columnList = "[Invoice Id], [Invoice Date], FORMAT([Invoice Date], ""mmm-dd"") AS [Formatted Date], [Serial], [Purch Id], [Item Id], [Sell Price], [Revenue Share], [Vendor]"

    ' 优化日期范围查询
    Dim startDate As Date, endDate As Date
    startDate = DateValue(targetMonth & " 1, " & targetYear)
    endDate = DateAdd("m", 1, startDate)
    
    Dim query As String
    query = "SELECT " & columnList & " FROM [tbl_Cons_Rpt] WHERE [Invoice Date] >= #" & Format(startDate, "mm/dd/yyyy") & "# AND [Invoice Date] < #" & Format(endDate, "mm/dd/yyyy") & "# AND [Vendor] = 'V005501 - Atlantic Broadband ARP'"

    Dim targetWorkbook As Workbook
    Set targetWorkbook = Workbooks.Open("C:\Users\notgoingtoshow.xlsx")

    ' 清空内容并删除原有同名表格
    With targetWorkbook.Sheets("qry_ABB1")
        .UsedRange.ClearContents
        On Error Resume Next
        .ListObjects("qry_ABB1").Delete
        On Error GoTo 0
    End With

    Dim tableDestination As Range
    Set tableDestination = targetWorkbook.Sheets("qry_ABB1").Range("A1")

    ' 直接创建QueryTable导入数据，再转为ListObject
    With tableDestination.QueryTable
        ' 替换为你的实际数据源连接字符串（如Access/SQL Server的ODBC/OLEDB连接）
        .Connection = "OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Data Source=你的数据库文件路径;Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
        .CommandText = query
        .BackgroundQuery = False
        .Refresh
        ' 将查询结果转为ListObject
        targetWorkbook.Sheets("qry_ABB1").ListObjects.Add(xlSrcRange, .ResultRange, , xlYes).Name = "qry_ABB1"
        .Delete ' 删除临时QueryTable（可选）
    End With

    ' Save and close the target workbook
    targetWorkbook.Close SaveChanges:=True
End Sub

注：需将代码中的.Connection替换为你实际使用的数据源连接字符串，不同数据源（如Access、SQL Server、Excel工作簿）的连接格式不同，请根据实际情况调整。

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