rohanrao_1
New Member
- Joined
- May 12, 2020
- Messages
- 4
- Office Version
- 365
- Platform
- Windows
Hey there, I am trying to make excel get automatically get data from a link (however the link changes based on certain inputs like ticker symbol, time intervals, to/from time) instead of manually typing out the inputs into the link, putting it into power query, performing transformations, and then loading it. It would take too long to do this for many tables so automating this would save me a lot of time. I have tried to record a macro that imports data from the JSON file that I'm trying to pull data from in power query. 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.
May 10 3:52 AM - Codeshare
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 https://finnhub.io/api/v1/stock/can...1390&to=1572910590&token=bqp253nrh5rced4gh1n0
The edits I make through powerquery are linked here - this can be put in the advanced editor.
May 10 4:07 AM - Codeshare
This is the desired output
This is the error message that I get
I have heard that this can be performed with a named range containing the URL and custom function, but don't know how I could do this in a way that will automatically query the right link and perform the appropriate transformations to the table. With a macro on other tables that didn't get the error, I was able to generate the link I want to pull data from (instead of typing it out and copy pasting it onto the query), perform all the modifications to the data through power query, and load it to the desired location with the press of a button.
If you could help me out, I would greatly appreciate it.
May 10 3:52 AM - Codeshare
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 https://finnhub.io/api/v1/stock/can...1390&to=1572910590&token=bqp253nrh5rced4gh1n0
The edits I make through powerquery are linked here - this can be put in the advanced editor.
May 10 4:07 AM - Codeshare
This is the desired output
This is the error message that I get
I have heard that this can be performed with a named range containing the URL and custom function, but don't know how I could do this in a way that will automatically query the right link and perform the appropriate transformations to the table. With a macro on other tables that didn't get the error, I was able to generate the link I want to pull data from (instead of typing it out and copy pasting it onto the query), perform all the modifications to the data through power query, and load it to the desired location with the press of a button.
If you could help me out, I would greatly appreciate it.
