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

%3CLINGO-SUB%20id%3D%22lingo-sub-1381493%22%20slang%3D%22en-US%22%3EError%20when%20recording%20macro%20and%20importing%20JSON%20file%20through%20power%20query.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1381493%22%20slang%3D%22en-US%22%3E%3CP%20class%3D%22_1qeIAgB0cPwnLhDF9XSiJM%22%3EHey%20there%2C%20I%20am%20trying%20to%20record%20a%20macro%20that%20imports%20data%20from%20a%20JSON%20file%20in%20power%20query.%20This%20is%20so%20that%20I%20can%20automate%20the%20process%20of%20excel%20getting%3CSPAN%3E%26nbsp%3Bthe%20data%20from%20the%20correct%20link%20instead%20of%20manually%20typing%20out%20the%20inputs%20into%20the%20link%2C%20putting%20it%20into%20power%20query%2C%20performing%20the%20custom%20function%2C%20and%20then%20loading%20it%20(and%20since%20I%20will%20be%20doing%20this%20for%20multiple%20timeframes%20for%20multiple%20stocks%2C%20it%20would%20take%20a%20very%20long%20time%20if%20this%20was%20not%20automated).%26nbsp%3B%3C%2FSPAN%3EUnfortunately%20on%20one%20of%20the%20tables%20that%20I%20am%20trying%20to%20import%2C%20I%20get%20an%20error%20right%20when%20I%20click%20%22close%20and%20load%20to%22.%20Exception%20from%20HRESULT%3A%200x800A03EC.%20it%20says%20that%20there%20are%20more%20details%20which%20I%20will%20link%20below%20-%20the%20full%20details%20are%20really%20long%20and%20I%20don't%20really%20understand%20everything%20here%2C%20but%20I've%20linked%20it%20anyways.%3C%2FP%3E%3CP%20class%3D%22_1qeIAgB0cPwnLhDF9XSiJM%22%3E%3CA%20href%3D%22https%3A%2F%2Fcodeshare.io%2F24oVj7%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20ugc%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fcodeshare.io%2F24oVj7%3C%2FA%3E%3C%2FP%3E%3CP%20class%3D%22_1qeIAgB0cPwnLhDF9XSiJM%22%3EWhat's%20peculiar%20is%20that%20I%20do%20not%20get%20an%20error%20when%20no%20macro%20is%20recording%20(the%20table%20will%20load%20to%20the%20right%20place%20with%20no%20issues)%2C%20however%2C%20when%20I%20record%20a%20macro%20and%20finally%20load%20the%20table%2C%20I%20get%20hit%20with%20the%20error%20message%20that's%20inserted%20below%20and%20no%20table.%20Furthermore%2C%20the%20situation%20becomes%20even%20more%20strange%20because%20I%20have%20tried%20loading%20the%20table%20without%20all%20the%20transformations%2Fchanges%20in%20power%20query%20and%20it%20sometimes%20loads%20(but%20since%20I%20remove%20some%20of%20these%20changes%2C%20it's%20not%20the%20table%20output%20that%20I%20want%20-%20unfortunately%20this%20never%20works%20when%20I%20apply%20all%20changes).%3C%2FP%3E%3CP%20class%3D%22_1qeIAgB0cPwnLhDF9XSiJM%22%3EI%20pull%20in%20the%20data%20from%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CA%20href%3D%22https%3A%2F%2Ffinnhub.io%2Fapi%2Fv1%2Fstock%2Fcandle%3Fsymbol%3DAAPL%26amp%3Bresolution%3D1%26amp%3Bfrom%3D1572651390%26amp%3Bto%3D1572910590%26amp%3Btoken%3Dbqp253nrh5rced4gh1n0%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20ugc%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Ffinnhub.io%2Fapi%2Fv1%2Fstock%2Fcandle%3Fsymbol%3DAAPL%26amp%3Bresolution%3D1%26amp%3Bfrom%3D1572651390%26amp%3Bto%3D1572910590%26amp%3Btoken%3Dbqp253nrh5rced4gh1n0%3C%2FA%3E%3C%2FP%3E%3CP%20class%3D%22_1qeIAgB0cPwnLhDF9XSiJM%22%3EThe%20edits%20I%20make%20through%20powerquery%20are%20linked%20here%20-%20this%20can%20be%20put%20in%20the%20advanced%20editor.%3C%2FP%3E%3CP%20class%3D%22_1qeIAgB0cPwnLhDF9XSiJM%22%3E%3CA%20href%3D%22https%3A%2F%2Fcodeshare.io%2Fax4gzd%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20ugc%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fcodeshare.io%2Fax4gzd%3C%2FA%3E%3C%2FP%3E%3CP%20class%3D%22_1qeIAgB0cPwnLhDF9XSiJM%22%3E%26nbsp%3B%3C%2FP%3E%3CDIV%20class%3D%22_2-UiOdhyj4wHBv7Rc2FeDr%20c1cmiB1jfdq4sxidlPDAx%22%3E%3CDIV%20class%3D%22_3Oa0THmZ3f5iZXAQ0hBJ0k%20_2LjgQiHLCZ9LDbCQx5KaOi%22%3E%3CDIV%20class%3D%22mceNonEditable%20lia-copypaste-placeholder%22%3E%26nbsp%3B%3C%2FDIV%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22CorrectOutput.png%22%20style%3D%22width%3A%20657px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F190816i05B7DD18C7500F1F%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22CorrectOutput.png%22%20alt%3D%22CorrectOutput.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FDIV%3E%3C%2FDIV%3E%3CP%20class%3D%22FJNSiirwoPtG58aeGw2Jx%22%3EThis%20is%20the%20desired%20output%3C%2FP%3E%3CDIV%20class%3D%22_2-UiOdhyj4wHBv7Rc2FeDr%20c1cmiB1jfdq4sxidlPDAx%22%3E%3CDIV%20class%3D%22_3Oa0THmZ3f5iZXAQ0hBJ0k%20_2LjgQiHLCZ9LDbCQx5KaOi%22%3E%3CDIV%20class%3D%22mceNonEditable%20lia-copypaste-placeholder%22%3E%26nbsp%3B%3C%2FDIV%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22LoadERROR.png%22%20style%3D%22width%3A%20691px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F190817i3DE328B767A00FAB%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22LoadERROR.png%22%20alt%3D%22LoadERROR.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FDIV%3E%3C%2FDIV%3E%3CP%20class%3D%22FJNSiirwoPtG58aeGw2Jx%22%3EThis%20is%20the%20error%20message%20that%20I%20get%3C%2FP%3E%3CP%20class%3D%22FJNSiirwoPtG58aeGw2Jx%22%3E%26nbsp%3B%3C%2FP%3E%3CP%20class%3D%22FJNSiirwoPtG58aeGw2Jx%22%3EIf%20any%20of%20you%20can%20help%20me%20out%2C%20I%20would%20truly%20appreciate%20it!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1381493%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EPower%20BI%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Highlighted
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.

https://codeshare.io/24oVj7

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/candle?symbol=AAPL&resolution=1&from=1572651390&to=1572910590&token=...

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

https://codeshare.io/ax4gzd

 

 

CorrectOutput.png

This is the desired output

 

LoadERROR.png

This is the error message that I get

 

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

0 Replies