Thanks for this idea (and to Rob for his suggestion to export to a flat file -
unfortunately the excel files are too volatile to really make that practical).
I did try turning off screen update, and setting calculate to manual but it
seemed to have little effect.
I have since discovered how to open and process Excel files using ADO and I've
converted the program to do it that way - it absolutely flies compared to the
Excel method! I can now process all 20 workbooks in a few seconds and I'm
quite happy.
Thanks again.
Ian
----- Original Message -----
From: Unicorn.PC.Support
To: [EMAIL PROTECTED]
Sent: Tuesday, November 30, 2004 7:33 PM
Subject: Re: [vbhelp] Excel
----- Original Message -----
From: "Ian Brooke" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Wednesday, December 01, 2004 1:39 AM
Subject: [vbhelp] Excel
>
>
> Hi folks,
> Following the success of my recent questions (thanks again) I though
> I would try something different. Somebody has to give you all
> something to do!
>
> I'm using VB6 to drive Excel 2002 to open a series of workbooks,
> each with a single worksheet consisting of on average 1000 rows
> (although some have up to 3000) each of 6 columns. As each sheet is
> opened the program has to check all the cells in 3 columns looking
> for particular values.
>
> It uses .Range to get the number of rows in the sheet and uses that
> in a FOR loop to access each row, on each row it uses .Cell to
> access the 3 columns looking for these values.
>
> It takes *forever*! Typically a 1000 row sheet takes almost 15
> seconds to process (and that's on a 3Ghz dual P4 with 1Gb ram) and
> opening the full 20 workbooks takes what seems like a lifetime!
>
> Can anyone suggest a (much) faster way of doing this?
>
> With thanks
> Ian
Turn off Auto Update / Auto Calculate in excel it has been years, so I am
not sure of the name, but the performance increase will be impressive
Matt
.
'// =======================================================
Rules : http://ReliableAnswers.com/List/Rules.asp
Home : http://groups.yahoo.com/group/vbHelp/
=======================================================
Post : [EMAIL PROTECTED]
Join : [EMAIL PROTECTED]
Leave : [EMAIL PROTECTED]
'// =======================================================
Yahoo! Groups Sponsor
ADVERTISEMENT
------------------------------------------------------------------------------
Yahoo! Groups Links
a.. To visit your group on the web, go to:
http://groups.yahoo.com/group/vbhelp/
b.. To unsubscribe from this group, send an email to:
[EMAIL PROTECTED]
c.. Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.802 / Virus Database: 545 - Release Date: 28/11/2004
[Non-text portions of this message have been removed]
------------------------ Yahoo! Groups Sponsor --------------------~-->
$9.95 domain names from Yahoo!. Register anything.
http://us.click.yahoo.com/J8kdrA/y20IAA/yQLSAA/k7folB/TM
--------------------------------------------------------------------~->
'// =======================================================
Rules : http://ReliableAnswers.com/List/Rules.asp
Home : http://groups.yahoo.com/group/vbHelp/
=======================================================
Post : [EMAIL PROTECTED]
Join : [EMAIL PROTECTED]
Leave : [EMAIL PROTECTED]
'// =======================================================
Yahoo! Groups Links
<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/vbhelp/
<*> To unsubscribe from this group, send an email to:
[EMAIL PROTECTED]
<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/
