0

I have a subroutine that loops through opening, changing, saving and closing multiple files from a shared OneDrive. I'm running into errors when the subroutine closes and saves the files in the line below.

ActiveWorkbook.Close SaveChanges:=True

The subroutine opens the file in the lines below, which automatically opens files in the desktop app rather than the web app. When the file is saved, it tries to sync with the file on the OneDrive account, causing two issues. First, the file won't close until changes are synced, so the subroutine gets stuck on the close line until the sync is finished. Is there a way to force the code to move on before the workbook is finished saving and closing? I'm not sure if On Error Resume Next will do the trick here since I don't believe the issue will trigger an error.

Application.ScreenUpdating = False

Dim j As Integer
Dim k As Integer
Dim n As Integer
n = Range("AgencyTable").Count / 2 - 1
Dim wbkname() As String
ReDim wbkname(n)

For k = 1 To n Step 1

    wbkname(k) = ThisWorkbook.Names("AgencyTable").RefersToRange(k + 1, 1)
    
Next k

For j = 1 To n Step 1
    
    Dim path As String

    path = "<path>" & wbkname(j) & ".xlsm"

    Set xl = CreateObject("Excel.Sheet")
    Set xlsheet = xl.Application.Workbooks.Open(FileName:=path)

The second issue I'm running into is that the file will occasionally fail to save. I am then prompted to save a copy of the file, and my only options at that point are to save a copy or discard the changes. I'm hoping Applpication.DisplayAlerts = False will force the subroutine to discard the changes. Is this correct?

Users encouter this issue often, but it only seems to occur in a specific scenario I have not been able to identify, so I cannot re-create the issue at this time, making it tough to Debug; therefore, any advice is appreciated.

jhericurl
  • 1
  • I don't believe you can save workbooks asynchronously and I haven't head of multithreading in VBA. But if Saving is the problem, what about not saving or closing the files until the end and then doing all of that at once? – Toddleson Dec 23 '21 at 14:24
  • I hadn't considered waiting until the end to save and close. Even if the issue still exists, that would allow all of the changes to my main workbook to run uninterrupted. My only concern is the number of open workbooks towards the end. I'll try it out. Thanks! – jhericurl Dec 23 '21 at 14:37
  • You assign the opened workbook to variable `xlsheet` (strange name because it's a workbook, not a sheet, you should consider renaming it), so use this variable when closing the workbook: `xlsheet.Close SaveChanges:=True` – FunThomas Dec 23 '21 at 14:40
  • You're right, it is a strange name. Later in the subroutine, I activate the workbook and use ActiveWorkbook.Close. Is xlsheet.Close any different? – jhericurl Dec 23 '21 at 14:54
  • It's different if the Workbook is (no longer) the Active Workbook. You should avoid to activate workbooks or -sheets in your code, use variables instead, it is almost never necessary. Have a look to https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba. But beside this, no, there is no difference. And I am afraid that Toddleson is right, Excel is not multithreaded and continues with one statement only when the previous is done. – FunThomas Dec 23 '21 at 15:00
  • For your second issue: `Application.DisplayAlerts=False` will not save you, it prevents Excel to show a warning but the underlying problem remains. – FunThomas Dec 23 '21 at 15:01
  • I really appreciate the advice and link about using variables instead of activating workbooks. Thanks! – jhericurl Dec 23 '21 at 15:50
  • I was afraid of that. Thanks for the confirmation! – jhericurl Dec 23 '21 at 15:51

0 Answers0