excel 创建PowerQuery时出现令牌错误

此子程序使用文件夹路径中的源代码创建一个powerquery，它从文件夹中获取所有excel文件，并对其进行合并和筛选。
每当我将Source = Folder.files（"Dir"）设置为动态时，我都会遇到一个错误是否有一种方法可以将路径设置为动态，这样我就可以刷新查询页面并更新Settings工作表中Folder.files的SourcePath值。
我有这个错误
运行时错误"1004"：[Expression. Error]无法识别名称"Source"，请确保其拼写正确。
在该行.Refresh BackgroundQuery:=False中
如果我为引用工作表中"C:\Path"的单元格的SourcePath创建一个值，则会出现以下错误
错误：运行时错误"1004"：应用程序定义或对象定义错误
在该行中.ListObject.DisplayName = "Data"

Sub AddSaveQuery()
'Set Objects
Dim SourcePath As String
Dim SavePath As String
SourcePath = Worksheets("Home").Range("G21").Value
SavePath = Worksheets("Home").Range("G8").Value
Sub AddSaveQuery()
'Set Objects 
Dim SourcePath As String 
Dim SavePath As String 

SourcePath = Worksheets("Home").Range("G21").Value 
SavePath = Worksheets("Home").Range("G8").Value

'Will set first condition for sheet2
'Filter Column2 Begins with FilterValue1
ActiveWorkbook.Queries.Add Name:="Data", Formula:= _
                           "let" & Chr(13) & "" & Chr(10) & "    Source = Folder.Files(""" &     SourcePath & """)," & Chr(13) & "" & Chr(10) & "    #""Filtered Hidden Files1"" = Table.SelectRows(Source, Each [Attributes]?[Hidden]? <> true)," & Chr(13) & "" & Chr(10) & "    #""Invoke Custom Function1"" = Table.AddColumn(#""Filtered Hidden Files1"", ""Transform File"", Each #""Transform File""([Content]))," & Chr(13) & "" & Chr(10) & "    #""Renamed Columns1""" & _
                           " = Table.RenameColumns(#""Invoke Custom Function1"", {""Name"", ""Source.Name""})," & Chr(13) & "" & Chr(10) & "    #""Removed Other Columns1"" = Table.SelectColumns(#""Renamed Columns1"", {""Source.Name"", ""Transform File""})," & Chr(13) & "" & Chr(10) & "    #""Expanded Table Column1"" = Table.ExpandTableColumn(#""Removed Other Columns1"", ""Transform File"", Table.ColumnNames(#""Transform File""(#""Sample File"")))," & Chr(13) & "" & _
                           "" & Chr(10) & "    #""Changed Type"" = Table.TransformColumnTypes(#""Expanded Table Column1"",{{""Source.Name"", Type text}, {""Column1"", Type any}, {""Column2"", Type any})," & Chr(13) & "" & Chr(10) & "    #""Removed Other Columns"" = Table.SelectColumns(#""Column1" & _
                           "ed Type"",{""Column2""})," & Chr(13) & "" & Chr(10) & "    #""Filtered Rows"" = Table.Sel" & _
                           "ectRows(#""Removed Other Columns"", Each Text.StartsWith([Column2], ""FilterValue1""))" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Filtered Rows"""

ActiveWorkbook.Queries.Add Name:="Parameter1", Formula:= _
                           "#""Sample File"" meta [IsParameterQuery=true, BinaryIdentifier=#""Sample File"", Type=""Binary"", IsParameterQueryRequired=true]"
ActiveWorkbook.Queries.Add Name:="Transform Sample File", Formula:= _
                           "let" & Chr(13) & "" & Chr(10) & "    Source = Excel.Workbook(Parameter1, null, true)," & Chr(13) & "" & Chr(10) & "    Page1_Sheet = Source{[Item=""Page1"",Kind=""Sheet""]}[Data]," & Chr(13) & "" & Chr(10) & "    #""Promoted Headers"" = Table.PromoteHeaders(Page1_Sheet, [PromoteAllScalars=true])" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Promoted Headers"""
ActiveWorkbook.Queries.Add Name:="Sample File", Formula:= _
                           "let" & Chr(13) & "" & Chr(10) & "    Source = Folder.Files(""" & SourcePath & """)," & Chr(13) & "" & Chr(10) & "    Navigation1 = Source{0}[Content]" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    Navigation1"
ActiveWorkbook.Queries.Add Name:="Transform File", Formula:= _
                           "let" & Chr(13) & "" & Chr(10) & "    Source = (Parameter1) => let" & Chr(13) & "" & Chr(10) & "        Source = Excel.Workbook(Parameter1, null, true)," & Chr(13) & "" & Chr(10) & "        Page1_Sheet = Source{[Item=""Page1"",Kind=""Sheet""]}[Data]," & Chr(13) & "" & Chr(10) & "        #""Promoted Headers"" = Table.PromoteHeaders(Page1_Sheet, [PromoteAllScalars=true])" & Chr(13) & "" & Chr(10) & "    in" & Chr(13) & "" & Chr(10) & "        #""Promoted Headers""" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    Source"
ActiveWorkbook.Worksheets.Add
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
     "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=Data;Extended Properties=""""" _
     , Destination:=Range("$A$1")).QueryTable
    .CommandType = xlCmdSql
    .CommandText = Array("SELECT * FROM [Data]")
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .BackgroundQuery = True
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .PreserveColumnInfo = True
    .ListObject.DisplayName = "Data"
    .Refresh BackgroundQuery:=False
End With
Workbooks("CognosDataCleaner2.xlsm").Connections.Add2 "Query - Parameter1", _
                                                      "Connection To the        'Parameter1' query in the workbook.", _
                                                      "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=Parameter1;Extended Properties=""""" _
                                                      , "SELECT * FROM [Parameter1]", 2
Workbooks("CognosDataCleaner2.xlsm").Connections.Add2 _
                                                      "Query - Transform Sample File", _
                                                      "Connection To the        'Transform Sample File' query in the workbook.", _
                                                      "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""Transform Sample File"";Extended Properties=""""" _
                                                      , "SELECT * FROM [Transform Sample File]", 2
Workbooks("CognosDataCleaner2.xlsm").Connections.Add2 "Query - Sample File", _
                                                      "Connection To the        'Sample File' query in the workbook.", _
                                                      "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""Sample File"";Extended Properties=""""" _
                                                      , "SELECT * FROM [Sample File]", 2
Workbooks("CognosDataCleaner2.xlsm").Connections.Add2 "Query - Transform File" _
                                                      , "Connection To the        'Transform File' query in the workbook.", _
                                                      "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""Transform File"";Extended Properties=""""" _
                                                      , "SELECT * FROM [Transform File]", 2

'Rename New Query Sheet to TEST1
Sheets("Sheet1").Select
Sheets("Sheet1").Name = "TEST1"
Sheets("TEST1").Select

Workbooks("Book1").Connections.Add2 "Query - Transform File", _
                                    "Connection To the        'Transform File' query in the     workbook.", _
                                    "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""Transform File"";Extended Properties=""""" _
                                    , "SELECT * FROM [Transform File]", 2
Workbooks("Book1").Connections.Add2 "Query - Sample File", _
                                    "Connection To the        'Sample File' query in the workbook.", _
                                    "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""Sample File"";Extended Properties=""""" _
                                    , "SELECT * FROM [Sample File]", 2
Sheets("TEST1").Copy

'SAVE PATH
ChDir SavePath
ActiveWorkbook.SaveAs Filename:= _
                      SavePath _
                      , FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
ActiveWindow.Close

End Sub

我已经创建了一个对象源路径，并在该路径中将其连接起来，因此它的值如下所示："目录"
A1 ="A2 =" A3 =目录或路径
=连接（A1、A3和A2）
我还将Source设置为String，使其也成为一个对象。

excel 创建PowerQuery时出现令牌错误

1条答案

相关问题

热门标签

最新问答