在VBA中实现PowerQuery时遇报错问题求助

一、解决"The name 'Source' wasn't recognized"错误 #

录制宏生成的代码常存在冗余或上下文绑定错误，核心是M代码在VBA中未被正确识别。需确保M代码结构完整，同时用更稳定的PowerQuery API调用替代录制的零散代码。

修正后的VBA代码示例 #

Sub RefreshContractuelsQuery()
    Dim qry As WorkbookQuery
    Dim mCode As String
    
    ' 替换为你从PowerQuery复制的完整M代码
    mCode = "let" & vbCrLf & _
            "    Source = Csv.Document(File.Contents(""C:\你的CSV文件路径.csv""),[Delimiter="","", Columns=5, Encoding=1252, QuoteStyle=QuoteStyle.Csv])," & vbCrLf & _
            "    #""提升的标题"" = Table.PromoteHeaders(Source, [PromoteAllScalars=true])," & vbCrLf & _
            "    #""更改的类型"" = Table.TransformColumnTypes(#""提升的标题"",{{""列1"", type text}, {""列2"", Int64.Type}})" & vbCrLf & _
            "in" & vbCrLf & _
            "    #""更改的类型"""
    
    ' 先删除已存在的同名查询，避免堆积
    On Error Resume Next
    ThisWorkbook.Queries("Contractuels").Delete
    On Error GoTo 0
    
    ' 创建新查询
    Set qry = ThisWorkbook.Queries.Add(Name:="Contractuels", Formula:=mCode)
    
    ' 将查询加载到指定工作表（按需修改目标位置，比如Sheet1的A1单元格）
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=Contractuels;Extended Properties=""""" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [Contractuels]")
        .BackgroundQuery = True
        .Refresh BackgroundQuery:=False
    End With
End Sub

关键修正点 #

• 预删除旧查询：每次运行前清理同名查询，从根源解决查询堆积问题
• 完整M代码传入：确保M代码包含完整的let...in结构，Source作为M代码内的步骤被正确识别，避免录制宏生成的无效API调用
• 稳定加载逻辑：用ListObjects.Add直接绑定查询，替代录制的ExecuteExcel4Macro这类易出错的代码

二、解决查询堆积与Excel崩溃问题 #

• 强制清理旧查询：保留代码开头的查询删除逻辑，避免重复创建查询导致内存占用过高
• 控制刷新模式：数据量较大时，设置.Refresh BackgroundQuery:=False，确保刷新完成后再执行后续操作，减少崩溃概率
• 放弃录制宏：录制的宏会生成大量冗余代码，直接手动编写VBA调用PowerQuery API更稳定
• 手动清理缓存：通过数据>获取数据>查询选项>缓存清除PowerQuery缓存，或重启Excel释放内存

三、按钮绑定与动态路径优化 #

• 在开发工具中插入按钮，绑定上述RefreshContractuelsQuery宏即可实现点击触发
• 如果需要动态选择CSV文件，可添加文件选择对话框：

Dim csvPath As String
csvPath = Application.GetOpenFilename("CSV文件 (*.csv), *.csv")
If csvPath = "False" Then Exit Sub
' 将mCode中的固定路径替换为csvPath变量

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