我正在开发一个宏VBA的导入表中存在的.xml文件,存储在同一文件夹的excel文件。我想导入每个表在自己的工作表(在意大利被称为“Foglio”)。
下面我报告的代码。我得到一个错误,它看起来像文件名或路径没有正确定义。
Sub Vai()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Dim fso As Object
Dim folder As Object
Dim file As Object
Dim percorso As String
Dim nome As String
Dim n As Integer
n = 2
Set fso = CreateObject("scripting.Filesystemobject")
Set folder = fso.GetFolder(ThisWorkbook.Path)
For Each file In folder.Files
percorso = file.Path
Sheets.Add.Name = "Foglio" & n
''The following part was made wit macro recorder and I made some modifications on the resulting code. I think the problem is here.
ActiveWorkbook.Queries.Add Name:="Letture_canali", Formula:= _
"let" & Chr(13) & "" & Chr(10) & " Origine = Xml.Tables(File.Contents("" & percorso & ""))," & Chr(13) & "" & Chr(10) & " Table0 = Origine{0}[Table]," & Chr(13) & "" & Chr(10) & " #""Modificato tipo"" = Table.TransformColumnTypes(Table0,{{""index"", Int64.Type}, {""ch1"", Int64.Type}, {""ch2"", Int64.Type}, {""ch3"", Int64.Type}, {""ch4"", Int64.Type}, {""encoder1"", Int64.Type}, {""encoder2"", Int64.Type}, {""te" & _
"mpo"", Int64.Type}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " #""Modificato tipo"""
With Worksheets("Foglio" & n).ListObjects.Add(SourceType:=0, Source:= _
"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=Letture_canali;Extended Properties=""""" _
, Destination:=Range("$A$1")).QueryTable
.CommandType = xlCmdSql
.CommandText = Array("SELECT * FROM [Letture_canali]")
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.ListObject.DisplayName = "Letture_canali"
.Refresh BackgroundQuery:=False
End With
n = n + 1
Next
End Sub
有人知道问题出在哪里吗?
1条答案
按热度按时间nhhxz33t1#
试试这个: