Run Time ERROR, Missing module error in vba excel-CodePudding

the code run fines when without passing variable

ActiveWorkbook.Queries.Add Name:="Table001 (Page 2)", Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Source = Pdf.Tables(File.Contents(""C:\Users\abc\Downloads\data.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:= _

if i pass a variable it shows module error

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:= _

CodePudding user response：

sub export()

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.Worksheets.Add With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _ "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""Table001 (Page 2)"";Extended Properties=""""" _ , Destination:=Range("$A$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

End With

End sub

var = Range("A1").Value will get the pdf filepath from CELL A1

CodePudding user response：

You still haven't fixed the use of your variable. This is wrong (fourth line, with var in it):

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""])," & _

this might work (but doesn't really match your error description, so it's probably only part of the problem):

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""])," & _

I would really recommend you to back up, start with a simple test procedure that does almost nothing, and add to it bit by bit, little by little, checking as you go. There is a lot going on here and it is very hard to say where exactly the problem might lie.