Refreshing workbook opens data source

[ricerambo]

Hi All,

I've ran into quite a wall and was wondering if anyone had any suggestions.

I have an SSRS report(not the issue) with a subscription to write an excel file which is overwritten every 15 minutes. I use this spreadsheet as my database connection query in order to get the data I need to populate my dashboard. The problem is when someone refreshes at the same time the file is being overwritten; or two users are refreshing the data at the same time it opens up the source file and asks the user for 'Read/Write' or 'Cancel' option.

How do I keep the source file from opening in these situations? Thanks in advance!

[ricerambo]

After some extensive googling and barking up trees, I've come to the realization I am spinning my wheels. I did find changing the connection properties to disable background refresh did help with some of the performance issues. However I am still having an issue when I refresh all. The connection to my excel spreadsheet I am referencing locks it and if another user refreshes within two/three seconds it will open the data source file as read/write. I have trigger happy users so this will occur. If the user leaves the source file open it will not override with new data.


I was wondering if anyone knew any VB/Macro to delay the refresh all action if the data source is currently being used/locked. Would it be possible for a message box to appear stating refresh is waiting for other users. Sorry, VB is not a skill of mine (yet). I just dived in a few days ago so i'm trying to get up to speed.

Thanks in advance