CODEWITHSUNDEEP
excelvba
user6651298
user6651298

Reputation:

Is there a way to check for non-visible, open Excel Workbooks?

I have one open workbook with a button "SaveClose". I want the event on that button to close the entire Excel application if it is the last workbook opened. The thing is, there are possibility that there could be unknown personal.xlsb open or any other macro workbook open that is not visible.

I want to know if there is any other workable Excel workbook opened. Is there a check for a not visible workbook? If it is the last workbook, close Excel application, if not close active workbook, here's what I got:

Sub CloseForceSave()

'Save the workbook.
ThisWorkbook.Save

'If this is the only workbook opened in the current session, then...
If Workbooks.Count = 1 Then "or Workbooks.Count = 2" to account for personal.xlsb
'...quit Excel
Application.Quit
'Else...
Else
'...close the active workbook
ThisWorkbook.Close
End If

End Sub

Upvotes: 5

Views: 2551

Answers (2)

Dick Kusleika
Dick Kusleika

Reputation: 33175

When I do this I just ignore any hidden workbooks. If there is only one workbook with a visible window, I quit the application. I don't know of any better way of counting than a loop, but I've never had enough workbooks open for this to be a performance problem.

Function VisibleWorkbookCount() As Long

    Dim wb As Workbook
    Dim wd As Window
    Dim lReturn As Long

    For Each wb In Application.Workbooks
        For Each wd In wb.Windows
            If wd.Visible Then
                lReturn = lReturn + 1
                Exit For
            End If
        Next wd
    Next wb

    VisibleWorkbookCount = lReturn

End Function

Upvotes: 3

hepatus
hepatus

Reputation: 1

if a workbook is not visible, it's usually a AddIn. Try to check "Workbook.IsAddin Property"

see https://msdn.microsoft.com/de-de/library/office/ff838249.aspx

Upvotes: 0

Related Questions