determine if ANY workbook is open before closing down

DANDERSON · Apr 19th 2005

Basically, I have written a VBA app that calls upon Excel, and opens Excel, then opens a workbook, and then makes a few modifications, and then saves and closes that workbook.

What I cant seem to figure out is how to determine if the user has any other workbooks open before the VBA app attempts to shut down Excel.

I have found example for a specific workbook, but I will not know what workbook a user night have open. I just want a check tool.

Thanks for the guidance,
Dan

Neiluk66 · Apr 19th 2005

Re: determine if ANY workbook is open before closing down

Hi,

I'm no expert but i can start you off.

If you count the number of open workbooks first then build an If function around the numer returned you can say: if there are more than 1 workbooks open then, do something, otherwise close and save changes.

Thefollowing works but i dont fully understand why itis telling me i have two open when i only have 1 open so i set the value to 2 to show that the logic works.

Might be my personal macro wk who knows

Code

Sub WORKBOOKS()


nowbs = Application.WORKBOOKS.Count


If nowbs < 2 Then


Application.WORKBOOKS.Close


Else


End If


MsgBox nowbs


End Sub

Display More

DANDERSON · Apr 19th 2005

Re: determine if ANY workbook is open before closing down

Thanks! That was the lead I needed. I was able to modify it for my needs. I greatly appreciate it, have a great day!
Dan

determine if ANY workbook is open before closing down

Participate now!

Share