CODEWITHSUNDEEP
excelvba
Potocpe1
Potocpe1

Reputation: 35

VBA Excel macro to quit all workbooks ends in infinite loop

I would like to write a macro to loop throuht all excel workbooks (in multiple instances) and print the active sheet in all workbooks and close all excel instances.

The code is following:

Dim xl As Excel.Application
Set xl = GetObject(, "Excel.Application")
If xl Is Nothing Then
    msgbox "Unable to set xl"
    Exit Sub
End If

Dim wb As Excel.Workbook
Dim loopCount As Integer
loopCount = 0


Do Until xl Is Nothing
    loopCount = loopCount + 1

    For Each wb In xl.Workbooks
        wb.ActiveSheet.PrintOut
        wb.Saved = True
    Next

    xl.Quit

    If loopCount > 30 Then
        msgbox "Infinite loop"
        Exit Sub
    End If

    Set xl = GetObject(, "Excel.Application")

Loop

msgbox "Ended successfully"

The problem is, that only one application is quited and then macro end in an infinite loop. Why? I tried several ways hot to do it, always ending with infinite loop.

Thank you very much for your ideas!

Upvotes: 1

Views: 171

Answers (1)

Denyo
Denyo

Reputation: 117

you close excel completly, so the macro can not end its work.

try this:

Sub test()

    Dim xl As Excel.Application
    Set xl = GetObject(, "Excel.Application")
    If xl Is Nothing Then
        MsgBox "Unable to set xl"
        Exit Sub
    End If

    Dim wb As Excel.Workbook
    Dim loopCount As Integer
    loopCount = 0


    Do Until xl Is Nothing
        loopCount = loopCount + 1

        For Each wb In xl.Workbooks
            wb.ActiveSheet.PrintOut
            wb.Saved = True
            ' closes all workbooks except the one which contains the macro
            If (wb.Name <> ThisWorkbook.Name) Then
                wb.Close
            ' if all workbooks except the one which contains the macro are closed, it junps to the end
            ElseIf (Workbooks.Count = 1) Then
                GoTo end
            End If
        Next



        If loopCount > 30 Then
            MsgBox "Infinite loop"
            Exit Sub
        End If

        Set xl = GetObject(, "Excel.Application")

    Loop



    ' due to if (false) this part is just reachable via GoTo
    If (False) Then
end:
    ' here you can also close the workbook which contains the macro
    MsgBox "Ended successfully"
    End If

End Sub

Upvotes: 1

Related Questions