运行时错误,丢失在excel vba模块错误

Question

当不传递变量

时，代码运行错误。

ActiveWorkbook.Queries.Add Name:="Table001 (Page 2)", Formula:= _
"let" & Chr(13) & "" & Chr(10) & "    Source = Pdf.Tables(File.Contents(""C:UsersabcDownloadsdata.pdf""), [Implementation=""1.3""])," & Chr(13) & "" & Chr(10) & "    Table001 = Source{[Id=""Table001""]}[Data]," & Chr(13) & "" & Chr(10) & "    #""Changed Type"" = Table.TransformColumnTypes(Table001,{{""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}, {""Column10"", type text}, {""Column11"", type text}, {""Column12"", type text}, {""Column13"", type text}, {""Column14"", type text}, {""Column15"", type text}, {""Column16"", type text}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Changed Type"""
ActiveWorkbook.Queries.Add Name:="Table002 (Page 3)", Formula:= _

如果我传递一个变量它会显示模块错误

Dim var As String

var = Range("A1").Value
ActiveWorkbook.Queries.Add Name:="Table001 (Page 2)", Formula:= _
"let" & Chr(13) & "" & Chr(10) & "    Source = Pdf.Tables(File.Contents(""var""), [Implementation=""1.3""])," & Chr(13) & "" & Chr(10) & "    Table001 = Source{[Id=""Table001""]}[Data]," & Chr(13) & "" & Chr(10) & "    #""Changed Type"" = Table.TransformColumnTypes(Table001,{{""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}, {""Column10"", type text}, {""Column11"", type text}, {""Column12"", type text}, {""Column13"", type text}, {""Column14"", type text}, {""Column15"", type text}, {""Column16"", type text}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Changed Type"""
ActiveWorkbook.Queries.Add Name:="Table002 (Page 3)", Formula:= _

Answer 1

sub export()

Dim var As String

var = Range("A1")。值

ActiveWorkbook.Queries。添加名称:="Table001 (Page 2)"公式:= _"let",杆(13),",杆(10),";Source = Pdf.Tables(File.Contents(var)， [Implementation="1.3"])，",杆(13),",杆(10),";Table001 = Source{[Id="Table001";]}[Data]，",杆(13),",杆(10),";#"改变Type";= Table.TransformColumnTypes(Table001，{{"Column1"; type text}， {"Column2"; type text}， {"Column3"; type text}， {"Column4";,＿",输入文本},{"Column6",输入文本},{"Column7",输入文本},{"Column8",输入文本},{"Column9",输入文本},{"Column10",输入文本},{"Column11",输入文本},{"Column12",输入文本},{"Column13",输入文本},{"Column14",输入文本},{"Column15",输入文本},{"Column16"; type text}})",杆(13),",杆(10),"in",杆(13),",杆(10),";#"改变Type";"ActiveWorkbook.Worksheets.AddActiveSheet.ListObjects。添加(SourceType:=0, Source:= _)提供商=Microsoft.Mashup.OleDb.1;数据源=$Workbook$;位置="Table001 (Page 2)"扩展属性=""＿目的地:=范围("$ 1美元"))。QueryTable.CommandType = xlCmdSql.CommandText = Array(SELECT * FROM [Table001 (Page 2)]").RowNumbers = False.FillAdjacentFormulas = False.PreserveFormatting = True.RefreshOnFileOpen = False.BackgroundQuery = True.RefreshStyle = xlInsertDeleteCells.SavePassword = False.SaveData = True.AdjustColumnWidth = True.RefreshPeriod = 0.PreserveColumnInfo = True.ListObject。DisplayName = "Table001__Page_2".Refresh BackgroundQuery: = False以

End With

结束子

var = Range("A1")。值将从CELL A1

获取pdf文件路径。

Answer 2

您仍然没有固定变量的使用。这是错误的(第四行，var在其中):

var = Range("A1").Value
ActiveWorkbook.Queries.Add Name:="Table001 (Page 2)", _
Formula:= "let" & Chr(13) & "" & Chr(10) & _ 
" Source = Pdf.Tables(File.Contents(var), [Implementation=""1.3""])," & _

这可能会起作用(但与您的错误描述不匹配，所以这可能只是问题的一部分):

var = Range("A1").Value
ActiveWorkbook.Queries.Add Name:="Table001 (Page 2)", _
Formula:= "let" & Chr(13) & "" & Chr(10) & _ 
" Source = Pdf.Tables(File.Contents(" & var & "), [Implementation=""1.3""])," & _

我真的建议您备份，从一个几乎什么都不做的简单测试过程开始，一点一点地添加，一点一点地进行检查。这里有很多,很难确切地说,这个问题可能说谎。