MS Access Based Loop Through All excel files in a a folder.. "Skip if file already using"

[Ocicek]

Hello All

I have a code loop through all excel file in a given folder.

But I have problem if some files opened from that folder the loop is getting crash. (With this code excel files opening as hidden on another instances)

My example code is:

So Where should i add a condition in the code to solve this problem?

Thanks from now.

Have a nice day.

Sub LoopAllExcelFilesInFolder()


Dim wb As Workbook
Dim myPath As String
Dim myFile As String
Dim myExtension As String
Dim FldrPicker As FileDialog


'Retrieve Target Folder Path From User
  Set FldrPicker = Application.FileDialog(msoFileDialogFolderPicker)

    With FldrPicker
      .Title = "Select A Target Folder"
      .AllowMultiSelect = False
        If .Show <> -1 Then GoTo NextCode
        myPath = .SelectedItems(1) & "\"
    End With

'In Case of Cancel
NextCode:
  myPath = myPath
  If myPath = "" Then GoTo ResetSettings

'Target File Extension (must include wildcard "*")
  myExtension = "*.xls*"

'Target Path with Ending Extention
  myFile = Dir(myPath & myExtension)

'Loop through each Excel file in folder
  Do While myFile <> ""
    'Set variable equal to opened workbook
      Set wb = Workbooks.Open(Filename:=myPath & myFile)
    
    'Ensure Workbook has opened before moving on to next line of code
      DoEvents
    
    'Change First Worksheet's Background Fill Blue
      wb.Worksheets(1).Range("A1:Z1").Interior.Color = RGB(51, 98, 174)
    
    'Save and Close Workbook
      wb.Close SaveChanges:=True
      
    'Ensure Workbook has closed before moving on to next line of code
      DoEvents

    'Get next file name
      myFile = Dir
  Loop

'Message Box when tasks are completed
  MsgBox "Task Complete!"

ResetSettings:
  'Reset Macro Optimization Settings
    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True

End Sub

 

[The_Doc_Man]

Excel does not like to share files, so your "crash" is probably an error regarding file locking or something like that. You need to research articles on error trapping in this forum.

In overview, in that subroutine you would create some sort of flag that is a success/failure flag (could just be a Boolean TRUE/FALSE for this purpose.) Then set (enable) an On Error trap near the top of your sequence. Set your success/failure flag to "success" and leave it like that. Now when your loop is running, every time you open a file, the next step should be to test the success flag.

Here's the key part. If your routine trips on a file, you have error trap code that is not in-line with the rest of that code. It has to set the flag that says "failure" (and if you want, here is where a MSGBOX could be used to notify you.) Then do a RESUME NEXT to end the trap routine.

Now in the main part of this subroutine, you test that flag. IF it ever comes back failure, you write some code to clean up that error, reset the flag to success, and continue your loop. The idea here is that if you got an error, the file didn't open so the rest of your code cannot work FOR THAT FILE - but you could do whatever is required to reset variables or whatever and try again for the next file.

I'm not going to try to write that for you because I'm working on something else and I'm just taking a break here. But this should give you the idea of what you need to do to handle errors. If you find some other examples of error handling in this forum, you should be able to look at the overview I just gave you and do something positive with it.

 

[Ocicek]

Thank you @The_Doc_Man .. I'm working on that.