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

CLINT WILLIAMS

Board Regular
Joined
Nov 8, 2011
Messages
69
Office Version
  1. 365
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.
================================================================================

Rich (BB code):
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
 
Last edited by a moderator:

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

CLINT WILLIAMS

Board Regular
Joined
Nov 8, 2011
Messages
69
Office Version
  1. 365
Hi is there anyone that can assist me with this please. Would really appreciate it.

Thanks
Clint
 

Forum statistics

Threads
1,147,518
Messages
5,741,637
Members
423,675
Latest member
Dea21

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top