时间戳

首页 > 解决方案 > 无法识别名称“来源”。PowerQuery 的 VBA

vba - 无法识别名称“来源”。PowerQuery 的 VBA

问题描述

我创建了一个为 PowerQuery 转换数据的宏。然而，尽管在我手动创建宏时记录了它，但现在当我将它作为宏运行时，我收到错误“名称'源'未被识别”。

在不同区域玩弄代码后，我仍然无法按要求运行它：

ActiveWorkbook.Queries.Add Name:="Table1", Formula:= _
    "let Source = Excel.CurrentWorkbook(){[Name=""Table1""]}[Content]," & Chr(13) & "" & Chr(10) & "   #""Changed Type"" = Table.TransformColumnTypes(Source,{{""Column1"", type text}, {""Column2"", type text}, {""Column3"", type text}, {""Column4"", type text}, {""Column5"", type text}, {""Column6"", type text}, {""Column7"", type text}, {""Column8"", type text}, {""Column9"", type text}, {""C" & _
    "olumn10"", type text}, {""Column11"", type text}, {""Column12"", type text}, {""Column13"", type text}, {""Column14"", type text}, {""Column15"", type text}, {""Column16"", type text}, {""Column17"", type text}, {""Column18"", type text}, {""Column19"", type text}, {""Column20"", type text}, {""Column21"", type text}, {""Column22"", type text}, {""Column23"", type t" & _
    "ext}}), " & Chr(13) & "" & Chr(10) & "" & Chr(13) & "" & Chr(10) & "    #""Replaced Value"" = Table.ReplaceValue(#""Changed Type"",""Company Limited by Guarantee with Share Capital"",""11"",Replacer.ReplaceText,{""Column3""}), " & Chr(13) & "" & Chr(10) & "" & Chr(13) & "" & Chr(10) & "    #""Replaced Value1"" = Table.ReplaceValue(#""Replaced Value"",""Company Limited by Share Capital"",""1"",Replacer.ReplaceText,{""Column3""}), " & Chr(13) & "" & Chr(10) & "" & Chr(13) & "" & Chr(10) & "    #""Replaced Value2"" = Table.ReplaceVal" & _
    "ue(#""Replaced Value1"",""European Public Limited Liability Company (SE)"",""48"",Replacer.ReplaceText,{""Column3""}), " & Chr(13) & "" & Chr(10) & "" & Chr(13) & "" & Chr(10) & "    #""Replaced Value3"" = Table.ReplaceValue(#""Replaced Value2"",""Incorporated - Industrial & Provident Society Act"",""6"",Replacer.ReplaceText,{""Column3""}), " & Chr(13) & "" & Chr(10) & "" & Chr(13) & "" & Chr(10) & "    #""Replaced Value4"" = Table.ReplaceValue(#""Replaced Value3"",""Incorpor" & _
    "ated under Building Societies Act 1986"",""44"",Replacer.ReplaceText,{""Column3""}), " & Chr(13) & "" & Chr(10) & "" & Chr(13) & "" & Chr(10) & "    #""Replaced Value5"" = Table.ReplaceValue(#""Replaced Value4"",""Incorporated under Friendly Society Act 1992"",""45"",Replacer.ReplaceText,{""Column3""}), " & Chr(13) & "" & Chr(10) & "" & Chr(13) & "" & Chr(10) & "    #""Replaced Value6"" = Table.ReplaceValue(#""Replaced Value5"",""Limited Liability Partnership"",""42"",Replace" & _
    "r.ReplaceText,{""Column3""}), " & Chr(13) & "" & Chr(10) & "" & Chr(13) & "" & Chr(10) & "    #""Replaced Value7"" = Table.ReplaceValue(#""Replaced Value6"",""Company Limited by Guarantee with No Share Capital"",""12"",Replacer.ReplaceText,{""Column3""}), " & Chr(13) & "" & Chr(10) & "" & Chr(13) & "" & Chr(10) & "    #""Replaced Value8"" = Table.ReplaceValue(#""Replaced Value7"",""Limited Partnership"",""24"",Replacer.ReplaceText,{""Column3""}), " & Chr(13) & "" & Chr(10) & "" & Chr(13) & "" & Chr(10) & "    #""Replaced Value9"" " & _
    "= Table.ReplaceValue(#""Replaced Value8"",""Municipality or State e.g New South Wales"",""34"",Replacer.ReplaceText,{""Column3""}), " & Chr(13) & "" & Chr(10) & "" & Chr(13) & "" & Chr(10) & "    #""Replaced Value10"" = Table.ReplaceValue(#""Replaced Value9"",""Not Migrated"",""7"",Replacer.ReplaceText,{""Column3""}), " & Chr(13) & "" & Chr(10) & "" & Chr(13) & "" & Chr(10) & "    #""Replaced Value11"" = Table.ReplaceValue(#""Replaced Value10"",""Not Supplied"",""8"",Replacer" & _
    ".ReplaceText,{""Column3""}), " & Chr(13) & "" & Chr(10) & "" & Chr(13) & "" & Chr(10) & "    #""Replaced Value12"" = Table.ReplaceValue(#""Replaced Value11"",""Not Yet Formed"",""29"",Replacer.ReplaceText,{""Column3""}), " & Chr(13) & "" & Chr(10) & "" & Chr(13) & "" & Chr(10) & "    #""Replaced Value13"" = Table.ReplaceValue(#""Replaced Value12"",""Registered - Other Company Type by Companies House"",""49"",Replacer.ReplaceText,{""Column3""}), " & Chr(13) & "" & Chr(10) & "" & Chr(13) & "" & Chr(10) & "    #""Replaced Value14"" =" & _
    " Table.ReplaceValue(#""Replaced Value13"",""Overseas Firm Incorporated"",""50"",Replacer.ReplaceText,{""Column3""}), " & Chr(13) & "" & Chr(10) & "" & Chr(13) & "" & Chr(10) & "    #""Replaced Value15"" = Table.ReplaceValue(#""Replaced Value14"",""Overseas Firm Unincorporated"",""51"",Replacer.ReplaceText,{""Column3""}), " & Chr(13) & "" & Chr(10) & "" & Chr(13) & "" & Chr(10) & "    #""Replaced Value16"" = Table.ReplaceValue(#""Replaced Value15"",""PLC"",""4"",Replacer.Repla" & _
    "ceText,{""Column3""}), " & Chr(13) & "" & Chr(10) & "" & Chr(13) & "" & Chr(10) & "    #""Replaced Value17"" = Table.ReplaceValue(#""Replaced Value16"",""Partnership"",""23"",Replacer.ReplaceText,{""Column3""}), " & Chr(13) & "" & Chr(10) & "" & Chr(13) & "" & Chr(10) & "    #""Replaced Value18"" = Table.ReplaceValue(#""Replaced Value17"",""Private Unlimited Company"",""30"",Replacer.ReplaceText,{""Column3""}), " & Chr(13) & "" & Chr(10) & "" & Chr(13) & "" & Chr(10) & "    #""Replaced Value19"" = Table.ReplaceValue(#""Replaced Va" & _
    "lue18"",""Other"",""99"",Replacer.ReplaceText,{""Column3""}), " & Chr(13) & "" & Chr(10) & "" & Chr(13) & "" & Chr(10) & "    #""Replaced Value20"" = Table.ReplaceValue(#""Replaced Value19"",""Registered as Overseas Company by Companies House"",""47"",Replacer.ReplaceText,{""Column3""}), " & Chr(13) & "" & Chr(10) & "" & Chr(13) & "" & Chr(10) & "    #""Replaced Value21"" = Table.ReplaceValue(#""Replaced Value20"",""Registered as a Northern Ireland credit union"",""52"",Repla" & _
    "cer.ReplaceText,{""Column3""}), " & Chr(13) & "" & Chr(10) & "" & Chr(13) & "" & Chr(10) & "    #""Replaced Value22"" = Table.ReplaceValue(#""Replaced Value21"",""Registered under Friendly Society Act 1974"",""46"",Replacer.ReplaceText,{""Column3""}), " & Chr(13) & "" & Chr(10) & "" & Chr(13) & "" & Chr(10) & "    #""Replaced Value23"" = Table.ReplaceValue(#""Replaced Value22"",""Registered under I&PS Act 1965 and CU Act 1979"",""43"",Replacer.ReplaceText,{""Column3""}), " & Chr(13) & "" & Chr(10) & "" & Chr(13) & "" & Chr(10) & "" & _
    "    #""Replaced Value24"" = Table.ReplaceValue(#""Replaced Value23"",""Sole Trader"",""3"",Replacer.ReplaceText,{""Column3""}), " & Chr(13) & "" & Chr(10) & "" & Chr(13) & "" & Chr(10) & "    #""Replaced Value25"" = Table.ReplaceValue(#""Replaced Value24"",""Trust"",""22"",Replacer.ReplaceText,{""Column3""}), " & Chr(13) & "" & Chr(10) & "" & Chr(13) & "" & Chr(10) & "    #""Replaced Value26"" = Table.ReplaceValue(#""Replaced Value25"",""UK Co incorporated by Special Act/Roya" & _
    "l Charter"",""2"",Replacer.ReplaceText,{""Column3""}), " & Chr(13) & "" & Chr(10) & "" & Chr(13) & "" & Chr(10) & "    #""Replaced Value27"" = Table.ReplaceValue(#""Replaced Value26"",""UK body created by Special Act/Royal Charter"",""14"",Replacer.ReplaceText,{""Column3""}), " & Chr(13) & "" & Chr(10) & "" & Chr(13) & "" & Chr(10) & "    #""Replaced Value28"" = Table.ReplaceValue(#""Replaced Value27"",""Unincorporated Body"",""21"",Replacer.ReplaceText,{""Column3""}), " & Chr(13) & "" & Chr(10) & "" & Chr(13) & "" & Chr(10) & "  " & _
    "  #""Replaced Value29"" = Table.ReplaceValue(#""Replaced Value28"",""Limited"",""5"",Replacer.ReplaceText,{""Column3""}), " & Chr(13) & "" & Chr(10) & "" & Chr(13) & "" & Chr(10) & "    #""Replaced Value30"" = Table.ReplaceValue(#""Replaced Value29"",""Appointed Representative"",""6"",Replacer.ReplaceText,{""Column4""}), " & Chr(13) & "" & Chr(10) & "" & Chr(13) & "" & Chr(10) & "    #""Replaced Value31"" = Table.ReplaceValue(#""Replaced Value30"",""Branch (UK) of a Overseas " & _
    "Firm"",""2"",Replacer.ReplaceText,{""Column4""}), " & Chr(13) & "" & Chr(10) & "" & Chr(13) & "" & Chr(10) & "    #""Replaced Value32"" = Table.ReplaceValue(#""Replaced Value31"",""CBTL"",""34"",Replacer.ReplaceText,{""Column4""}), " & Chr(13) & "" & Chr(10) & "" & Chr(13) & "" & Chr(10) & "    #""Replaced Value33"" = Table.ReplaceValue(#""Replaced Value32"",""Claims Management"",""37"",Replacer.ReplaceText,{""Column4""}), " & Chr(13) & "" & Chr(10) & "" & Chr(13) & "" & Chr(10) & "    #""Replaced Value34"" = Table.ReplaceValue(#" & _
    """Replaced Value33"",""Trustee/Depositary"",""12"",Replacer.ReplaceText,{""Column4""}), " & Chr(13) & "" & Chr(10) & "" & Chr(13) & "" & Chr(10) & "    #""Replaced Value35"" = Table.ReplaceValue(#""Replaced Value34"",""Introducer A.R."",""27"",Replacer.ReplaceText,{""Column4""}), " & Chr(13) & "" & Chr(10) & "" & Chr(13) & "" & Chr(10) & "    #""Replaced Value36"" = Table.ReplaceValue(#""Replaced Value35"",""Operator"",""11"",Replacer.ReplaceText,{""Column4""}), " & Chr(13) & "" & Chr(10) & "" & Chr(13) & "" & Chr(10) & "    #""Re" & _
    "placed Value37"" = Table.ReplaceValue(#""Replaced Value36"",""Regulated"",""1"",Replacer.ReplaceText,{""Column4""}), " & Chr(13) & "" & Chr(10) & "" & Chr(13) & "" & Chr(10) & "    #""Replaced Value38"" = Table.ReplaceValue(#""Replaced Value37"",""Services (UK) of an Overseas Firm"",""5"",Replacer.ReplaceText,{""Column4""}), " & Chr(13) & "" & Chr(10) & "" & Chr(13) & "" & Chr(10) & "    #""Replaced Value39"" = Table.ReplaceValue(#""Replaced Value38"",""Trustee"",""9"",Replac" & _
    "er.ReplaceText,{""Column4""}), " & Chr(13) & "" & Chr(10) & "" & Chr(13) & "" & Chr(10) & "    #""Replaced Value40"" = Table.ReplaceValue(#""Replaced Value39"",""Depositary"",""10"",Replacer.ReplaceText,{""Column4""}), " & Chr(13) & "" & Chr(10) & "" & Chr(13) & "" & Chr(10) & "    #""Replaced Value41"" = Table.ReplaceValue(#""Replaced Value40"",""UK Representative"",""13"",Replacer.ReplaceText,{""Column4""}), " & Chr(13) & "" & Chr(10) & "" & Chr(13) & "" & Chr(10) & "    #""Replaced Value42"" = Table.ReplaceValue(#""Replaced Val" & _
    "ue41"",""Credit Rating Agency"",""99"",Replacer.ReplaceText,{""Column4""}), " & Chr(13) & "" & Chr(10) & "" & Chr(13) & "" & Chr(10) & "    #""Replaced Value43"" = Table.ReplaceValue(#""Replaced Value42"",""Data Reporting Service Provider"",""98"",Replacer.ReplaceText,{""Column4""}), " & Chr(13) & "" & Chr(10) & "" & Chr(13) & "" & Chr(10) & "    #""Replaced Value44"" = Table.ReplaceValue(#""Replaced Value43"",""Trade Repository"",""97"",Replacer.ReplaceText,{""Column4""}), " & Chr(13) & "" & _
    "" & Chr(10) & "" & Chr(13) & "" & Chr(10) & "    #""Replaced Value45"" = Table.ReplaceValue(#""Replaced Value44"",""Not hold and not control client money"",""N"",Replacer.ReplaceText,{""Column5""}), " & Chr(13) & "" & Chr(10) & "" & Chr(13) & "" & Chr(10) & "    #""Replaced Value46"" = Table.ReplaceValue(#""Replaced Value45"",""Hold and control client money"",""Y"",Replacer.ReplaceText,{""Column5""}), " & Chr(13) & "" & Chr(10) & "" & Chr(13) & "" & Chr(10) & "    #""Replaced Value47"" = Table.ReplaceValue(#""Replace" & _
    "d Value46"",""Control but not hold client money"",""N"",Replacer.ReplaceText,{""Column5""}), " & Chr(13) & "" & Chr(10) & "" & Chr(13) & "" & Chr(10) & "    #""Replaced Value48"" = Table.ReplaceValue(#""Replaced Value47"","""",""X"",Replacer.ReplaceValue,{""Column5""}), " & Chr(13) & "" & Chr(10) & "" & Chr(13) & "" & Chr(10) & "    #""Split Column by Delimiter"" = Table.SplitColumn(#""Replaced Value48"", ""Column12"", Splitter.SplitTextByDelimiter("" "", QuoteStyle.Csv), {""" & _
    "Column12.1"", ""Column12.2""}), " & Chr(13) & "" & Chr(10) & "" & Chr(13) & "" & Chr(10) & "    #""Changed Type1"" = Table.TransformColumnTypes(#""Split Column by Delimiter"",{{""Column12.1"", type text}, {""Column12.2"", type text}}), " & Chr(13) & "" & Chr(10) & "" & Chr(13) & "" & Chr(10) & "    #""Replaced Value49"" = Table.ReplaceValue(#""Changed Type1"",""+44Â"",""+44"",Replacer.ReplaceText,{""Column13""}), " & Chr(13) & "" & Chr(10) & "" & Chr(13) & "" & Chr(10) & "    #""Split Column by Position"" = Table.SplitColumn(#""Re" & _
    ""
ActiveWorkbook.Worksheets.Add
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
    "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=Table1;Extended Properties=""""" _
    , Destination:=Range("$A$1")).QueryTable
    .CommandType = xlCmdSql
    .CommandText = Array("SELECT * FROM [Table1]")
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .BackgroundQuery = True
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .PreserveColumnInfo = True
    .ListObject.DisplayName = "Table1_2"
    .Refresh BackgroundQuery:=False

非常感谢任何提示/指示或建议。

谢谢

标签： vba

解决方案

推荐阅读