VBScript “open all workbooks” Error Handling

Question

My VBScript opens all "xlsm" files in a folder, runs VBA code "Slim" and closes the workbook (one-by-one). It works fine, but I struggle with error handling. If the script tries to open a workbook currently open by someone else, I end up with error popup Someone else is working in /path + wb-name/ right now. Please try again later. and the loop pauses up until OK is clicked on the warning message. Looks like it will open the workbook no problem, but ends up with an error at the end since the VBA code saves a new file and tries to delete the old one. Hence, I'll end up with a new file and non-deleted old one on top of the error message.

While clearly not the ideal solution, quitting the whole loop in this scenario would also be better than waiting for the OK to be clicked, since VBScript is automated to launch.

I'd need to build an error handling for this scenario, so that already opened file would just be skipped and the loop would continue uninterrupted. Unfortunately, DisplayAlerts = False good ol' On Error Resume Next won't do it here.

If possible in a reasonable way, I'd like to solve this through VBScript and not adjust the VBA code.

Set fso = CreateObject("Scripting.FileSystemObject")
Set xl  = CreateObject("Excel.Application")

On Error Resume Next
xl.DisplayAlerts = False

For Each f In fso.GetFolder("G:\Archive").Files
  If LCase(fso.GetExtensionName(f.Name)) = "xlsm" Then
    
    Set wb = xl.Workbooks.Open(f.Path)
    
    xl.Run "Slim"    
    wb.Close

  End If

Next

xl.Quit

Set fso = Nothing
Set xl = Nothing

Tried different scenarios, haven't cracked it so far. Latest option was this, but didn't help (is there a different way of checking if the workbook is currently read-only)?

For Each f In fso.GetFolder("G:\Archive").Files
  If LCase(fso.GetExtensionName(f.Name)) = "xlsm" Then
    
    Set wb = xl.Workbooks.Open(f.Path) 
    
    If NOT wb.ReadOnly Then

    xl.Run "Slim"
    wb.Close

    Else

    wb.Close

    End If
        
  End If

Next

Mike67 Mike67 · Accepted Answer · 2020-08-31T21:26:51

If an excel file is open, opening it again is blocked. Renaming the file is also blocked, but a failed rename does not show a message box with resume next

Try this code. It tries to rename the file first. If the rename succeeds, it renames back then runs the macro. If rename fails, it skips the file.

Set fso = CreateObject("Scripting.FileSystemObject")
Set xl  = CreateObject("Excel.Application")

On Error Resume Next
xl.DisplayAlerts = False
For Each f In fso.GetFolder("D:\MikeStuff\StackOverflow\ExcelCheckOpen").Files
  If LCase(fso.GetExtensionName(f.Name)) = "xlsm" Then
    xpath = f.Path  ' file path lost after rename
    fso.MoveFile xpath, xpath & ".txt"  ' will fail if file locked by excel
    if fso.FileExists(xpath & ".txt") Then ' rename worked, file not locked
        fso.MoveFile xpath & ".txt", xpath  ' rename back
        Set wb = xl.Workbooks.Open(xpath)
        xl.Run "Slim"    
        wb.Close
    End If
  End If
Next

xl.Quit

Set fso = Nothing
Set xl = Nothing

VBScript “open all workbooks” Error Handling

1 Answers