Trying to loop through Workbooks.Name

Question

I am trying to loop through workbook-names which opens automatically, to close them, without saving them. This is how it works without a loop:

On Error Resume Next
ActiveWorkbook.Sheets.Copy
ActiveWorkbook.SaveAs Filename:=Path & Range("C2").Value & "_Plan.xlsx", FileFormat:=51


If Err.Number = 1004 Then
    MsgBox "Speichervorgang wurde beendet."
    Workbooks("Mappe1").Close SaveChanges:=False
    Workbooks("Mappe2").Close SaveChanges:=False
    Workbooks("Mappe3").Close SaveChanges:=False
    Workbooks("Mappe4").Close SaveChanges:=False
    Workbooks("Mappe5").Close SaveChanges:=False
    Workbooks("Mappe6").Close SaveChanges:=False
    Workbooks("Mappe7").Close SaveChanges:=False
    ...
Else
...
End If

What I tried:

Dim counter As Integer
Dim Mappenname As String

On Error Resume Next
ActiveWorkbook.Sheets.Copy
ActiveWorkbook.SaveAs Filename:=Path & Range("C2").Value & "_Plan.xlsx", FileFormat:=51


If Err.Number = 1004 Then
    MsgBox "Speichervorgang wurde beendet."

    counter = 0
    Do While counter = 100
        Mappenname = "Mappe" & counter
        If Workbooks(Mappenname).Open = True Then
            ActiveWorkbook.Close SaveChanges:=False
            Exit Do
        End If
        counter = counter + 1
    Loop
Else
...
End If

With my Loop, nothing happens...

Get rid of the On Error Resume Next for starters. And what does "nothing happens" mean? No msgbox? And your Do Loop won't do anything as counter starts at 0. Do you mean Do Until ? — QHarr, Feb 08 '19 at 11:50
@QHarr So if I get rid of the On Error Resume Next and cancel the SaveAs I get Err.Number = 1004. If I cansel the SaveAs I also want to end Activeworkbook.Sheets.copy or delete the Workbook with the loop. — Andreas, Feb 08 '19 at 12:09
You want an [on error goto errhandler](https://stackoverflow.com/a/23688126/6241235) (instead) and add a label for your error handling section just above your error handling code which should go at the very bottom. Typically you would want an End Sub before this error handling so it is only run if error thrown otherwise code completes and exits. — QHarr, Feb 08 '19 at 12:11
@QHarr Thank you very much! :-D The on error goto errhandler worked perfect! :-) — Andreas, Feb 08 '19 at 12:30

score 0 · Answer 1 · answered Feb 08 '19 at 11:52

Try something small as this one and try to make it work:

Sub TestMe()
    MsgBox Workbooks(1).Open = True
End Sub

The code above tries to mimic this condition - If Workbooks(Mappenname).Open = True Then. As you will see, it is quite a challenging task, because Workbooks do not have the .Open boolean property. Unfortunately, the code and the error handler cannot tell this, because of the using of On Error Resume Next.

The fortunate part is that plenty of people have asked themself the question - how to see whether a workbook is opened - Detect whether Excel workbook is already open

score 0 · Answer 2 · answered Feb 08 '19 at 13:04

This is the code which is working for me:

On Error GoTo Warnung
    ActiveWorkbook.Sheets.Copy
    ActiveWorkbook.SaveAs Filename:=Speicherpfad & Range(ZelleSpeichername).Value & "_Netzentwicklungsplan.xlsx", FileFormat:=51
...
Warnung:
counter = 0
    Do Until counter = 10000
        Mappenname = "Mappe" & counter
        If ActiveWorkbook.Name = Mappenname Then
            ActiveWorkbook.Close SaveChanges:=False
            MsgBox "Speichervorgang wurde beendet."
            Exit Do
        End If
        counter = counter + 1
    Loop
End Sub

Trying to loop through Workbooks.Name

2 Answers2