Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old July 12th 19, 09:15 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2009
Posts: 11
Default 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.

  #2   Report Post  
Old July 12th 19, 07:01 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2016
Posts: 11
Default EXCEL VBA Caching GET request?

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=201902022 00423"


--
Adrian C
  #3   Report Post  
Old July 12th 19, 07:56 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2009
Posts: 11
Default EXCEL VBA Caching GET request?

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=201902022 00423"


--
Adrian C


worked a treat! thanks Adrian.


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
API Call Caching? armsiee Excel Programming 0 July 12th 19 06:37 AM
caching in excel murali velaga Excel Programming 5 May 12th 09 12:11 PM
Excel + MS Query + table definition caching Tony Excel Discussion (Misc queries) 0 August 14th 07 12:30 PM
Caching? Dean[_8_] Excel Programming 21 November 8th 06 09:38 PM
EXCEL CACHING ... affordsol Excel Programming 0 February 23rd 06 05:11 PM


All times are GMT +1. The time now is 09:56 AM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017