时间戳

首页 > 解决方案 > 将字符串更改为具有精确字符串的变量时，VBA 会引发错误

excel - 将字符串更改为具有精确字符串的变量时，VBA 会引发错误

问题描述

这是我用于 powerquery 功能的 vba 代码

ActiveWorkbook.Queries.Add Name:="Or ORder", Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Source = Excel.Workbook(File.Contents(""C:\Users\DDK\Downloads\excel.xlsx""), null, true)," & Chr(13) & "" & Chr(10) & "    Sheet1_Sheet = Source{[Item=""Sheet1"",Kind=""Sheet""]}[Data]," & Chr(13) & "" & Chr(10) & "    #""Promoted Headers"" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true])," & Chr(13) & "" & Chr(10) & "    #""Changed Type"" = Table.TransformColumnTypes(#""Promoted Headers"",{{""Column1"", type any}" & _
        ", {""CROWN"", type any}, {""March 15th, 2021"", type text}, {""Column4"", type any}, {""Column5"", type any}})," & Chr(13) & "" & Chr(10) & "    #""Removed Top Rows"" = Table.Skip(#""Changed Type"",2)," & Chr(13) & "" & Chr(10) & "    #""Renamed Columns"" = Table.RenameColumns(#""Removed Top Rows"",{{""Column1"", ""QTY""}, {""CROWN"", ""ITEM""}, {""March 15th, 2021"", ""Part""}, {""Column5"", ""Price""}})," & Chr(13) & "" & Chr(10) & "    #""Filter" & _
        "ed Rows"" = Table.SelectRows(#""Renamed Columns"", each [QTY] <> null and [QTY] <> """")" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Filtered Rows"""
    ActiveWorkbook.Worksheets.Add.Name = "Our Order"
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""Or ORder"";Extended Properties=""""" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [Or ORder]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "Or_ORder"
        .Refresh BackgroundQuery:=False
    End With

当源的文件目录更改为这样的变量时

Dim fileOrder as String
fileOrder = "C:\Users\Ddk\Downloads\excel.xlsx"
....
Source = Excel.Workbook(File.Contents(fileOrder), null, true)," & Chr(13) & "" & Chr(10) ""....

到达时会抛出错误.Refresh BackgroundQuery:=False。为什么？即使它是完全相同的字符串，它也会给出错误[Expression.Error] The import Order matches no exports. Did you miss a module reference?

问题是我希望用户通过 VBA 选择用于电源查询的 excel 文件，这就是为什么我需要在文件目录上有一个变量的原因。

标签： excelvbapowerquery

解决方案

为什么不使用 VBA 将路径写入命名单元格范围，并让 powequery 读取它？

let Source = Csv.Document(File.Contents(Excel.CurrentWorkbook(){[Name="RangeName"]}[Content]{0}[Column1]),

或者

let  Source = Excel.Workbook(File.Contents(Excel.CurrentWorkbook(){[Name="RangeName"]}[Content]{0}[Column1]), null, true),

推荐阅读