I have a table in a file (that I'll call my Working file, for the purposes of this question) which lists out services and has an on/off selection for each service, similar to the below:
I have a macro that runs through this list - If the service is turned off, the macro moves on to the next service; If the service is on, it picks up a file from SharePoint named Reference_XXX.xlsb, where XXX is the service (Reference_ABC.xlsb, Reference_DEF.xlsb, etc) and runs a series of processes to extract the required info from these Reference files. The idea being that these separate Reference Files can be maintained separately, and only the required info for active services would be pulled into the Working file. The opening of the Reference file from SharePoint and the extraction of the required info takes ~3 seconds.
The issue is, after the first file (which works perfectly), the macro breaks when it gets to the Workbooks.Open part of the below
The odd thing is, that if I manually press F5 to continue the macro, it works absolutely fine, but will break at every time the loop gets to Workbooks.Open (sFile), and will again work fine if manually moved on with F5. The other odd thing is that if I delete the
part of the sPath address, then the macro no longer stalls, but the opening and extraction of info from the 2nd Reference file onwards now takes upwards of 90 seconds, which is unacceptably long (as there are potentially 20+ services to be added). What is going wrong here? How can I avoid the macro stalling, whilst at the same time keeping a tolerable running time? Thanks!
| Service | On/Off |
|---|---|
| ABC | 1 |
| DEF | 0 |
| GHI | 0 |
| JKL | 1 |
| MNO | 1 |
I have a macro that runs through this list - If the service is turned off, the macro moves on to the next service; If the service is on, it picks up a file from SharePoint named Reference_XXX.xlsb, where XXX is the service (Reference_ABC.xlsb, Reference_DEF.xlsb, etc) and runs a series of processes to extract the required info from these Reference files. The idea being that these separate Reference Files can be maintained separately, and only the required info for active services would be pulled into the Working file. The opening of the Reference file from SharePoint and the extraction of the required info takes ~3 seconds.
The issue is, after the first file (which works perfectly), the macro breaks when it gets to the Workbooks.Open part of the below
VBA Code:
sPath = "https://examplecompany.sharepoint.com/:x:/r/sites/GPF/po/WORKING/"
sFile = sPath & "Reference_" & Service & ".xlsb"
Workbooks.Open (sFile)The odd thing is, that if I manually press F5 to continue the macro, it works absolutely fine, but will break at every time the loop gets to Workbooks.Open (sFile), and will again work fine if manually moved on with F5. The other odd thing is that if I delete the
VBA Code:
:x:/r/
