Possible to replace multiple power queries with vba?

[ST_Gjallarhorn]

Hello excel gurus!
I've been using power query to make API calls with unique token/API keys, there are 3 different data formats, and they range from small to medium amounts of data (some are under 1000 rows, others are under 20) but the I do have 1 big power query connection that contains 600k data points. The raw data is all in JSON, and power query does a flawless job turning it all into tables with heading, and every table is in its own sheet in case I ever need to actually look at them individually. I have other sheets that combine the data and reference the big table, and contains the actual statistical analysis stuff. So things have been working fine for my uses. However, I'm up to 50+ connections (50+ API keys). I've set up these connections over time by hand.

I wish I started exploring earlier but I'm hoping to use vba to make this more scalable (I will need to add more connections in the future) and more manageable (if I need to delete/replace/re-purpose API keys without creating new connections).

I envision using vba to pull the API data, then have it convert the JSON into a table (this is the biggest hurdle/mystery for me, I'm not sure how to approach this), output the data into its own sheet.

(thinking out loud) I would have a master list of token/API keys, so the vba code can be run based on how many are on that master list. This would replace 99% of the power query connections, and in lieu of refreshing connections, I would run the vba script when I want to refresh the data. I guess the only connection I wouldn't mind keeping is the 600k data one, it might be better to leave that alone.

I appreciate any guidance.

[jdelano]

The short answer is yes, you can do everything that Power Query does with VBA. However, as you've stated, it does everything for you with a few clicks whereas with a VBA macro you'll need to code all the requests to the APIs and the translation from JSON to spreadsheet data.

There are a few options to parse JSON in VBA (https://myexcelgenius.com/getting-da...mat-using-vba/) and VBA for requesting data from a web API (https://codingislove.com/http-requests-excel-vba/) you can even create new PQ connection using VBA (https://www.excelcampus.com/vba/powe...ly-all-tables/) now the pros are that you have much more flexibility to then add processing post data pull.

Maybe just create a sheet with the connection information required by PQ and where you want the resulting data to be placed. You can use the VBA example to create the PQ (if there isn't one) as you loop through that list.