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

Okatzy

New Member
Joined
Jul 28, 2017
Messages
2
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?
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
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?
 
Upvote 0
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 :)
 
Upvote 0
... 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
Code:
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
 
Upvote 0

Forum statistics

Threads
1,224,820
Messages
6,181,157
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top