So I was able to record a macro and get the code for my Power Query PDF Import.
This works pretty well for a known PDF page count, but I am wondering how to execute this for a dynamic PDF. The structure in the PDF will be the same, but the number of pages could differ
I am new to Power Query code, but I am thinking it needs to be something along the lines of this process:- User selects a file. That file is stored as the Data Source for that import. PDF Filename will change each time
- VBA code reads number of tables in selected PDF file via Pdf.Tables(File.Contents(""" & PDF_Path & """), [Implementation=""1.3""]) (I can do this with Advanced Editor Power Query M script, but need it in code)
- Queries are created (or run from stored queries) based on the number of tables.
- Data is entered into Excel via ActiveSheet.ListObjects.Add commands for each query
The dynamic part is what I am having issues with. Is it possible to store a data Souce via VBA, then use that for queries and have all the variables in the query be dynamic? Same for entering into Excel
Any good examples of this?
Bookmarks