Results 1 to 4 of 4

Thread: JSON file within VB
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Aug 2019
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default JSON file within VB

    Hello. I am loading up a JSON file with particular data and dumping it out to Excel.
    The process works, but when I try and grab the next set of data, I get an error.
    I am assuming the JSON file needs to be cleared/emptied so that the next 50 rows of data can be loaded.
    Does anybody have a command in VB to refresh/clear/empty the JSON file before the next set of data is loaded?
    Any input / assistance would be greatly appreciated.
    Thank you.

  2. #2
    Board Regular nardagus's Avatar
    Join Date
    Apr 2012
    Location
    PL
    Posts
    268
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: JSON file within VB

    Well I haven't tried to import JSON files yet, but:
    1) what kind of error do you GET?
    2) If you use loop for import you could reset all variables at the end the loop. I.e. for objects: set {object} = nothing, for string strvar ="", etc.

    N.

  3. #3
    New Member
    Join Date
    Aug 2019
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: JSON file within VB

    Hi. Here is the error I get.
    Run Time Error -2147467259 (80004005);

    Automation error
    Unspecified error
    No debug / specific line is highlighted.

    basically since i couldn't get a looping process to work - i set my datarange for the first 50 lines of data...get . load file...json , etc... export back to excel . this works
    when i then take that code and duplicate it to grab the next 50 lines of data (there is a 50 line max with the external url / mulesoft, etc..).. i need to do chucks of 50....
    is there a way to add a command to clear the file before getting the next 50 lines or do i need to go the looping avenue?

    summary of what i am trying to do:

    GETDATA:
    sheet = a
    cell a1 header (text) = field1
    cell b1 header (text) = field2
    cell c1 header (text) = field3
    cell d1 header (text) = field4
    cell e1 header (text) = field5
    data range could be a2 and that’s it or as much as a2:a1000
    loop until the end or max up 50 lines of data at a time
    using
    myitem("field1") = cell.Value
    myitem("field2") = cell.Offset(0, 1).Value
    myitem("field3") = cell.Offset(0, 2).Value
    myitem("field4") = cell.Offset(0, 3).Value
    myitem("field5") = cell.Offset(0, 4).Value
    items.Add myitem
    Set myitem = Nothing
    Set subitem = Nothing
    Again loop until the end of the data set but the GETDATA andOUTPUT can only be a max of 50 at a time
    Then goto OUTPUT:
    I have the logic to send and receive from URL working successfully
    OUTPUT:
    Logic to take the 50 rows of data or end of dataset (If less than50) and put it in (starting in cell F2)
    If dataset is greater > 50 then loop back to GETDATA (start onthe 51st line of data and continue for another 50 rows) then OUTPUT… repeatas needed until end of dataset.
    Some of my outputs can be ..lets say 820 rows of data.
    So the loop process for GETDATA and OUTPUT will needto happen… 16 times for the full 50 at a time and then a 17th time for the remaining 20 records
    For Each Item In output file that needs to be extracted in cellF2, etc..
    Maybe j is a variable?
    Sheets("A").Cells(j, 6).Value = Item("outputfield1")
    Sheets("A").Cells(j, 7).Value = Item("outputfield2")
    Sheets("A").Cells(j, 8).Value = Item("outputfield3")
    Sheets("A").Cells(j, 9).Value = Item("outputfield4")
    Sheets("A").Cells(j, 10).Value = Item("outputfield5")
    Sheets("A").Cells(j, 11).Value = Item("outputfield6")
    Sheets("A").Cells(j, 12).Value = Item("outputfield7")
    j = j + 1 ?

    after each block of 50 - clear file . move to the next 50 and repeat as needed.. until end of dataset..which could be 30 rows, 150 rows, 800 rows, etc...
    Thank you !


  4. #4
    Board Regular nardagus's Avatar
    Join Date
    Apr 2012
    Location
    PL
    Posts
    268
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: JSON file within VB

    I don't think I can help you with this. As I said. I have never tried playing with JSON. Sorry.
    And yes. Since you assign a value to "j" this also becomes a variable. And yes. In some circumstances playing around with variables used as a row number in Cells() can also be a pain.
    Good luck anyway.

    Nard
    Using Excel: 2007, 2010, 2013, 2016

    - be as much descriptive as you can. More info means faster solution and easier job for helpers.
    - when posting data samples use Forum Tools. Search for Forum Tools.
    - for vba code posting use [ code]Paste code here[/code ] tag
    - it would be nice to give your helpers a feedback if their solution actually worked
    - ...noone is perfect...


Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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