John,

Thanks for your suggestions. The sub I sent is the one that takes around 30
seconds. The rest take around 5 seconds and are quite involved with
calculations and cell placements/formats for the final Excel report. To use
the rest of my code and to place things properly on the report, I first need
an array in the designated order - cannot paste directly to worksheet (which
would still be at the 30 second mark anyway).

I've found that doing things within vb and utilizing arrays vastly speeds
things up. I usually work with much smaller files in automated processes
where time (30 seconds to one minute) is not a concern. I'm not a trained
programmer so I'm sure there are things that I'm not aware of or that I can
do better.

I noticed that I used selects, etc and have since cleaned those up but with
little or no performance gain. If I reduce the database file to say 2,500
rows, everything runs very fast. So I'm convinced that I can not get much
better performance in time without breaking down the database file to
smaller units. That may not be possible since the Excel database file is a
nightly dump of our mainframe data and I don't think "they" will want to
modify/add to that.

Thanks for your help.

dave


"jaf" wrote in message
...
Hi Dave,
FYI: I don't work much with db's and queries but 3 seconds is a
significant
performance gain.
There are querydef's that can dynamically retrieve the field names each
time
it's run.



I'm not as good at coding as most people here, but I was looking at the
code
in your original post.
You said.
"My code opens the file, sorts it (primarily by Customer Code) searches
through a column that contains Customer Codes, once it finds the first
occurrence of the customer code, it grabs data from 32 columns (Not all
together but scattered) and puts it into an array, then loops until the
last
occurrence of the customer code is found.

Opening the file takes 15 seconds and gathering the data takes 15 seconds
resulting in 30 seconds."


You do a lot of "selects" and "Set curcell" to populate your array using
unnecessary CPU cycles.
You don't show what you do with the array. I presume it's in another sub.

My point being a worksheet is an array.
If you can populate the array with the do loop you should be able to
populate the new sheet/wb instead of the array.
Eliminating the middleman and 15 seconds or so.

(how is mycompany rowvalue found)

'Put titles in first row of array
For A = 1 To NumberOfItems
newsheet.cells(1, A) = oldsheet.Cells(1, MyGet(A))
Next A

for i=1 to numberofitems '2="A2"
for j=1 to 33
newsheet.cells(i+1,j)=oldsheet.cells(mycompany.row +i,myget(j))


Simplistic quasi pseudo code but may look something like that.


--

John

johnf202 at hotmail dot com


"Dave B" <Not.Available wrote in message
...
John,

I did work in that direction for a while. It took about 3 seconds off
the
time and I found that the Header titles change daily which blows up the
query. Several of the headings in the database file have the current
date
and end of last month as part of the title - which isn't good database
technique but it's what I have to work around.

Thanks for the input - all the techniques suggested in the newsgroup
I've
tried work great with smaller files but when the database file has
42,000
rows and 208 columns, it gums things up a bit.

dave


"jaf" wrote in message
...
Hi Dave,
I'm not looking want to confuse the issue, but have you tried dataget
external data, database query, using your Excel workbook as the
datasource.
You can sort the data as it is being pulled from the closed workbook.
I just did a quick test and Excel pulled 600 rows &12 columns of data
in
under .2 seconds.

This will use MSQuery and write a SQL query. Similar to ADO but all
Excel.
Record a macro while running the query and Excel will write the code
or
save
the query and it can be edited later.


--

John

johnf202 at hotmail dot com