Create Power Query with source and name as variable to import PDF

[CLINT WILLIAMS]

Hi

I am trying to import a pdf into power query, I found this code below and trying to alter it to try and achieve this, the section in blue below needs to replaced with the the red. Also would it be possible for the code to import all the tables but this would also vary or does queryname do this? Forgive if I am asking silly questions still feeling my way through the more complexed codes.
================================================================================

Sub Macro1()
  
    Dim queryName As String, sourceFullName As String
    Dim pqDestinationCell As Range
  
    With ActiveSheet
        queryName = Replace(.Range("A1").Value, " ", "_")
        sourceFullName = .Range("A2").Value
        Set pqDestinationCell = .Range("A3")
    End With
  
    ActiveWorkbook.Queries.Add Name:=queryName, Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Quelle = Csv.Document(File.Contents(""" & sourceFullName & """),[Delimiter="","", Columns=10, Encoding=1252, QuoteStyle=QuoteStyle.None])," & Chr(13) & "" & Chr(10) & "    #""Höher gestufte Header"" = Table.PromoteHeaders(Quelle, [PromoteAllScalars=true])," & Chr(13) & "" & Chr(10) & "    #""Analysierte JSON"" = Table.TransformColumns(#""Höher gestufte Header"",{{""<OPEN>"", Json.Docum" & _
        "ent}, {""<HIGH>"", Json.Document}, {""<LOW>"", Json.Document}, {""<CLOSE>"", Json.Document}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Analysierte JSON"""
 

 ActiveWorkbook.Queries.Add Name:="Table003 (Page 1-5) (2)", Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Source = Pdf.Tables(File.Contents(""C:\Users\om05099\Desktop\RPS EASYFILE\2021\UPDATES\30082021\RPS SOA 2021 30082021.pdf""), [Implementation=""1.2""])," & Chr(13) & "" & Chr(10) & "    Table003 = Source{[Id=""Table003""]}[Data]," & Chr(13) & "" & Chr(10) & "    #""Changed Type"" = Table.TransformColumnTypes(Table003,{{""Column1"", type date}, {""Column2"", type text}, {""Column3"", type text}, {""Column4"", typ" & _
        "e text}, {""Column5"", type text}, {""Column6"", type text}, {""Column7"", type text}, {""Column8"", type text}, {""Column9"", type text}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Changed Type"""
  
 
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""" & queryName & """;Extended Properties=""""" _
        , Destination:=pqDestinationCell).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [" & queryName & "]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.Name = queryName 'also assigns same value to .ListObject.DisplayName
        .Refresh BackgroundQuery:=False
    End With
  
End Sub

Regards
Clint

 

[CLINT WILLIAMS]

Hi is there anyone that can assist me with this please. Would really appreciate it.

Thanks
Clint