VBA Excel can't move focus on the control when looping through files in the directory

Question

I would like to loop through all my excel files in the directory and resave them.

The following code:

 Sub DirectoryFileLoop()
 Dim fileDirectory As String
 Dim FileName As String
 Dim fileToOpen As Workbook
 Dim strPath As String

 strPath = Application.FileDialog(msoFileDialogOpen).Show


 Application.ScreenUpdating = False

 fileDirectory = Dir$(strPath & "*.xls", vbNormal)

 FileName = Dir(fileDirectory)

 Do While Len(FileName) > 0

 Set fileToOpen = Workbooks.Open(fileDirectory & FileName)

 With fileToOpen
 .Save
 .Close
 End With


 Debug.Print FileName

 FileName = Dir
 Loop


 Application.ScreenUpdating = True

 MsgBox ("All files have been validated")
 End Sub

works just for a few first examples. Afterward I am getting an error:

Run-time error '1004' Can't move focus to the control because it is invisible, not enabled or of a type that does not accept the focus.

Tried to find some solution, and everything I found is just here:

https://social.msdn.microsoft.com/Forums/office/en-US/3263b079-7e4f-452c-8dcc-92c682b8370b/excel-form-cant-move-focus-to-the-control-because-it-is-invisible-not-enabled-or-of-a-type-that?forum=exceldev

Error: Can't move focus because it is invisible

but these hints didn't help me.

Where might be the problem here?

Answer 1

Your code is wrong. It does not return a folder, as you need, Dir is also wrongly used. Please, try the next adapted code:

Sub DirectoryFileLoop()
 Dim fileDirectory As String, FileName As String
 Dim fileToOpen As Workbook

 fileDirectory = getFolderPath & "\"

 Application.ScreenUpdating = False
 
 If fileDirectory = "\" Then MsgBox "No any folder has been selected...": Exit Sub

 FileName = Dir$(fileDirectory & "*.xls*", vbNormal)
 
 If FileName = "" Then MsgBox "No Excel workbook could be found in " & fileDirectory: Exit Sub
 
 Do While FileName <> ""
     Set fileToOpen = Workbooks.Open(fileDirectory & FileName)
     'do whatever you need here (to validate)...
     fileToOpen.Close True
     Debug.Print FileName

     FileName = Dir
 Loop

 Application.ScreenUpdating = True

 MsgBox ("All files have been validated")
 End Sub

 Private Function getFolderPath(Optional strPath As String) As String
 Dim Fldr As FileDialog, sItem As String
 Set Fldr = Application.FileDialog(msoFileDialogFolderPicker)
 With Fldr
    .Title = "Select a Folder"
    .AllowMultiSelect = False
    If strPath <> "" Then .InitialFileName = strPath 'the folder where the dialog to open
    If .Show <> -1 Then GoTo NextCode
    sItem = .SelectedItems(1)
 End With
NextCode:
 getFolderPath = sItem
 Set Fldr = Nothing
End Function

getFolderPath offers the possibility to set an initial folder where form to start browsing. I used it without such a parameter (it is Optional), but if necessary/helpful you can use it.