0

Hello All,

I am in a roadblock right now and I really need some help on the code below. What I wanted to do is close all opened workbooks. This code does the work IF there is at least one or more opened workbooks.

My problem is IF there is NO opened workbook at all; It goes around unendlessly in the WHILE Loop. How do I not process the closing IF there is no any opened workbook at all? (NOTE: I found this code from somewhere else.)

Again, thank you in advance for helping a poor confused student.

cheers, Dor

'--------------------------------------------
Public Sub CloseAllWorkbooks()
Dim objOffice As Excel.Application
Dim WBook As Excel.Workbook
On Error Resume Next
While Err.Number = 0
objOffice = GetObject(, "Excel.Application")
objOffice.DisplayAlerts = False
For Each objWindow As Excel.Workbook In objOffice.Windows
objWindow.Activate()
WBook = objOffice.ActiveWorkbook
WBook.Saved = True
WBook.Close()
Next
objOffice.DisplayAlerts = True
objOffice.Quit()
objOffice = Nothing
End While
End Sub
'--------------------------------------------

2
Contributors
2
Replies
3
Views
7 Years
Discussion Span
Last Post by dor.marchan
0

try using the Count property of the collection to test if there are any items in the collection.

if objOffice.count > 0 then
' rest of your code from while to end while
end if

also for the future wrap you code in code /code to make it easier to read

Edited by ChrisPadgham: n/a

0

try using the Count property of the collection to test if there are any items in the collection.

if objOffice.count > 0 then
' rest of your code from while to end while
end if

also for the future wrap you code in code /code to make it easier to read

===============================

Thanks for the tip...when I placed the IF statement exactly before the WHILE loop, it shows a warning (I think ther object was not yet set); so, I tried placing it before the FOR loop and it appears it's doing what I wanted to do...thanks....

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.