VBA提取CSV数据触发Run-time error 1004问题求助

问题原因分析 #

1. 数据源配置错误：原代码连接字符串指向工作簿内的Power Query查询（Location=TSO Metered Data），但你的需求是读取外部CSV文件，数据源不匹配导致QueryTable无法正确初始化ListObject。
2. 设置时机不当：.ListObject.DisplayName在QueryTable未完成数据刷新前调用，此时ListObject的引用未完全稳定，触发对象定义错误。
3. 逻辑偏差：代码新建了工作表而非直接在目标Input工作表操作，不符合需求。

解决方案 #

• 重新配置连接字符串，指向外部CSV文件；
• 先完成QueryTable数据刷新，再设置ListObject的DisplayName；
• 直接在Input工作表内操作，避免冗余工作表创建。

修正后的代码 #

Sub PowerQueryTransform()
    Dim myWorkBook As Excel.Workbook
    Dim myWorkSheet As Excel.Worksheet
    Dim qt As QueryTable
    Dim lo As ListObject
    Dim csvPath As String
    
    ' 替换为你的CSV文件实际路径
    csvPath = "C:\YourFolder\TSO Metered Data.csv"
    
    Set myWorkBook = Application.Workbooks("Meterd Monthly Data Colector.xlsm")
    Set myWorkSheet = myWorkBook.Sheets("Input")
    
    ' 清空Input工作表原有数据（可选，根据需求调整）
    myWorkSheet.Cells.Clear
    
    ' 在Input工作表创建QueryTable并关联CSV数据源
    Set qt = myWorkSheet.ListObjects.Add( _
        SourceType:=0, _
        Source:="OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=" & csvPath & ";Extended Properties=""""", _
        Destination:=myWorkSheet.Range("$A$1")).QueryTable
    
    With qt
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [Sheet1$]") ' CSV默认表名为Sheet1$，若有自定义表名需修改
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        
        ' 先完成数据刷新，确保ListObject初始化完成
        .Refresh BackgroundQuery:=False
        
        ' 获取已初始化的ListObject并设置名称
        Set lo = .ListObject
        lo.DisplayName = "TSO Metered Data"
    End With
    
    myWorkSheet.Activate
    myWorkSheet.Range("A1").Select
End Sub

关键注意事项 #

• 必须将csvPath替换为你的CSV文件实际存储路径；
• CSV通过Power Query连接时默认表名为Sheet1$，若你的CSV有表头且Power Query识别为自定义表名，需同步修改.CommandText中的表名；
• 清空Input工作表的操作可根据需求选择保留或移除。

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