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

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,231
Messages
5,768,938
Members
425,506
Latest member
AndreaWorkPlace

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