VBA to Clear Cache in Excel

[Pinaceous]

Good Day All,

Is there a vba macro to clear cache in excel?

For example as in;

Clear Cache in Excel

Please let me know!

Thank you!
pinaceous

 

[Pinaceous]

That code does not get you to the window you showed.

It does actually but I had to use a button click in referring the macro on the main excel page not the vba page.

 

[Pinaceous]

I saw this on the web. Is it what you're looking for?

Application.RecentFiles.Maximum = 0

Hey *Skyybot!

I believe so but how can I tell that it worked?

Thanks,
pinaceous

 

[Jaafar Tribak]

I use office 2016 which doesn't have the "Delete cached files" entry under Options|Save menu so I can't give this shot but I think the best way to programmatically press that button is via Microsoft Active Accessibility.

Also, there might be a flag entry in the registry which may enable you to delete the cached files. Have you considered looking there ?

 

[Skyybot]

Hey *Skyybot!

I believe so but how can I tell that it worked?

Thanks,
pinaceous

As far as I can tell, if the file C:\Users\"UserName"\AppData\Local\Microsoft\Office\16\OfficeFileCache contains a file named "0" then the cache is clear. I have seen posts on other forums suggesting deleting that file (OfficeFileCache) but I think that is used for other Office apps as well and I'm unsure of how that will affect operation of other Office apps.

 

[johnnyL]

It does actually but I had to use a button click in referring the macro on the main excel page not the vba page.

Can you provide a link to a workbook with the code you mentioned that gets you to the window you displayed?

 

[Pinaceous]

Can you provide a link to a workbook with the code you mentioned that gets you to the window you displayed?

I'm having trouble with how to create a link for the workbook. Just create any new.xlsm as below with the button click and reference the macro 4 as above for it and it will work.

 

[Pinaceous]

I use office 2016 which doesn't have the "Delete cached files" entry under Options|Save menu so I can't give this shot but I think the best way to programmatically press that button is via Microsoft Active Accessibility.

Also, there might be a flag entry in the registry which may enable you to delete the cached files. Have you considered looking there ?

Hi Jaafar Tribak,

Do you know how to access the flag entry in the registry through vba?

Thanks,
pinaceous

 

[johnnyL]

I'm having trouble with how to create a link for the workbook. Just create any new.xlsm as below with the button click and reference the macro 4 as above for it and it will work.
View attachment 86211

You could upload it to any free file sharing site. I usually use This but you can use dropbox or any of your choice. Then supply the link to the file here.

Edit: I finally got it to work with the code you provided.

 

[johnnyL]

Ok the following code should take you right up to the edge:

    SendKeys "%"
    SendKeys "FT^S%D%D~{TAB}"

The following code would take you to the edge & actually pull the trigger:

    SendKeys "%"
    SendKeys "FT^S%D%D~{TAB}~"

You probably should add a final line to turn the num lock back on:

    SendKeys "{NUMLOCK}", True

 

[Pinaceous]

You could upload it to any free file sharing site. I usually use This but you can use dropbox or any of your choice. Then supply the link to the file here.

Edit: I finally got it to work with the code you provided.

Hi johnnyL,
I'll work on that and let you know.
Thank you,
pinaceous