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

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

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

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

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

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

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

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

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

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

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