close excel only if no other workbooks open

[sedgely]

Hi
i have a work book that auto opens does some 'stuff' then saves itself and closes. what i would like to do is also close Excel. This i can do no problem using

Application.Quit

however this can be a problem if the user has another workbook open. does anyone know a way of detecting if there are workbooks open and then using the above ONLY if there isn't?

Craig

 

[RogADog]

Craig

If you have more than one workbook open, you can scroll through them using:
ActiveWindow.ActivateNext

If there's only the one workbook open, the activeworkbook remains the same.

Hope it helps

Roger

 

[Bowers74]

This should do the trick!

If workbooks.count = 1 then
application.quit
End if

I hope this helps!

Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[TonyJollans]

Hi sedgely,

It would be nice if you could just check Workbooks.Count but, unfortunately, that includes personal.xls and any Add-Ins the user may have so there is no absolute value to check against.

But when you say the workbook auto-opens, what do you mean? I haven't tried doing this, but the code which opens it must either use an existing instance of Excel or start a new one and ought to know which it has done, so should know the state of play when it comes to closing time, shouldn't it?

Enjoy,
Tony

 

[sedgely]

Tony
maybe i didn't put that very well. i use Windows scheduled tasks to open Excel and the workbook.

but if i then use application.quit it will attempt to close Excel even if there were other workbooks open prior to the scheduled task running, it does prompt for a save for the other workbooks, but it is annoying to say the least.

Craig

 

[Bowers74]

Since the PERSONAL.xls is located in a hidden window (the same for all custom add-ins), the following code will work.

Sub QuitXLIfOnlyOneWBOpen()
Dim wb As Workbook
Dim win As Window
Dim i As Integer
i = 0
For Each win In Application.Windows
    If win.Visible = True Then
        i = i + 1
    End If
Next win
If i = 1 Then 
    Application.Quit
Else
    ThisWorkbook.Close
End If
End Sub

I hope this helps! (Tony)


Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[TonyJollans]

Hi Mike,

That looks good - it hurts my ear though

Enjoy,
Tony

 

[Bowers74]

Tony,

It was you pointing out to me that the PERSONAL.xls would be included in the Workbooks.Count that inspired me.

I'm glad you like it! ;-)



Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[Bowers74]

I was wondering if sedgely even got this?

Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[sedgely]

Mike
Yes i did get this, but unfortunately I'm that busy at the mo I've not had chance to try it. But thanks anyway to all. When i get time (hopefully this afternoon) i will try it and let you all know.
Thanks again

Craig