need to load 5 20mb excel files

J

Judo Tom

Member
Joined
Oct 24, 2002
Messages
7
I have a program that needs to open 5 excel files that are 20mb. each file needs to open calc and then save. this takes about 2 mins and about 3-4 on a laptop. is there anyway to load the files into memory to minimize the hard drive access? or is there any other tips/tricks i can use to speed up this process.


the workbooks contains about 9000 rows with 200 fields on one sheet with very little formulas and the 2nd sheet is a matrix of about 200 rows by 50 columns that are mainly vlookups to the first sheet..

any ideas would be greatly appreciated.

Thanks
Tom
 
Hi Tom,

Hm, let see if we can speed things up.

Sheet1 contains, as far as I can understand, only text so its nothing You can do about it.

Sheet2 is the target here. Would it be possible for You to replace the VLOOKUPS with a VBA-solution instead?

In general You should turn off the automatic calculation (Tools | Options | Tab Calculation) and either to it manually, i e hit the F9-button or have it executed in a VBA-procedure.

You may also consider to replace the VLOCKUPS-functions with INDEX/MATCH-functions that require less amount of memory.

Kind regards,
Dennis
 
Thanks Dennis!

I do have the calc set to manual and i pass the calc arguement into excel.

you are correct there is very little i can do to change sheet1. My question would be do you think its faster to remove all the vlookups and do all the "math" with vba using for loops and things of that nature.. or leave the vlookups in and/or possible replace with index/match formulas...

is there really a big difference in vlookups vs index/match?


and again if there is anyway to have vb .net load the files into memory or something like that .. or is that just a pipe dream.

Thanks.
Tom
 
Tom,

In general match/index is faster then vlookups - please see Charles Williams good comments about it:

http://www.decisionmodels.com/optspeede.htm

As for VBA vs built-in functions it depends on the number of and how to lookup. If You use index/match then Im not sure but propably the index/match would be faster then VBA.

However, if You have many lookups and replace them with values then it will have some impact on the filesize. But this require that You move the data to new workbooks (XL does not by automation fully decrease the filesize.)

As for the VB.Net approach Im not the guy to give You any good answers (yet!).

However in VB I would test to read the contents into arrays of the variant-type via ADO/Recordset.GetRows but it depends on the data itself...

Kind regards,
Dennis
 
Back
Top