Sometimes returns old (possibly cached?) values from Workbooks in SharePoint

Question

There are just under 2,000 excel reports in an SharePoint (Office 365) website and I am required to run a main report twice daily to get values from all of those 2,000 reports. I use something similar to the following to get the information from these reports:

ActiveCell.Value = "='https://oursite.sharepoint.com/" & path2 & "/[" & Filename & "]Sheet1'!$D$9"

We define path2 / filename above which are all fine.

This normally works fine and brings back the correct results however sometimes it'll bring old values through even though nobody has changed the reports on SharePoint.

I don't have any prior experience to working with SharePoint other than this so was wondering if there is some automatic caching or something similar?

If so is there a way to turn it off as we only want the latest results, perhaps something in the connection string used in Excel?

Connection String looks like:

Provider=Microsoft.Office.List.OLEDB.2.0;Data Source="";ApplicationName=Excel;Version=12.0.0.0

Answer 1

Essentially, you can use the program in your start menu "Microsoft Office Upload Center" to set cached files to be removed as soon as you close them on the profile used to run these reports.

It will slow you down slightly opening other documents but sounds like the extra time might be worth the extra accuracy.

The link below should help to understand SharePoint's cache process.

Office Document Cache settings