I have a routine that imports worksheets from an older version of my
companies software to the latest version. Using it in office 2000/2003
a sheet with about 200 rows takes about 20-30 seconds. In office 2007
it was taking close to 6 minutes. I have already made several changes
and gotten the time down to just over a minute, but I would like to
know if anyone has an idea of what would cause this slowdown.
A little background info: The procedure starts off fine but as it
progresses it takes longer and longer to process each row. It
basically sets the value for a cell in the new workbook to the value
of the coresponding cell in the old workbook. Oh yeah and the last
thing you should know...Its legacy code in VB6

Put your data into an array and store it a row at a time.

Storing data a cell at a time is a time killer.

The psuedo code for this is:

data = Array(5,7,8,12)
Range(myrow.Cells(1,1),Cells(1,4)).Value = data

It's also possible to insert the data for the entire Worksheet with a
similar single instruction.

In this instance, because there are only 200 rows, I recommend you
take my example, study it, and then figure out how to do this.

This will DRAMATICALLY improve performance.

Summary. Whenever possible store data a row at a time. If possible
store with a single instruction.


On Mar 15, 2:58 pm, "LoDawg" wrote:
I have a routine that imports worksheets from an older version of my
companies software to the latest version. Using it in office 2000/2003
a sheet with about 200 rows takes about 20-30 seconds. In office 2007
it was taking close to 6 minutes. I have already made several changes
and gotten the time down to just over a minute, but I would like to
know if anyone has an idea of what would cause this slowdown.
A little background info: The procedure starts off fine but as it
progresses it takes longer and longer to process each row. It
basically sets the value for a cell in the new workbook to the value
of the coresponding cell in the old workbook. Oh yeah and the last
thing you should know...Its legacy code in VB6

Thanks for the reply. I am usure however if it will help in this case.
The data transfer is multiple lines like this for example:

newWorkBook.sheets(Details).range(NAME_COL & newRow).value =
oldWorkBook.sheets(Details).range("D" & oldRow).value

To me this doesnt qualify as storage, but our definitions may differ.
Also the columns used in the old version are not the same as the ones
used in the new version. With an increased feature set columns have
been moved and to return the data back to the new workbook from an
array seems like it would require just as many commnad as I have now.
I could more easily put an array in a for loop and let it run through
the data but I dont see how I am using less commands. I dont use the
entire row from the old workbook just a few of the columns and they
are not consecutive, nor do they go into the new workbook in that
manner.

I will however give this a shot because I dont have a better idea for
now. Again, thank you for you time and suggestion.

iumt = row counter for sheet umt
iadt = row counter for sheet adt
MAX_COLUMNS = integer corresponding to last column in sheet umt

Then:

umt.Cells(iumt, "A").Resize(1, MAX_COLUMNS).Copy adt.Cells(iadt, "A")

Try copying with this syntax. That might help.