clear excel cache -- or maybe something else?

[inosent]

i have a routine that goes to a web site and gets data. the data at the site changes often.

but there is a quirk.

in order to get the most recent data, i *always* have to quit excel completely, and then restart, re-run the routine to get the most recent data

i have manually cleared the cache and the cookies but i still get the same issue.

i have tried adding CInt(1000 * Rnd()) to the link to try and force the cache (or whatever is causing the trouble) to see the link as a new link to force it to give up the old data and bring in the new

but nothing works.

once i open excel, and get the data, i will not be able to get new data from the site

i am using

Dim objHTTP As Object
Set objHTTP = CreateObject("MSXML2.XMLHTTP")


objHTTP.Open "GET", [the link], false
objHTTP.send 


set objHTTP = Nothing

to get the data

is there some code that will emulate excel shutting down or something? somehow, somewhere excel is 'holing on' to something in re this particular site, or something like that.

all i know is the only way to fix the problem, to update and get the new data, i always have to close excel and then restart, which of course is a pain

any ideas ?

 

[eeek18]

Does the advice given in the Excel Help not assist?

1. Click a cell in the region of the worksheet where your query data was returned (also known as an external data range.

1. Click Refresh Data
   
   on the External Data toolbar.
2. A spinning refresh icon
   
   appears in the status bar to indicate that the query is running. Double-click the icon to check the status of the query.
3. Tips

1. You can have your data refresh automatically when the file is opened, at timed intervals, or in the background by clicking Data Range Properties
   
   on the External Data toolbar and selecting or clearing options under Refresh control.

• • If the data returned to your worksheet after a refresh isn't what you expected, check the Web page you are querying to make sure it is in the same location and that the data on it is provided in the same way as when you created the query.

 

[eeek18]

Noticed just as I hit return that you're using xml.

Does the Excel Help not assist then...


<TABLE class=OTbl cellSpacing=0 cellPadding=0 width="100%"><TBODY><TR><TD class=cdOTATtl width="100%">Refresh XML data </TD><TD style="PADDING-RIGHT: 0px; PADDING-LEFT: 12px; PADDING-BOTTOM: 0px; PADDING-TOP: 0px"></TD><TR><TD class=ACB style="PADDING-RIGHT: 12px; PADDING-LEFT: 12px; PADDING-BOTTOM: 10px; PADDING-TOP: 10px" width="100%" colSpan=3><!-- defs in --><!-- META NAME="lcid" CONTENT="1033" -->Note XML features, except for saving files in the XML Spreadsheet format, are available only in Microsoft Office Professional Edition 2003 and Microsoft Office Excel 2003.

• Click Refresh XML Data
  
  on the XML toolbar to import the latest data from the data source.

</TD></TR></TBODY></TABLE>
of course, assuming your using 2003 or later version...

 

[inosent]

thanks for the reply

well, basically nothing is being saved to the spreadsheet. objHTTP.responseText is processed independently of the spreadsheet, and the data is saved to a text file.

the problem i am having is after hitting [the link] the first time around, and i get the data, as long as i have that instance of excel open the .responseText from the same link will say no new data when in fact there is

so this has nothing to do with the spreadsheet, excel is more of an after thought. but somehow there is some diabolical cache thing going on because the link with the site is still active or something, i really cant say

i've used Set objHTTP = Nothing but that doesnt work either

i guess the question is in what way does excel hold its own cache (if that is the issue)? why is it, in order to establish a current connection to the site, why do i always have to shut excel down completely?

 

[inosent]

anybody? or has nobody ever heard of this issue b4?

 

[inosent]

for posterity, i solved this issue by using code to 1) clear the cache and 2) beefed up the logging out from the site. the issue was not 'technically' with excel, but at the site. however, the question of how to break a connection with a site w/o having to necessarily log off at the site, emuilating the quitting of excel and restartgin excel is still a mystery.

there is likely some sort of 'excel cache' somewhere, otherwise the site would not think the 'browser' session had ended when excel quit.

iow, excel in this case acts as a browser, storing a cookie somewhere, keeping the session between the site and excel active so long as excel is open.

once excel quits, the link with the site is severed, and the mystery cookie (wherever it is) gets killed

this question still has not been resolved

 

[jeff801003]

inosent,

Have you deleted the querytables and Workbook connections?

for Query Tables:

For Each x In Worksheets(wsTemp).QueryTables
        x.Delete
Next

For Connections:

For Each x In ThisWorkbook.Connections
        x.Delete
Next

Can you post your code for deleting the cache? I'd love to see how you did it.

Thanks!

 

[stwp86]

I know this is an old thread, but I struggled through this issue earlier in the week. The solution I found was to change xmlhttp to serverxmlhttp. Apparently, one of the benefits of xmlhttp is the caching. May not be the best , but it fixes the problem without have to deal with random strings and headers.