PowerQuery Automation

[nBow]

My question is this, is there a way to automate power query in vba, for example if I record a macro of me getting data from a PDF I open the pdf in power query I choose the option select multiple in the menu, I choose two pages and then I load it to a table. But when I go back to the macro, all the data is defined. I know how I could change the display name of the pages loaded, but the query to table formatting in VBA is always set to {Column1: type x} for every column in the original pdf. I just want to load every page and automatically add the same amount of columns that are in the pdf into excel without defining each column in code for the macro, because some pdfs have 3 pages some have 2, they have different formatting on each page and each type. The rest of my macro does the formatting, but is there a way to basically do the process that powerquery does automatically in the macro. Power query already automatically assigns columns and types, I just want that in my vba macro as well, because the macro as is will open a dialogue box to select the pdf, load each page of that pdf to query and then into a data table on a new worksheet(what I need), and all my formatting code runs after to make the data uniform combine the tables and then draw that data to a final page. If anyone has any idea of how to basically make the code less finite and more variable let me know, I can also post the recorded macro, for uploading but I assume if you have knowledge of pq in vba you know what I'm talking about here. Let me know if you need more info and thank you to anyone who can help me.

 

[Shortmeister1]

This should help:

How to automate a power query in VBA?

I have data in sheet 1. Normally I go to power query and do my transformations, then close, and load to an existing sheet 2. I would like to automate this using VBA, where I can just run my power q...

stackoverflow.com

 

[nBow]

This should help:

How to automate a power query in VBA?

I have data in sheet 1. Normally I go to power query and do my transformations, then close, and load to an existing sheet 2. I would like to automate this using VBA, where I can just run my power q...

stackoverflow.com

Thank you for the response, I'll try it out, but I think this still needs to promote headers for the files it loads. That's the main issue I'm running into. When you manually import data power query generates columns with headers, but I can't seem to find any kind of call in the vba library that does this, even recording the macro just records a hardcoded function for each page loaded. Still this may help. If I still run into the issue with it wanting me to define the columns it loads, maybe I can use this as a template and create my own function to generate column names.
Currently importing a pdf for reference is no issue but what I'm struggling with is:
1. Automatically selecting pages to load rather than tables
2. generating columns for those pages