I have an excel workbook that is to be used as a report, that is distributed to different people. The workbook gets data from an input file (another Excel file) that is not available to all the people who are receiving the report.
My problem is, that when the people who does not have access to the input file opens the report, the workbook tries to update the data, but fails, as the user does not have access to the input file. Is there a way to stop the sheet from auto-updating when it is opened?
Based on the versions of excel I have used, Excel 2013 and 2016 that does not sound like the standard behavior I have seen.
There would be a couple things to check:
1) Is there macros in the file, which run on open and force refresh. VBA command to refresh a data model being the following ThisWorkbook.Model.Refresh
2) Right click on the pivot tables --> Pivot table settings --> Data, check if refresh data when opening the file is selected. Not sure on this one but it might be forcing a refresh of the model.
3) If you are using power query, open the queries & connection tab, right click each query and select properties. There is an option for Refresh data when opening the file.