Looping through excel files in a folder and performing a procedure on each of them VBA

Question

My Aim:

This procedure is meant to loop through excel files in a specified folder and preform a sub (cleanDataAndTransfer), which is meant to clean the data in the files being looped through and then paste it in to a new sheet in the master file.

My problem:

Im getting the Run-time error '91': Object variable or With block variable not set on the .Title = "Select A Target Folder" line.

I've tried different solutions to rectify the issue but nothing has yet worked.

My code:

Sub loopAllExcelFilesInFolder()

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

  Application.ScreenUpdating = False
  Application.EnableEvents = False
  Application.Calculation = xlCalculationManual

  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

NextCode:
  myPath = myPath
  If myPath = "" Then GoTo ResetSettings

  myExtension = "*.xls*"

  myFile = Dir(myPath & myExtension)

  Do While myFile <> ""
      Set wb = Workbooks.Open(FileName:=myPath & myFile)
    
      DoEvents
    
      Call cleanDataAndTransfer
    
      wb.Close SaveChanges:=True
      
      DoEvents

      myFile = Dir
  Loop


ResetSettings:
    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True

End Sub

I would really appreciate any suggestions on how to solve this bug and any other improvements! Thanks in advance :)

There is no line with `.Title = "Select A Target Folder"` in the code you posted. — Storax, Sep 23 '21 at 10:28
RTE=Run time error. So, what I am saying is that the code runs till the line where `.Show ` is called. — Storax, Sep 23 '21 at 10:33
It runs until the `.Title = "Select A Target Folder"` line and then throws up the error I messaged above — Esrom Tecle, Sep 23 '21 at 10:36
As I wrote, code works for me fine, no RTE. I get the dialog box with thte title _Select A Target Folder_ and I can even select a path, no problem. Do you use a Mac? This might help then https://stackoverflow.com/a/37413945/6600940 — Storax, Sep 23 '21 at 10:38
I added some more information to my previous comment. I am afraid I cannot help further as I do not have a Mac and it might be that `FileDialog` is not supported on a Mac. — Storax, Sep 23 '21 at 10:41
[This might help](https://stackoverflow.com/a/37413945/445425) — chris neilsen, Sep 23 '21 at 10:48
Thanks @Storax! Is there anyway I could work around this by getting it to open a set folder instead of opening up a dialog box? — Esrom Tecle, Sep 23 '21 at 11:09

CDP1802 · Answer 1 · 2021-09-23T15:23:33.890

0

I don't have a Mac to test this but you could try an InputBox.

update - no filter on Dir

Sub loopAllExcelFilesInFolder()

   Sub loopAllExcelFilesInFolder2()

    Const EXT = "csv"

    Dim wb As Workbook, myPath As String, myFile As String
    Dim count As Integer, isWindows As Boolean
    
    myPath = ThisWorkbook.Path & Application.PathSeparator
    myPath = VBA.InputBox("Enter folder", "Folder", myPath)
    If myPath = "" Then Exit Sub

    If Right(myPath, 1) <> Application.PathSeparator Then
        myPath = myPath & Application.PathSeparator
    End If
   
    myFile = Dir(myPath)
    Do While myFile <> ""
        If Right(myFile, Len(EXT)) = EXT Then
 
            Set wb = Workbooks.Open(Filename:=myPath & myFile)
            Call cleanDataAndTransfer
            wb.Close SaveChanges:=True
            count = count + 1

        End If
        myFile = Dir
    Loop
    MsgBox count & " files cleaned", vbInformation
End Sub

edited Sep 23 '21 at 15:23

answered Sep 23 '21 at 12:30

CDP1802

13,871
2
7
17

I tried this but a this error came up, Run-time error '5': Invalid procedure call or argument – Esrom Tecle Sep 23 '21 at 14:57
@Esrom If it is on the line `myPath = VBA.InputBox("Enter folder", "Folder", myPath)` then replace the line with `myPath = "/whatever/your/path/to/files/is/"` – CDP1802 Sep 23 '21 at 15:03
Sorry, it was on this myFile = Dir(myPath, MacID("TEXT")) ' mac – Esrom Tecle Sep 23 '21 at 15:12
@Esrom - see update without the MacID() function. – CDP1802 Sep 23 '21 at 15:24

Looping through excel files in a folder and performing a procedure on each of them VBA

1 Answers1