| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Closing files by a macro
Hi all
I have 20 excellworkbooks open with diferent names. Problem is to save and close theese files as fast as possible by a macro. As it is now, one file has to be saved and closed before I can run the macro again manually. Is the a vbariable there can be used to automatic restart the macro??? anyone have an idea?? The macro is placed in Personal.xls. regards kurt |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Closing files by a macro
See http://www.cpearson.com/excel/ontime.htm. I think it will do what you
ask. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "kurt" wrote in message oups.com... Hi all I have 20 excellworkbooks open with diferent names. Problem is to save and close theese files as fast as possible by a macro. As it is now, one file has to be saved and closed before I can run the macro again manually. Is the a vbariable there can be used to automatic restart the macro??? anyone have an idea?? The macro is placed in Personal.xls. regards kurt |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Closing files by a macro
Hi Bob
Than you for the fast response, I know that I could put in a timer , but some of the workbooks takes 10 to 15 sec, some 1 to 2 sec before it is saved and closed it is a "savings is finished" constrant I am looking for. regards Kurt Bob Phillips skrev: See http://www.cpearson.com/excel/ontime.htm. I think it will do what you ask. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "kurt" wrote in message oups.com... Hi all I have 20 excellworkbooks open with diferent names. Problem is to save and close theese files as fast as possible by a macro. As it is now, one file has to be saved and closed before I can run the macro again manually. Is the a vbariable there can be used to automatic restart the macro??? anyone have an idea?? The macro is placed in Personal.xls. regards kurt |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Closing files by a macro
Kurt,
If you are saving and closing the ActiveWorkbook, another workbook's _Activate event will fire as it becomes the new ActiveWorkbook. Is that what you mean ? NickHK "kurt" egroups.com... Hi Bob Than you for the fast response, I know that I could put in a timer , but some of the workbooks takes 10 to 15 sec, some 1 to 2 sec before it is saved and closed it is a "savings is finished" constrant I am looking for. regards Kurt Bob Phillips skrev: See http://www.cpearson.com/excel/ontime.htm. I think it will do what you ask. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "kurt" wrote in message oups.com... Hi all I have 20 excellworkbooks open with diferent names. Problem is to save and close theese files as fast as possible by a macro. As it is now, one file has to be saved and closed before I can run the macro again manually. Is the a vbariable there can be used to automatic restart the macro??? anyone have an idea?? The macro is placed in Personal.xls. regards kurt |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Closing files by a macro
Dim wb as Workbook
For each wb in Workbooks if not wb.name = "Personal.xls" Then wb.Close True End if next Is that what you are trying to accomplish? Charles kurt wrote: Hi all I have 20 excellworkbooks open with diferent names. Problem is to save and close theese files as fast as possible by a macro. As it is now, one file has to be saved and closed before I can run the macro again manually. Is the a vbariable there can be used to automatic restart the macro??? anyone have an idea?? The macro is placed in Personal.xls. regards kurt |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Closing files by a macro
You might want to throw in : ActiveWorkbook.Save : in Charles' code to save
and close. "Die_Another_Day" wrote: Dim wb as Workbook For each wb in Workbooks if not wb.name = "Personal.xls" Then wb.Close True End if next Is that what you are trying to accomplish? Charles kurt wrote: Hi all I have 20 excellworkbooks open with diferent names. Problem is to save and close theese files as fast as possible by a macro. As it is now, one file has to be saved and closed before I can run the macro again manually. Is the a vbariable there can be used to automatic restart the macro??? anyone have an idea?? The macro is placed in Personal.xls. regards kurt |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Closing files by a macro
JLG, The syntax for Close is:
ActiveWorkbook.Close SaveChanges:=True So the " True" takes care of the save. Charles JLGWhiz wrote: You might want to throw in : ActiveWorkbook.Save : in Charles' code to save and close. "Die_Another_Day" wrote: Dim wb as Workbook For each wb in Workbooks if not wb.name = "Personal.xls" Then wb.Close True End if next Is that what you are trying to accomplish? Charles kurt wrote: Hi all I have 20 excellworkbooks open with diferent names. Problem is to save and close theese files as fast as possible by a macro. As it is now, one file has to be saved and closed before I can run the macro again manually. Is the a vbariable there can be used to automatic restart the macro??? anyone have an idea?? The macro is placed in Personal.xls. regards kurt |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Closing files by a macro
I stand corrected. tnx Chs.
"Die_Another_Day" wrote: JLG, The syntax for Close is: ActiveWorkbook.Close SaveChanges:=True So the " True" takes care of the save. Charles JLGWhiz wrote: You might want to throw in : ActiveWorkbook.Save : in Charles' code to save and close. "Die_Another_Day" wrote: Dim wb as Workbook For each wb in Workbooks if not wb.name = "Personal.xls" Then wb.Close True End if next Is that what you are trying to accomplish? Charles kurt wrote: Hi all I have 20 excellworkbooks open with diferent names. Problem is to save and close theese files as fast as possible by a macro. As it is now, one file has to be saved and closed before I can run the macro again manually. Is the a vbariable there can be used to automatic restart the macro??? anyone have an idea?? The macro is placed in Personal.xls. regards kurt |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Closing files by a macro
np glad to help :)
charles JLGWhiz wrote: I stand corrected. tnx Chs. "Die_Another_Day" wrote: JLG, The syntax for Close is: ActiveWorkbook.Close SaveChanges:=True So the " True" takes care of the save. Charles JLGWhiz wrote: You might want to throw in : ActiveWorkbook.Save : in Charles' code to save and close. "Die_Another_Day" wrote: Dim wb as Workbook For each wb in Workbooks if not wb.name = "Personal.xls" Then wb.Close True End if next Is that what you are trying to accomplish? Charles kurt wrote: Hi all I have 20 excellworkbooks open with diferent names. Problem is to save and close theese files as fast as possible by a macro. As it is now, one file has to be saved and closed before I can run the macro again manually. Is the a vbariable there can be used to automatic restart the macro??? anyone have an idea?? The macro is placed in Personal.xls. regards kurt |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Closing files by a macro
Hi all
This macro is closing a numbers of workbooks as fast as i can. Private Sub Closeall() On Error Resume Next f = Dir(CurDir & "\", 7) Do While f < "" f = Dir .. .. here you can chek for read only files .. here you can close all sheets. ActiveWorkbook.Close savechanges:=True Loop End Sub regards Kurt Die_Another_Day skrev: np glad to help :) charles JLGWhiz wrote: I stand corrected. tnx Chs. "Die_Another_Day" wrote: JLG, The syntax for Close is: ActiveWorkbook.Close SaveChanges:=True So the " True" takes care of the save. Charles JLGWhiz wrote: You might want to throw in : ActiveWorkbook.Save : in Charles' code to save and close. "Die_Another_Day" wrote: Dim wb as Workbook For each wb in Workbooks if not wb.name = "Personal.xls" Then wb.Close True End if next Is that what you are trying to accomplish? Charles kurt wrote: Hi all I have 20 excellworkbooks open with diferent names. Problem is to save and close theese files as fast as possible by a macro. As it is now, one file has to be saved and closed before I can run the macro again manually. Is the a vbariable there can be used to automatic restart the macro??? anyone have an idea?? The macro is placed in Personal.xls. regards kurt |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Closing files by a macro
Kurt,
Not sure why you have loop with Dir() if you are closing workbooks... The "normal" way would be : Dim WS As Workbook For Each WS In Workbooks If WS.Name < ThisWorkbook.Name Then WS.Close SaveChanges:=True Next But it depends somewhat on where you are running this code. NickHK "kurt" wrote in message oups.com... Hi all This macro is closing a numbers of workbooks as fast as i can. Private Sub Closeall() On Error Resume Next f = Dir(CurDir & "\", 7) Do While f < "" f = Dir . . here you can chek for read only files . here you can close all sheets. ActiveWorkbook.Close savechanges:=True Loop End Sub regards Kurt Die_Another_Day skrev: np glad to help :) charles JLGWhiz wrote: I stand corrected. tnx Chs. "Die_Another_Day" wrote: JLG, The syntax for Close is: ActiveWorkbook.Close SaveChanges:=True So the " True" takes care of the save. Charles JLGWhiz wrote: You might want to throw in : ActiveWorkbook.Save : in Charles' code to save and close. "Die_Another_Day" wrote: Dim wb as Workbook For each wb in Workbooks if not wb.name = "Personal.xls" Then wb.Close True End if next Is that what you are trying to accomplish? Charles kurt wrote: Hi all I have 20 excellworkbooks open with diferent names. Problem is to save and close theese files as fast as possible by a macro. As it is now, one file has to be saved and closed before I can run the macro again manually. Is the a vbariable there can be used to automatic restart the macro??? anyone have an idea?? The macro is placed in Personal.xls. regards kurt |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Closing Files | Excel Discussion (Misc queries) | |||
| Closing Files | Excel Discussion (Misc queries) | |||
| closing all files | Excel Programming | |||
| Closing files and saving files | Excel Worksheet Functions | |||
| Save files before closing | Excel Discussion (Misc queries) | |||
Linear Mode
