Working from SharePoint, cache issue, Discard Changes fix.

Luke777

New Member
Joined
Aug 10, 2020
Messages
25
Office Version
  1. 2007
Platform
  1. Windows
Hi all,

I have a workbook stored in SharePoint which is opened in the app to allow for VBA operations (I shall refer to this workbook as wbA).

As part of its operations, it references two other workbooks, which are also on SharePoint. The fact that there are *two* referenced workbooks seems unimportant to me, so for the purpose of this explanation, I shall pretend there is only one and refer to it as wbB.

wbB is a daily report, its information is *always* unique to that day. wbB is also a brand new file created daily by another piece of software. Once a new report is generated, the old file is simply deleted and replaced with the new report of the same name.

The code itself runs perfectly. But since moving over to SharePoint, I've been experiencing a weird caching issue. At a point in the code wbB is opened up (using the SharePoint URL) and some information is copied to wbA. However, wbB is being cached locally, so when the following day's report is created and wbA comes to operate on it, the cached version of the file is opened instead of the new one (despite the code explicitly using wbB's URL).

I've tried using VBA to delete the cached files which is janky to say the least, and manually deleting them also works fine.

However, a better workaround has sort-of presented itself. When wbB is opened by the code, a message below the ribbon says something along the lines of "a new version is available on the server", pressing "discard changes" then forces wbB to open the relevant version of itself.

Is there a way do click "discard changes" using VBA? I've briefly tried a few possible methods, but no success so far. I assume the solution is a very short line after wbB.open, but I can't quite find the correct method.

Anyone have a solution to this one? I suspect I'm not a lone with it either.

Oh, and I should point out that I'm not in a position to make any changes to the way SharePoint's settings are configured.

Thanks for the help!
 

Some videos you may like

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

Watch MrExcel Video

Forum statistics

Threads
1,127,348
Messages
5,624,145
Members
416,014
Latest member
MickP69

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top