2

I am trying to do the following:

  1. specify a folder (containinf *.xlsm files)
  2. Iterate through all the files
  3. open each file, run a macro, close and save the file
  4. Move onto next file until all have been done.

The code below works, BUT the loop never ends... its as if every time I save the file that's just been worked on, it appears as a new item in the list of files to go through.

What am I doing wrong?

Thanks.

Sub runMe()

    Dim objFSO As Object
        Dim objFolder As Object
        Dim objFile As Object
        Dim MyPath As String
        Dim wb As Workbook
        Dim myDir As String

    With Application
        .ScreenUpdating = False
        .DisplayAlerts = False
        .EnableEvents = False
    End With

    myDir = "\templates"
    Debug.Print ActiveWorkbook.Path
    MyPath = ActiveWorkbook.Path & myDir

    Set objFSO = CreateObject("Scripting.FileSystemObject")

    'Get the folder object associated with the directory
    Set objFolder = objFSO.GetFolder(MyPath)

    'Loop through the Files 

    For Each objFile In objFolder.Files
        If InStr(objFile.Name, "~") = 0 And InStr(objFile.Name, ".xlsm") <> 0 Then
            Set wb = Workbooks.Open(objFile, 3)
            Application.Run "'" & wb.Name & "'!doMacro"
            wb.Close SaveChanges:=True
           ' Gets stuck in this loop
           ' WHY DOES IT KEEP LOOPING?
        End If
    Next

    With Application
        .ScreenUpdating = True
        .DisplayAlerts = True
        .EnableEvents = True
    End With

End Sub
Improve this question
10
  • Your code seems fine. What you are exactly doing in the doMacro part? It is affecting the files in some way? Commented Aug 6, 2013 at 12:18
  • 2
    Problems like this are most easily tracked down by setting a breakpoint and stepping through the code line by line, inspecting variables as you go. It's not complicated code so this should be a matter of minutes to debug. Commented Aug 6, 2013 at 12:31
  • This will probably not resolve your problem, but Workbooks.Open takes a String as a first argument (or something that can be cast to a String). Commented Aug 6, 2013 at 12:48
  • 1
    @Ioannis The default parameter of a File COM object is its path, you can cast it as string without problems. It's just not as explicit and obvious, but it works. Commented Aug 6, 2013 at 13:09
  • 1
    @Ioannis i.imgur.com/y6FIppN.png, note the small blue dot that signifies the default member of a COM class. Commented Aug 6, 2013 at 13:46

2 Answers 2

Reset to default
2 
Sub runMe()
    Dim FSO As New Scripting.FileSystemObject
    Dim File As Scripting.File
    Dim path As String

    With Application
        .ScreenUpdating = False
        .DisplayAlerts = False
        .EnableEvents = False
    End With

    path = ActiveWorkbook.Path & "\templates"

    For Each File In FSO.GetFolder(path).Files
        If InStr(File.Name, "~") = 0 _
           And LCase(FSO.GetExtensionName(File.Name)) = "xlsm" _
        Then
            With Workbooks.Open(File.Path, 3)
                Application.Run "'" & .Name & "'!doMacro"
                .Close SaveChanges:=True
            En With
        End If
    Next

    With Application
        .ScreenUpdating = True
        .DisplayAlerts = True
        .EnableEvents = True
    End With
End Sub

Your For Each loop can by definition not run forever, the error must be some place else, presumably in whatever doMacro does.

Subjective notes:

  • Include a reference to scrrun.dll in your VBA project. This is useful for early binding (New Scripting.FileSystemObject) and it gives you code completion for those objects.
  • GetExtensionName() is useful to get a file extension.
  • Drop the Hungarian notation, you are not using it consistently anyway.
  • You don't need a helper variable for For Each.
  • You can use a With block to substitute the other helper variable (wb).
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Thanks for the comments (bit of a novice as you can tell, this code is cobbled together from different snippets I find). doMacro just copies and pastes some values within the opened File. So it is changing it and then I call the .Close with saves... If I add debug.print File then I see each file (2 diff in this case) just being opened and closed, repeating.
0

By looking at your comment, I think that the problem is that when you save the file it is somehow added back to the FSO.GetFolder(path).Files collection Iteration. A way to work around this is to build an array with the file names and then execute your loop. The relevant code is below:

Dim aux As String, Paths as Variant, Path as Variant

For Each File In FSO.GetFolder(path).Files
    If Not File.Name Like "~*" And File.Name Like "*.xlsm" Then
       aux = aux & File.Path & ";"
    End If
Next File

If Len(aux) = 0 Then Exit Sub 'No file matches the criteria
Paths = Split(Left(aux, Len(aux) -1), ";") 'Builds an array with the filenames

For Each Path In Paths
    With Workbooks.Open(Path, 3)
        Application.Run "'" & .Name & "'!doMacro"
        .Close SaveChanges:=True
    End With
Next Path

I built a string separated by ";" and then used Split to build an array to avoid using indexes, ReDim Preserve statements or to test if the filename is empty

Improve this answer

1 Comment

Thanks @kbsou Funnily enough, in a roundabout way, I came to the same solution. I iterated through the files into a temp array (in my lame case, I put them in a named array) then looped through them. Think this is solving it. Cheers

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.