Macro update
 

I would like the following macro to shut down all open files including 1
hidden file and the shut down the Excel program. I have used this macro for
many years in Excel 5. I am now upgrading to Excel 2002 where it doesn't
work. Do you have any suggestions as to what the problem could be?



Dim Index As Integer, Number As Integer, FinalNumber As Integer

'Index
is a loop counter

Number = Application.Workbooks.Count 'The Number of open Workbooks

FinalNumber = Number - 1 'The 1 refers to the
hidden file

For Index = 1 To FinalNumber 'Place the Workbook names
in an array

ActiveWorkbook.Close saveChanges:=True

Next Index

Application.Quit



Thanks,



Roger

Macro update
 

In Excel 97 and later, when you close the workbook containing the macro, the
macro stops.

how about

Dim wkbk as Workbook
for each wkbk in Workbooks
if wkbk.Windows(1).Visible then
wkbk.Save
end if
Next
application.Quit

--
Regards,
Tom Ogilvy

"Roger" wrote in message
...
I would like the following macro to shut down all open files including 1
hidden file and the shut down the Excel program. I have used this macro
for
many years in Excel 5. I am now upgrading to Excel 2002 where it doesn't
work. Do you have any suggestions as to what the problem could be?



Dim Index As Integer, Number As Integer, FinalNumber As Integer

'Index
is a loop counter

Number = Application.Workbooks.Count 'The Number of open Workbooks

FinalNumber = Number - 1 'The 1 refers to the
hidden file

For Index = 1 To FinalNumber 'Place the Workbook
names
in an array

ActiveWorkbook.Close saveChanges:=True

Next Index

Application.Quit



Thanks,



Roger

Macro update
 

What is the 'hidden' file.

Give this a whirl.

Dim Index As Integer

For Index = 1 To Application.Workbooks.Count - 1
If Workbooks(i).Name < ThisWorkbook Then
ActiveWorkbook.Close saveChanges:=True
End If
Next Index
ThisWorkbook.Close saveChanges:=True
Application.Quit


--
HTH

Bob Phillips

"Roger" wrote in message
...
I would like the following macro to shut down all open files including 1
hidden file and the shut down the Excel program. I have used this macro
for
many years in Excel 5. I am now upgrading to Excel 2002 where it doesn't
work. Do you have any suggestions as to what the problem could be?



Dim Index As Integer, Number As Integer, FinalNumber As Integer

'Index
is a loop counter

Number = Application.Workbooks.Count 'The Number of open Workbooks

FinalNumber = Number - 1 'The 1 refers to the
hidden file

For Index = 1 To FinalNumber 'Place the Workbook
names
in an array

ActiveWorkbook.Close saveChanges:=True

Next Index

Application.Quit



Thanks,



Roger

Macro update
 

Workbooks count will return the count of open workbooks hidden or otherwsie.
So just us the following. However place the code in the personal book or
the last book to close, else the macro will terminate early!

Sub test()
Dim Index As Integer
For Index = 1 To Application.Workbooks.Count
ActiveWorkbook.Close saveChanges:=True
Next Index
Application.Quit
End Sub

--
Cheers
Nigel



"Roger" wrote in message
...
I would like the following macro to shut down all open files including 1
hidden file and the shut down the Excel program. I have used this macro
for
many years in Excel 5. I am now upgrading to Excel 2002 where it doesn't
work. Do you have any suggestions as to what the problem could be?



Dim Index As Integer, Number As Integer, FinalNumber As Integer

'Index
is a loop counter

Number = Application.Workbooks.Count 'The Number of open Workbooks

FinalNumber = Number - 1 'The 1 refers to the
hidden file

For Index = 1 To FinalNumber 'Place the Workbook
names
in an array

ActiveWorkbook.Close saveChanges:=True

Next Index

Application.Quit



Thanks,



Roger

Macro update
 

Tom, thanks for your suggestion. It works purrrfecly, even does something
that I had forgot to mention! Thanks to Bob and Nigel as well. Nigel I
didn't get yours in time to try it as Tom's macro was very good.

Regards to all,

Roger



"Nigel" wrote in message
...
Workbooks count will return the count of open workbooks hidden or
otherwsie.
So just us the following. However place the code in the personal book or
the last book to close, else the macro will terminate early!

Sub test()
Dim Index As Integer
For Index = 1 To Application.Workbooks.Count
ActiveWorkbook.Close saveChanges:=True
Next Index
Application.Quit
End Sub

--
Cheers
Nigel



"Roger" wrote in message
...
I would like the following macro to shut down all open files including 1
hidden file and the shut down the Excel program. I have used this macro
for
many years in Excel 5. I am now upgrading to Excel 2002 where it doesn't
work. Do you have any suggestions as to what the problem could be?



Dim Index As Integer, Number As Integer, FinalNumber As Integer


'Index
is a loop counter

Number = Application.Workbooks.Count 'The Number of open Workbooks

FinalNumber = Number - 1 'The 1 refers to the
hidden file

For Index = 1 To FinalNumber 'Place the Workbook
names
in an array

ActiveWorkbook.Close saveChanges:=True

Next Index

Application.Quit



Thanks,



Roger