I hope this question is allowed because I'm using Excel instead of Power BI, but because they both share the same technique when it comes to queries I figured I could ask it here. Please tell me if I'm wrong.
- I have several queries in an Excel file, the 'Excel query file'.
- This 'Excel query file' has sheets with Pivot tables, with the data from these queries.
- This 'Excel query file' is through VBA configured as always read only when opening.
- Multiple users have different 'Excel work files' and in each of them there is a reference to the 'Excel query file' with the queries mentioned above. (Reference added through VBA editor, this means when their 'Excel work file' is opened, the 'Excel query file' is opened in the background and can be accessed.)
- Unfortunately I have no environment to test this, so my question is as follows:
- When the 'Excel query file' with queries and pivot tables is opened by multiple users at the same time as read only, is it possible that these queries run correct for each user when they are loading different data into them? Will there occur errors when multiple users are refreshing at the same time? Will data get mixed up? My theory: these problems don't occur, because everyone opens the 'Excel query file' as read only, and has their own instance of the file. When their 'Excel work file' is closed together with its reference, nothing is saved back and the query is defeaulted to its empty state.
- The reason I would like to do it like this, is because I would be able to modify 1 Excel query file and automatically let everyone use the modified queries. Also, the 'workfiles' could be less heavy this way.
Thanks in advance for helping out. 🙂
