Excel VBA动态生成Power Query列类型代码刷新报错问题

从你的代码来看，问题出在VBA拼接Power Query代码时的引号转义错误，导致生成的PQ语法不符合规范，所以刷新时触发错误。当ColTypes为空时，PQ公式是完整且正确的，所以能正常运行；一旦ColTypes包含错误的语法，就会报错。

下面是具体的错误点和修正后的代码：

主要错误点 #

1. 双引号转义错误：在VBA中，要生成Power Query里的单个双引号，需要用两个双引号（""）来表示，但你的代码里重复使用了过多的双引号，导致生成的PQ代码中出现多余的引号（比如""ID""而不是"ID"）。
2. 步骤名称的引号错误：PQ的步骤名称（比如#"Type wijzigen"）在VBA拼接时，应该写成#"""Type wijzigen"""，而不是#""""Type wijzigen""""，否则会生成无效的步骤名称。
3. 结尾拼接的语法错误：最后拼接in和步骤名称时，引号转义混乱，导致PQ公式结构错误。

修正后的ColTypes拼接代码 #

Dim ColNames() As String
ReDim ColNames(1 To CountCols)
Dim ColTypes As String
ColNames = Split(CSVline, ";")
ColTypes = ""

' 初始化步骤：修正引号转义，正确生成PQ步骤名称
ColTypes = ColTypes & "    #""Type wijzigen"" = Table.TransformColumnTypes(#""De eerste rij als veldnamen gebruiken"", {"

For CT = LBound(ColNames) To UBound(ColNames)
    ' ID
    If InStr(ColNames(CT), "ID") > 0 Then
        ColTypes = ColTypes & "{""" & ColNames(CT) & """, type text}, "
    ' Type
    ElseIf InStr(ColNames(CT), "Type") > 0 Then
        ColTypes = ColTypes & "{""" & ColNames(CT) & """, type text}, "
    ' Assembly code en description
    ElseIf InStr(ColNames(CT), "Assembly") > 0 Then
        ColTypes = ColTypes & "{""" & ColNames(CT) & """, type text}, "
    ' Width
    ElseIf InStr(ColNames(CT), "Width") > 0 Or InStr(ColNames(CT), "Breedte") > 0 Then
        ColTypes = ColTypes & "{""" & ColNames(CT) & """, Int64.Type}, "
    ' Length
    ElseIf InStr(ColNames(CT), "Length") > 0 Or InStr(ColNames(CT), "Lengte") > 0 Or InStr(ColNames(CT), "Perimeter") > 0 Then
        ColTypes = ColTypes & "{""" & ColNames(CT) & """, Int64.Type}, "
    ' Height
    ElseIf InStr(ColNames(CT), "Height") > 0 Or InStr(ColNames(CT), "Hoogte") > 0 Then
        ColTypes = ColTypes & "{""" & ColNames(CT) & """, Int64.Type}, "
    ' Area
    ElseIf InStr(ColNames(CT), "Area") > 0 Or InStr(ColNames(CT), "Oppervlak") > 0 Then
        ColTypes = ColTypes & "{""" & ColNames(CT) & """, type number}, "
    ' Volume
    ElseIf InStr(ColNames(CT), "Volume") > 0 Then
        ColTypes = ColTypes & "{""" & ColNames(CT) & """, type number}, "
    ' Phasing
    ElseIf InStr(ColNames(CT), "Phase") > 0 Or InStr(ColNames(CT), "Fase") > 0 Then
        ColTypes = ColTypes & "{""" & ColNames(CT) & """, type text}, "
    ' Weight
    ElseIf InStr(ColNames(CT), "Kilo") > 0 Or InStr(ColNames(CT), "kg") > 0 Or InStr(ColNames(CT), "Ton") > 0 Or InStr(ColNames(CT), "Gewicht") > 0 Then
        ColTypes = ColTypes & "{""" & ColNames(CT) & """, type number}, "
    ' Level
    ElseIf InStr(ColNames(CT), "Level") > 0 Or InStr(ColNames(CT), "Verdieping") > 0 Or InStr(ColNames(CT), "Bouwlaag") > 0 Then
        ColTypes = ColTypes & "{""" & ColNames(CT) & """, type text}, "
    ' Other
    Else
        ColTypes = ColTypes & "{""" & ColNames(CT) & """, type text}, "
    End If
Next CT

' 移除最后一个多余的逗号和空格，增加空值判断避免报错
If Len(ColTypes) > 0 Then
    ColTypes = Left(ColTypes, Len(ColTypes) - 2)
End If

' 结束步骤并拼接in语句，修正引号转义
ColTypes = ColTypes & "})" & Chr(13) & Chr(10) & "in" & Chr(13) & Chr(10) & "    #""Type wijzigen"""

修正后的查询插入代码 #

ActiveWorkbook.Queries.Add _
    Name:=CSV(S), _
    Formula:= _
        "let" & Chr(13) & Chr(10) & _
        "    Bron = Csv.Document(File.Contents(""" & csvPath & CSV(S) & ".csv"""),[Delimiter="";"", Columns=" & CountCols & ", Encoding=1252, QuoteStyle=QuoteStyle.None])," & Chr(13) & Chr(10) & _
        "    #""De eerste rij als veldnamen gebruiken"" = Table.PromoteHeaders(Bron, [PromoteAllScalars=true])" & Chr(13) & Chr(10) & _
        ColTypes

关键修正说明 #

1. 引号转义：将原本的{"""" & ColNames(CT) & """", type text}改为{""" & ColNames(CT) & """, type text}，这样生成的PQ代码中列名只会有一对双引号（比如{"ID", type text}）。
2. 步骤名称规范：将#""""Type wijzigen""""改为#""Type wijzigen""，正确生成PQ的步骤名称格式#"Type wijzigen"。
3. 公式结构优化：在拼接PQ公式时添加了缩进（空格），让生成的代码更易读，同时避免语法混乱。
4. 空值判断：在移除末尾逗号前增加了Len(ColTypes)的判断，防止ColTypes为空时出现错误。

这样修正后，生成的Power Query代码语法完全符合规范，执行.refresh backgroundquery:=false时就不会再报错了。

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