Deleting contents of Json object in VBA

[Edsancar]

I'm importing data using API servers into VBA. I use the JsonConverter library from Github.

Due to the process I'm doing data changes and I have to get the data again, unfortunately if I run the Get command a second time, the data is not overwritten and my old data will be shown.

I'm thinking I have to erase the contents from the Object variable but I have not been succesful, how do I handle this

The following is the routine I use to get my data from the API server.

Dim testData As Object
Dim hReq as Object
Dim testData as object
Dim Response as String

    strUrl = "[URL]https://myurl.com/gb/backend/api/v2/testdata[/URL]"
    Set hReq = CreateObject("MSXML2.XMLHTTP")
    With hReq
        .Open "GET", strUrl, False
        .setRequestHeader "Authorization", "Bearer " & Key")
        .send
    End With

    Response = hReq.responseText
    Set testData = JsonConverter.ParseJson(Response)

If I print Response after the first run and Then after the second run (where the data has changed) both texts will be the same)

How can I release the contents of Response and testData

Thanks

 

[Edgar_]

Sounds like a server thing.

1. Remove the extra testData declaration
2. Enable Option Explicit to catch that kind of problem
3. Make sure you're sending the request exactly as required by the server, are you sure the required parameters have been sent?
To rule out whether the VBA code is causing the error and not the server, I suggest using an additional tool and seeing if you get the same behavior.

 

[John_w]

If I print Response after the first run and Then after the second run (where the data has changed) both texts will be the same)

It could be that the XMLhttp object is reading the URL response from the local cache instead of requesting it anew from the server. To prevent this, call the DeleteUrlCacheEntry API before sending the request.

Add the following code at the top of the module between Option Explicit (if present) and the first VBA procedure/function:

#If VBA7 Then
    Private Declare PtrSafe Function DeleteUrlCacheEntry Lib "Wininet.dll" Alias "DeleteUrlCacheEntryA" (ByVal lpszUrlName As String) As Long
#Else
    Private Declare Function DeleteUrlCacheEntry Lib "Wininet.dll" Alias "DeleteUrlCacheEntryA" (ByVal lpszUrlName As String) As Long
#End If

Amend your code thus:

    strUrl = "https://myurl.com/gb/backend/api/v2/testdata"
    DeleteUrlCacheEntry strUrl
    Set hReq = CreateObject("MSXML2.XMLHTTP")

 

[Edsancar]

Thanks, Edgar and John have a busy night tonight but will work with that Thursday Night and Friday (USCentral Time)

Will report the results, the API administrator confirmed my requests were correct and somehow the data is lingering (She does not use VBA, so she couldn´t help)

Thanks again for your prompt answers

 

[Domenic]

Another alternative might be to use the ServerXMLHTTP object, instead of the XMLHTTP object. Unlike XMLHTTP, the ServerXMLHTTP object will always make a server request.

Set hReq = CreateObject("MSXML2.ServerXMLHTTP")

Hope this helps!

 

[Edsancar]

Domenic and John

Both answers worked perfectly, in both cases the data was refreshed and both are easy to implement. I tested on a small routine that validates that the user linked to Token (authorization key) has the correct credentials to access the database. I tested different tokens but once I entered a valid token, it would always display the valid user informaton regardless if the token was changed or not (Even a blank token gave the old info, but after implementing either of the both solutions it works as intended. Did also some refreshing of dynamic data and it also works fine. There is a noticeable time increase to get all data, but it is expected since it's is getting everything back. MIght try no to erase the cache for some information that is not as dynamic and changes twice per week.

Thanks a lot for your prompt answers and solutions.

I think I need to put the check mark on the solution but I don´t know If i can put in on both, will surely try.