+ Reply to Thread
Results 1 to 16 of 16

Error trying to parse jSON file into excel sheet

  1. #1
    Registered User
    Join Date
    03-08-2010
    Location
    Sweden
    MS-Off Ver
    MS Office 365 Mac and Windows
    Posts
    94

    Error trying to parse jSON file into excel sheet

    Hello, I get this "error (13) Incompatible types" when trying to run the attached VBA. I know the json file is not all "stringified" (sample below) and I can't get pass this error.
    What is needed to get this pass the error? The MsgBox reads them all fine but I can't get it to write into the excel sheet...

    The error stops at this line:
    Please Login or Register  to view this content.
    Im using "GET" for the json and it looks like this...

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by soreno; 02-21-2021 at 09:20 AM.

  2. #2
    Valued Forum Contributor
    Join Date
    09-30-2018
    Location
    Vlaams Brabant Belgium
    MS-Off Ver
    365
    Posts
    456

    Re: Error trying to parse jSON file into excel sheet

    hi,

    maybe try without the .value ?
    or try Cstr(Item("Id"))
    Please be as complete as possible in your asking so it may save use all the time to rework the solution because you didn't give all the requirements. If you have a layout in mind please work it out first so we can adapt our solution to it. Thanks.
    If you have been helped, maybe you could click the *

  3. #3
    Valued Forum Contributor Haluk's Avatar
    Join Date
    02-14-2019
    Location
    Turkiye
    MS-Off Ver
    2010 - 64 Bit on Windows-11 (22 H2) 64 Bit
    Posts
    1,133

    Re: Error trying to parse jSON file into excel sheet

    Try this, but I'm not sure if it works on Mac.

    Do not forget to enter the correct URL to your JSon feed...

    Please Login or Register  to view this content.
    Last edited by Haluk; 02-20-2021 at 04:13 PM.

  4. #4
    Forum Expert Kenneth Hobson's Avatar
    Join Date
    02-05-2007
    Location
    Tecumseh, OK
    MS-Off Ver
    Office 365, Win10Home
    Posts
    2,573

    Re: Error trying to parse jSON file into excel sheet

    Your problem is probably the MAC API's. It ran fine for me after I removed the MAC API's.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    03-08-2010
    Location
    Sweden
    MS-Off Ver
    MS Office 365 Mac and Windows
    Posts
    94

    Re: Error trying to parse jSON file into excel sheet

    That worked! But how to add from the [JSON] "referentailKeys":[][/JSON[´] when it is an array? For example how would you pull the last array [JSON]"value":"112348435"[/JSON] and [JSON]"key":"AAAID"[/JSON] into the
    Please Login or Register  to view this content.
    ?
    One thing more it seems to remove spaces between words, tried to pull the "typeName":"Retail shop" and result was "Retailshop" = space is missing....
    Last edited by soreno; 02-21-2021 at 03:47 AM.

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Error trying to parse jSON file into excel sheet

    Administrative Note:

    We would very much like to help you with your query, however you need to include code tags around your code.

    Please take a moment to add the tags. Posting code between tags makes your code much easier to read and copy for testing, and it also maintains VBA formatting.

    Please see Forum Rule #2 about code tags and adjust accordingly. Click on Edit to open your post, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

    (Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  7. #7
    Registered User
    Join Date
    03-08-2010
    Location
    Sweden
    MS-Off Ver
    MS Office 365 Mac and Windows
    Posts
    94

    Re: Error trying to parse jSON file into excel sheet

    Quote Originally Posted by Kenneth Hobson View Post
    Your problem is probably the MAC API's. It ran fine for me after I removed the MAC API's.
    Yes it did work, but when pulling the data from the server using the "GET" method I passed the previous error (thank you for that) and now instead having "Object required (Error 424)". When checking the error it says "item = Nothing", the item is declared as Object so a bit lost here as the MsgBox shows that all the data is there but I can't somehow get it to write to the sheet...

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Error trying to parse jSON file into excel sheet

    Please see post #6 - don't ignore moderation requests.

    No further help to be offered until the OP has complied with the request. Thanks.

  9. #9
    Registered User
    Join Date
    03-08-2010
    Location
    Sweden
    MS-Off Ver
    MS Office 365 Mac and Windows
    Posts
    94

    Re: Error trying to parse jSON file into excel sheet

    Quote Originally Posted by Joske920 View Post
    hi,

    maybe try without the .value ?
    or try Cstr(Item("Id"))
    Tried this as well, passed the old error, the new one is now "Object required (Error 424)" telling me "item = Nothing". MsgBox shows all the data... thanks

  10. #10
    Registered User
    Join Date
    03-08-2010
    Location
    Sweden
    MS-Off Ver
    MS Office 365 Mac and Windows
    Posts
    94

    Re: Error trying to parse jSON file into excel sheet

    Excuse me but what do I do wrong, is it the json data I need to put [CODE] around? Else there is not much code written all is in the files I attached!

  11. #11
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Error trying to parse jSON file into excel sheet

    Yes, but you did it incorrectly - I have now corrected by changing JSON to CODE.

    Please make sure that you do this properly next time - any code at all needs wrapping in CODE tags as described above. Thanks.

  12. #12
    Valued Forum Contributor Haluk's Avatar
    Join Date
    02-14-2019
    Location
    Turkiye
    MS-Off Ver
    2010 - 64 Bit on Windows-11 (22 H2) 64 Bit
    Posts
    1,133

    Re: Error trying to parse jSON file into excel sheet

    Quote Originally Posted by soreno View Post
    That worked! But how to add from the [JSON] "referentailKeys":[][/JSON[´] when it is an array? For example how would you pull the last array [JSON]"value":"112348435"[/JSON] and [JSON]"key":"AAAID"[/JSON] into the
    Please Login or Register  to view this content.
    ?
    One thing more it seems to remove spaces between words, tried to pull the "typeName":"Retail shop" and result was "Retailshop" = space is missing....

    Try this;

    Please Login or Register  to view this content.

  13. #13
    Registered User
    Join Date
    03-08-2010
    Location
    Sweden
    MS-Off Ver
    MS Office 365 Mac and Windows
    Posts
    94

    Re: Error trying to parse jSON file into excel sheet

    Quote Originally Posted by AliGW View Post
    Yes, but you did it incorrectly - I have now corrected by changing JSON to CODE.

    Please make sure that you do this properly next time - any code at all needs wrapping in CODE tags as described above. Thanks.
    Ok, sorry a json file for me is not a code, just a string of text, I wanted to attach that json file to not "clutter" the Post but that was not allowed as an attachement... now I know thanks....

  14. #14
    Registered User
    Join Date
    03-08-2010
    Location
    Sweden
    MS-Off Ver
    MS Office 365 Mac and Windows
    Posts
    94

    Re: Error trying to parse jSON file into excel sheet

    Quote Originally Posted by Haluk View Post
    Try this;...
    Thanks that did help, have been trying to understand the code... if you want to pull out the first or middle part of the array in the sample data as the "keys" are the same under "referentialKeys"...
    There is one issue though, it only pick the last row of data in a long list. If I have 1000+ list of Ids and names etc. I only get one row in the sheet from the last row of data when it should be 1000 + rows... what is missing?

  15. #15
    Valued Forum Contributor Haluk's Avatar
    Join Date
    02-14-2019
    Location
    Turkiye
    MS-Off Ver
    2010 - 64 Bit on Windows-11 (22 H2) 64 Bit
    Posts
    1,133

    Re: Error trying to parse jSON file into excel sheet

    You asked for the last occurence of the values, in your message #5.

    If you want to fetch all occurences, then add the following red line in the code posted in message #12.

    Please Login or Register  to view this content.
    Last edited by Haluk; 02-21-2021 at 08:45 AM.

  16. #16
    Registered User
    Join Date
    03-08-2010
    Location
    Sweden
    MS-Off Ver
    MS Office 365 Mac and Windows
    Posts
    94

    Thumbs up Re: Error trying to parse jSON file into excel sheet

    Quote Originally Posted by Haluk View Post
    You asked for the last occurence of the values, in your message #5.
    Yes sorry my bad, I meant the last value in that referentialKey array.....
    Thanks it runs beautiful now and I'm happy

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Edit JSON parser code to add function to write JSON file
    By mc84excel in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 02-16-2020, 09:28 PM
  2. Replies: 1
    Last Post: 09-10-2019, 02:05 PM
  3. [SOLVED] Programmatically find Dropbox folder path using VBA (Parse JSON)
    By mc84excel in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-12-2019, 09:23 PM
  4. avoid parse json errro 10001 stop
    By incobart in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 12-17-2017, 04:06 AM
  5. Use Excel VBA to Get and parse an API JSON response
    By proverbguy in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-11-2017, 02:56 PM
  6. Parse / flatten nested JSON string in VBA
    By JasperD in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-23-2016, 08:45 AM
  7. Json file from URL to Excel
    By KK33317 in forum Tips and Tutorials
    Replies: 0
    Last Post: 03-13-2014, 07:39 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1