• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Cleaning Excel cache via VBA

J

Jovica

New Member
I have a model in one sheet that uses external data fetched from web ( e.g. yahoo finance ) based on ticker symbol of a company that I input. Problem starts when I use VBA to loop through a column full of company tickers and output the results into another sheet. Example : input ticker -> get data from web, based on that ticker -> analyse -> put result into sheet2, next ticker and so on. After couple of tickers data retrieval stops and I get errors results. I assume that's because cash memory is full of web data and needs to be cleared.

My question is how to clean memory after few looped iterations, so that whole process could be automated?

Thanks.
 
No, actually I am using Excel add-in that extracts single values from a web page (web table), but I am guessing that also in the process whole web page gets pulled and is temporarily stored somewhere, so after a while I am starting to get blank results due to memory overflow (I assume). So the only solution known to me is to save/close Excel and start it again. Problem is that I have to do that more than 50 times for just one model testing.

Is there any way to clean cache like in web browser with VBA?
 
Hi,

if it's a code using QueryTable the cache is not used, each call the web page is re-read again …
And I never had such a case as yours …

Without sharing your code and the web page address, no help possible … What else ?

Regards !​
 
There is no code using QueryTable, I am using this addin http://groups.yahoo.com/neo/groups/smf_addin/info to retrieve single value from this site http://www.advfn.com/. Data retrieval is done with formula "=IFERROR(RCHGetTableCell(URL,L1,"Other Cash Flow Information","Free Cash Flow")/1000,"")" not VBA, only code that I am using is to create a database in another sheet with range.Value = range.Value and to change company symbol after data has been put into database

Code: 
Sub Macro1()
Dim iMyNumber As Integer
iMyNumber = 1 + iMyNumber
    Do

    Call AddTicker
    Application.Wait (Now + TimeValue("0:00:03"))
    Call Results
    Call DeleteTicker


    
    Loop While iMyNumber < 30

    Call ClearCache

End Sub



Btw, Call ClearCache macro is empty, and I know that 30 iterations doesn't work and that doesn't matter since number of successful iterations varies until I start to get blank results.
 
Nevermind, nothing helps. I've put goto exit if value is blank...at least I've eased my troubles to some degree.

Thanks.
 
You must log in or register to reply here.
Back
Top