VBA Data Connection refresh opens up relevant workbook if in use - Please Help?

[Okatzy]

Context: I have been creating a dashboard to show a summary of various other workbooks. To do this I have setup data connections, which at the beginning of my macro are all refreshed using, Refresh All.


The issue: If one or more of the workbooks, for which the data on my spreadsheet is refreshing from, are open by another user on my work computer system, then workbook is opened up as a 'read only' version which then stops the macro as it becomes the active workbook.


My question: I am aware that i might be able to get around this by having the macro close the workbook as soon as it opens but i want to know if there is a way to stop the workbook opening in the first place, if other users are using the workbooks that the macro is trying to refresh?

 

[Fazza]

Details of what is being done are unclear (to me).

AFAIK, having data connections & using refreshall doesn't need to open workbooks. (So I don't understand why files are opening, and becoming read only. And also don't understand the comment about having the macro close the workbook as soon as it opens.) Regardless of this detail, it is familiar to me that if other files are open refreshing queries against them will error.

If opening the workbooks is part of the refresh process and if the file is already open by others causes problems, then yes, have your code check if the file is already open before trying to open it so you can avoid the error.

A different approach that might take a little more coding is to change the way the data connections (queries) operate. Instead of querying files that can be potentially open & cause errors, have those files that you want to query, on workbook close, create a copy (or even a new data only file with just the data) that exists only for querying. It might be best in a special subdirectory - created just to keep these special copies of the file. These files in their special location are not used by anyone, just the VBA to replace/update them on closure of their parent files. These special files can be relied on to be more-or-less always closed & available for your file to readily query without having to worry about files being open. A bit more advanced, it could even be a single mdb file that the files write to. This can be done without MS Access being installed. Gives advantage of full SQL (DELETE queries in particular) & ADOX (for working with tables) capabilities. This might be an overkill, & maybe simple copies will suffice, or txt files, or csv.

OK?

 

[Okatzy]

Hi Fazza,

The issue is that refreshall refreshing the data connections does open up the workbooks that are in use on other computers, when it shouldn't. Therefore it disrupts my code as the workbook is no longer the active workbook.
If I have the macro check if the workbooks are open, it doesn't fix my issue, as i need to be able to get the data regardless of whether they are open or not.

I like your workaround for it. However, I cannot force close them on other users computers. And I'm rather new to this, so I'm not sure how to go about creating a copy to a location and then referencing it. Is there a way to have a workbook that acts as an identical copy to another workbook?

Thanks

 

[Fazza]

... so I'm not sure how to go about creating a copy to a location and then referencing it. Is there a way to have a workbook that acts as an identical copy to another workbook?

code to create a copy on file closure. I've assumed sub-directory 'MyCopy' in same path as file

Private Sub Workbook_BeforeClose(Cancel As Boolean)


    With ThisWorkbook
        .SaveCopyAs .Path & Application.PathSeparator & "MyCopy" & Application.PathSeparator & .Name
    End With
    
End Sub

reference it instead of the files you already reference. exactly how you do that will depend on many things. such as how the queries are set up & your Excel version. basically however you have the queries now, just change them to instead reference the copied file

having a cloned copy (whilst the file is open) I can't help with