I have macro that loops through the files in a particular folder. I’ve used this method before without any issues. However, in the current macro, it tries to open the first file (Addresses) again and I’m getting an error message that says “file is already open and being used by another user” (or something to that effect)

There are 2 files in this folder (Addresses / Users). The purpose of this little snippet is to check and make sure each file has data (to prevent issues downstream).

The Address file is good and can be opened and has data. The next file is Users and it is corrupted and cannot be opened, so the On Error GoTo BadFile kicks in. The macro does what I want it to for the bad file but then goes back to the main loop and tries to open the Address file again when the error message is displayed.

There can be 6 different files and I’m trying to determine the status of each file.

X = 1
    
For Each XFILE In xFolder.Files
        
     MyWB = ""
        
     On Error GoTo badfile
        
     objExcelApp.Workbooks.Open (XFILE)
    
     MyWB = ActiveWorkbook.Name
        
     If Len(Trim(Cells(2, 1))) = 0 Then
        
          ReDim Preserve MyFile(1 To X)
          MyFile(X) = ActiveWorkbook.Name
            
          If InStr(1, MyFile(X), "Address") > 0 Then Got1 = True
          If InStr(1, MyFile(X), "Commodities") > 0 Then Got2 = True
          If InStr(1, MyFile(X), "GL") > 0 Then Got3 = True
          If InStr(1, MyFile(X), "Suppliers") > 0 Then Got4 = True
          If InStr(1, MyFile(X), "Users") > 0 Then Got5 = True
          If InStr(1, MyFile(X), "Translate") > 0 Then Got6 = True
            
          objExcelApp.Workbooks(MyWB).Close False
       
BadFile:
            
          ReDim Preserve MyFile(1 To X)
            
          MyWB = XFILE
          MyWB = Replace(MyWB, "\VS600", "")
          MyWB = Replace(MyWB, "Testing", "")
          MyWB = Replace(MyWB, "Data Files", "")

          MyFile(X) = MyWB
            
          If InStr(1, MyFile(X), "Address") > 0 Then BadFileGot1 = True
          If InStr(1, MyFile(X), "Commodities") > 0 Then BadFileGot2 = True
          If InStr(1, MyFile(X), "GL") > 0 Then BadFileGot3 = True
          If InStr(1, MyFile(X), "Suppliers") > 0 Then BadFileGot4 = True
          If InStr(1, MyFile(X), "Users") > 0 Then BadFileGot5 = True
          If InStr(1, MyFile(X), "Translate") > 0 Then BadFileGot6 = True
           
          X = X + 1
         
     Else
         
          objExcelApp.Workbooks(MyWB).Close False
          
     End If
        
Next XFILE

As I said, it works fine for the Address file and the user file. I would expect it to exit the loop and continue with the rest of the macro but it goes back and looks at the Address file again and I’m not sure why or how to prevent it.

Any help would be greatly appreciated. Thanks for taking the time to look in my issue….