Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
need help to update macro to office 2007 macro enabled workbook | Excel Discussion (Misc queries) | |||
Macro automatic update | Excel Discussion (Misc queries) | |||
capture DDE update value in VBA macro | Excel Discussion (Misc queries) | |||
dde link update in vba macro | Excel Programming | |||
Macro update | Excel Programming |