Excel open workbook check

[Kenny1943]

Hello,

I have several excel workbooks i am trying to save with sequencial file names i.e. file1, file2, file3...

All the workbooks are open and I using the Saveas method to save them, then closing the workbook before moving on to the next.

How can I write my macro to stop once it has closed all worbooks.

 

[Chattin]

Have a search in the VBE help on 'Do...Loop' or 'For...Next'

HTH

 

[TonyJollans]

Hi Kenny1943,

This is not quite as easy as you might think.

To simply loop through all the open workbooks, use something like:

Dim WB As Workbook
For Each WB in Application.Workbooks

    ' Do your stuff here

Next

But, once you close the workbook containing your code it will stop running so you must ensure it is the last one closed. You could do this ..

Dim WB As Workbook
For Each WB in Application.Workbooks
    If WB.Name <> ThisWorkbook.Name then

        ' Do your stuff here

    End If
Next
ThisWorkbook.Close

Or, somewhat better I would suggest, you could put your code in your Personal.xls Workbook. This then raises the problem of it being an open workbook which you do not want to SaveAs and Close. There may also be other open workbooks (e.g. Addins) which you do not actually want to process but all of these are in hidden windows so one solution would be ..

Dim WB As Workbook
For Each WB in Application.Workbooks
    If WB.Windows(1).Visible then

        ' Do your stuff here

    End If
Next

Enjoy,
Tony

 

[Kenny1943]

Tony,


Thanks for the quick response. This seems to work if there are no hidden windows on a spreadsheet. I tried your last suggestion because I would like to keep this in my Personal.xls file. It seems because some of the spreadsheets have hidden windows, it's not saving all the spreadsheet I have open.

Here's the code I wrote:

Sub TestMacro()
Dim WB As Workbook
Dim i As Integer

i = 0
For Each WB In Application.Workbooks
If WB.Windows(1).Visible Then
i = i + 1
ActiveWorkbook.SaveAs "C:\File" & i
ActiveWorkbook.Close False
End If
Next

End Sub

 

[TonyJollans]

Hi Kenny1943,

If that's the case, I would try checking for those with any visible windows which is a bit more complex. This should do it:

Sub TestMacro()
    Dim WB As Workbook
    Dim i As Integer

Dim Win As Window
    Dim TotallyHidden as Boolean

    i = 0
    For Each WB In Application.Workbooks

TotallyHidden = True
        For Each Win In WB.Windows
            If Win.Visible Then TotallyHidden = False
        Next
        If Not TotallyHidden Then

' This line replaced: If WB.Windows(1).Visible Then

            i = i + 1
            ActiveWorkbook.SaveAs "C:\File" & i
            ActiveWorkbook.Close False
        End If
    Next
    
End Sub

Enjoy,
Tony