JSON file within VB

[PeteB1]

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.

 

[nardagus]

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.

 

[PeteB1]

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 51^st 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 17^th 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 ?
<strike></strike>
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 ! <strike></strike>

 

[nardagus]

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.