Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > microsoft.public.excel.programming > #111014 > unrolled thread

EXCEL VBA Caching GET request?

Back to article view | Back to microsoft.public.excel.programming

Contents

  EXCEL VBA Caching GET request? armsiee <sjdolding@gmail.com> - 2019-07-12 01:15 -0700
    Re: EXCEL VBA Caching GET request? Adrian Caspersz <email@here.invalid> - 2019-07-12 19:01 +0100
      Re: EXCEL VBA Caching GET request? armsiee <sjdolding@gmail.com> - 2019-07-12 11:56 -0700

#111014 — EXCEL VBA Caching GET request?

I have developed a simple PHP CRUD Rest API with Excel interfacing as the front end UI. 

When spreadsheet loads opens a form which fires off a GET request using the following method: 

Sub getData() 

Dim ws As Worksheet 
Dim jsonText As String, sUrl As String, response As String 
Dim xmlHttp As New MSXML2.XMLHTTP60 

Dim JSON As Dictionary 

Dim i As Long 
Dim Item As Object 
Dim comp As Dictionary 

    Set var = Nothing 
    Set JSON = Nothing 
    response = "" 
    
    sUrl = "http://xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx" 
    
    
    With xmlHttp 
        .Open "GET", sUrl, False 
        .Send 
    End With 
    
    response = xmlHttp.ResponseText 
    Debug.Print response 
    Set JSON = JsonConverter.ParseJson(response) 
    ReDim var(JSON("records").Count, 5) 
    i = 0 
    For Each comp In JSON("records") 
        var(i, 0) = comp("CompId") 
        var(i, 1) = comp("CompName") 
        var(i, 2) = comp("Address1") 
        var(i, 3) = comp("Address2") 
        var(i, 4) = comp("Address3") 
        var(i, 5) = comp("PostCode") 
      
        i = i + 1 
    Next 
    
    Set xmlHttp = Nothing 
    
    Debug.Print "finished" 
End Sub 

Which works great gets the data and displays on form. 

Via another routine i can add a new company record.  and verify that i can see it on the web server. 

However, when i next fire the GET method.  It does not return the new record.  I have placed some debugging on the Server and it only registers the initial call.  any subsequent calls arent being logged.   

If i close Excel down and reopen i get the new record.  I destroy all the variables after use.  I am not sure where to go next?  ANy pointers greatfullly received. 

[toc] | [next] | [standalone]

#111015

On 12/07/2019 09:15, armsiee wrote:

<snip>

> 
> If i close Excel down and reopen i get the new record.  I destroy all the variables after use.  I am not sure where to go next?  ANy pointers greatfullly received.
> 

The response from your URL is being cached somewhere in MSXML2.XMLHTTP60

Put a random parameter like a timestamp to the end of the URL

  i.e.

    sURL="http://xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx&t=20190202200423"


-- 
Adrian C

[toc] | [prev] | [next] | [standalone]

#111016

On Friday, 12 July 2019 19:01:58 UTC+1, Adrian Caspersz  wrote:
> On 12/07/2019 09:15, armsiee wrote:
> 
> <snip>
> 
> > 
> > If i close Excel down and reopen i get the new record.  I destroy all the variables after use.  I am not sure where to go next?  ANy pointers greatfullly received.
> > 
> 
> The response from your URL is being cached somewhere in MSXML2.XMLHTTP60
> 
> Put a random parameter like a timestamp to the end of the URL
> 
>   i.e.
> 
>     sURL="http://xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx&t=20190202200423"
> 
> 
> -- 
> Adrian C

worked a treat!  thanks Adrian.

[toc] | [prev] | [standalone]

Back to top | Article view | microsoft.public.excel.programming

csiph-web