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!
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!
