I have a workbook that contains a macro which, when
triggered, runs through a loop to open a number of other
workbooks. As each opens, a simple calculation is
performed on some data held in it and the result is added
to a new row in the original workbook. I guess I have two
questions really:
1. The process of opening and closing each workbook is
quite slow - is there a more efficient way of doing this?
2. If the answer to Q1 is no, then problem I have is that,
from a user persepctive, having all of these workbooks
flashing up on the screen will be irritating. Is there a
way of opening the workbooks but not displaying them (like
the personal.xls workbook) or of not refreshing the screen
while the macro runs (like the docmd.echo false command in
Access VBA)?

Thank you in advance for any help!!!

Hi

2) In Excel VBA it's
Application.Screenupdating = False
'your loop
Application.Screenupdating = True

This may solve part of 1), it speeds the execution. To speed it even more,
we'd have to read it.

I suggest you add a progress indicator if your operation takes more than 4
seconds. See
http://j-walk.com/ss/excel/tips/tip34.htm
for a good-looking solution.

HTH. Best hishes Harald

"Andy" skrev i melding
...
I have a workbook that contains a macro which, when
triggered, runs through a loop to open a number of other
workbooks. As each opens, a simple calculation is
performed on some data held in it and the result is added
to a new row in the original workbook. I guess I have two
questions really:
1. The process of opening and closing each workbook is
quite slow - is there a more efficient way of doing this?
2. If the answer to Q1 is no, then problem I have is that,
from a user persepctive, having all of these workbooks
flashing up on the screen will be irritating. Is there a
way of opening the workbooks but not displaying them (like
the personal.xls workbook) or of not refreshing the screen
while the macro runs (like the docmd.echo false command in
Access VBA)?

Thank you in advance for any help!!!

Thanks very much Harald

I'll give this a go and see how I get on! If it still
runs slowly I'll copy the code into this message box.

Thanks again

Andy


-----Original Message-----
Hi

2) In Excel VBA it's
Application.Screenupdating = False
'your loop
Application.Screenupdating = True

This may solve part of 1), it speeds the execution. To
speed it even more,
we'd have to read it.

I suggest you add a progress indicator if your operation
takes more than 4
seconds. See
http://j-walk.com/ss/excel/tips/tip34.htm
for a good-looking solution.

HTH. Best hishes Harald

"Andy" skrev i
melding
...
I have a workbook that contains a macro which, when
triggered, runs through a loop to open a number of other
workbooks. As each opens, a simple calculation is
performed on some data held in it and the result is
added
to a new row in the original workbook. I guess I have
two
questions really:
1. The process of opening and closing each workbook is
quite slow - is there a more efficient way of doing
this?
2. If the answer to Q1 is no, then problem I have is
that,
from a user persepctive, having all of these workbooks
flashing up on the screen will be irritating. Is there
a
way of opening the workbooks but not displaying them
(like
the personal.xls workbook) or of not refreshing the
screen
while the macro runs (like the docmd.echo false command
in
Access VBA)?

Thank you in advance for any help!!!


.