search

VBA-tools / VBA-JSON

JSON conversion and parsing for VBA
MIT License
1.76k stars 568 forks source link

The data required for this operation is not yet available #221

Open rickleliveld opened 2 years ago

rickleliveld commented 2 years ago

Hello,

When I try to retrieve data via a URL I receive a message: The data required for this operation is not yet available (error -2147483638 (8000000a).

This only happens when I run the macro for the first time. When I click debug and run the macro again, it does get data from the URL cleanly.

It seems that the data is not yet available so he cannot retrieve it. I've already tried making the code wait for 10 seconds or 1 minute, but that doesn't solve the problem.

Any idea?

 With http
        .Open "GET", "https://api.rentman.net/projectequipment?limit=5&offset=0&sort=-modified, false"
        .setRequestHeader "Content-type", "application/json"
        .setRequestHeader "Accept", "application/json"
        .setRequestHeader "Authorization", "Bearer " & authKey
        .send
    End With

Application.Wait (Now + TimeValue("0:00:01"))

Set JSON = ParseJson(http.responseText)
i = 2
For Each Item In JSON("data")
sht.Cells(i, 1).Value = Item("id")
sht.Cells(i, 2).Value = Item("modified")
sht.Cells(i, 5).Value = Item("equipment_group")
i = i + 1
Next

(text translated via Google translate)

houghtonap commented 2 years ago

Your issue is not a VBA-JSON issue, it has to do with the fact you are calling the Http open method asynchronously. The wait time probably is not long enough however, you should be calling ParseJson in the callback routine rather than the way it is coded. Alternately, your code is basically synchronous, so just add false after the url in the Http open call and the call will be synchronous.

But wait you are saying, I have False after the Url!? Well no, that comma false is inside the quotes for the Url, thus the async parameter is True by default.

See Microsoft's documentation https://docs.microsoft.com/en-us/previous-versions/windows/desktop/ms757849(v=vs.85)

rickleliveld commented 2 years ago

Hi Andrew,

Wow, thanks for your quick response. It works and I'm very happy with that, thnxs!

There is just another problem now. When I run the macro for the 2nd time, it doesn't refresh the data. I probably need to build somewhere that it has to wait for the responsetext?

Can you help me with that? What should I do?

Sincerely, Rick Leliveld

(text translated by Google)