How open statements effect code

I have a piece of code that does not run at the speed i would expect it to, after completing some testing i believe i have narrowed it down to behaviour related to an open statement, basically i created the below statement to tst where the proble lay

Dim i As Integer
Workbooks.Open ("S:\intemediate.xls")
Workbooks.Open ("S:\supply.xls")

Do
i = 0 + i
Windows("intermediate.xls").Activate
Windows("Supply.xls").Activate
i = i + 1
Loop Until i = 100

My problem is that wehn my loop statements run they do so at snails pace, if however i highlight out the workbooks.open and open them manually the code completes in seconds rather than minutes!!

Can anyone explain why this is as it could have an overall bearing of the way i'm approaching the problem as i need to open several different workbooks and copy the data back to the intermediate workbook. perhaps if you test the code yourself you will have a better idea of what im going on about

cereldine,
Can't say about the speed, but the normal way to write this type of code is:
Dim WB1 as Workbook
Dim WB2 As Workbook

Set WB1=Workbooks.Open ("S:\intemediate.xls")
Set WB2=Workbooks.Open ("S:\supply.xls")
With WB1
'Do something
.......

And normally this is no need to .select/.activate objects in order to work
with them.
The time it takes to open a file depends on its size, any _Open code etc.

By the way your "i=0+i" does nothing.

NickHK

"cereldine" <[email protected]> wrote
in message news:[email protected]...
>
> I have a piece of code that does not run at the speed i would expect it
> to, after completing some testing i believe i have narrowed it down to
> behaviour related to an open statement, basically i created the below
> statement to tst where the proble lay
>
> Dim i As Integer
> Workbooks.Open ("S:\intemediate.xls")
> Workbooks.Open ("S:\supply.xls")
>
> Do
> i = 0 + i
> Windows("intermediate.xls").Activate
> Windows("Supply.xls").Activate
> i = i + 1
> Loop Until i = 100
>
> My problem is that wehn my loop statements run they do so at snails
> pace, if however i highlight out the workbooks.open and open them
> manually the code completes in seconds rather than minutes!!
>
> Can anyone explain why this is as it could have an overall bearing of
> the way i'm approaching the problem as i need to open several different
> workbooks and copy the data back to the intermediate workbook. perhaps
> if you test the code yourself you will have a better idea of what im
> going on about
>
>
> --
> cereldine
> ------------------------------------------------------------------------
> cereldine's Profile:
http://www.excelforum.com/member.php...o&userid=32069
> View this thread: http://www.excelforum.com/showthread...hreadid=534904
>