Error when recording macro and importing JSON file through power query.

Regular Visitor

Hey there, I am trying to record a macro that imports data from a JSON file in power query. This is so that I can automate the process of excel getting the data from the correct link instead of manually typing out the inputs into the link, putting it into power query, performing the custom function, and then loading it (and since I will be doing this for multiple timeframes for multiple stocks, it would take a very long time if this was not automated). Unfortunately on one of the tables that I am trying to import, I get an error right when I click "close and load to". Exception from HRESULT: 0x800A03EC. it says that there are more details which I will link below - the full details are really long and I don't really understand everything here, but I've linked it anyways.

What's peculiar is that I do not get an error when no macro is recording (the table will load to the right place with no issues), however, when I record a macro and finally load the table, I get hit with the error message that's inserted below and no table. Furthermore, the situation becomes even more strange because I have tried loading the table without all the transformations/changes in power query and it sometimes loads (but since I remove some of these changes, it's not the table output that I want - unfortunately this never works when I apply all changes).

I pull in the data from

The edits I make through powerquery are linked here - this can be put in the advanced editor.




This is the desired output



This is the error message that I get


If any of you can help me out, I would truly appreciate it!

0 Replies