Painfully Slow Excel Find

[idbr]

Hi,

I'm running a looping process from an Access module to find values in a series of Excel Workbooks. The workbooks are typically small, averaging around 3-5000 rows of data across 10 columns. I'm copying the files to my C:\ drive to process them. Simplified:

'Check each file in the list
Do Until rstFiles.EOF

appXL.Workbooks.Open rstFiles("FilePath")

'Copy file to C:\ here

'Look for all the values I want to find
Do Until rstValues.EOF

call Excel_Find(rstValues("ValueToFind"), "C:\FileToProcess")

rstValues.MoveNext

Loop

appXL.Workbooks.Close

rstFiles.MoveNext

Loop

The find operation itself is ridiculously slow, each is taking around 10 seconds to return a value. I can manually return a result pretty much instantaneously, as I can from the Excel VBE. It's just from Access I'm having the problem.

I'd wondered if it has something to do with already having the book open and using GetObject, so I ran the find operation direct in the main routine. This didn't improve things.

Here's the code:

Public Function Excel_Find(strWhat As String, strFileName As String) As Integer

'Find instances of strWhat in workbook strFileName

Dim c                   As Range

Dim strFirstAddress     As String

Dim i                   As Integer

Dim appXL               As Object

'Workbook will already be open, so just grab its window
Set appXL = GetObject(, "Excel.Application")

'No need for system messages
appXL.DisplayAlerts = False

'Initialise the counters
i = 0

'Select all worksheets so we only have to search once
appXL.ActiveWorkbook.Worksheets.Select

'------------------------------
'Have we found the search term?
'------------------------------

[COLOR=blue]Set c = appXL.Cells.Find(what:=strWhat, LookIn:=xlValues, lookat:=xlWhole)[/color blue]

'EXTREMELY SLOW FROM ABOVE TO NEXT

If Not c Is Nothing Then 'yes we have

    Debug.Print strWhat
    
    'Increase the counter
    i = i + 1
    
    'Set the first found cell to avoid an endless loop
    strFirstAddress = c.Address
    
    '------------------------------------------------------------
    'Loop through the sheet, looking for further unique instances
    '------------------------------------------------------------
    
    Do
        
        'Find the next instance
        Set c = appXL.Cells.FindNext(c)
        
        'Increase the counter
        i = i + 1
        
    Loop While Not (c.Address = strFirstAddress Or i > 2)
    
End If

Excel_Find = i

Set appXL = Nothing
i = Empty
strWhat = Empty
strFileName = Empty
strFirstAddress = Empty

End Function

Any advice would be appreciated, I'm running Excel & Access 2000 on Win 2k.

Cheers, Iain

 

[kjv1611]

My first guess is that running it in Excel, natively, will automatically be faster than running it from Access. Also, when you are using Recordsets, you are putting it all in memory, then searching. Is it not possible to search within the workbooks without using recordsets? I would think that would run much faster.

 

[firefytr]

Also, why are you calling an Excel application within each iteration of the function?? Why would you not want to call it before your loop so it's already initialized - and only once?

-----------
Regards,
Zack Barresse

 

[kjv1611]

Yeah, I didn't notice that, firefytr.

idbr, try taking the application reference out of your loop, and moving it to the top of your procedure, and then post back with the results.

 

[idbr]

Hi Gents, thanks for the input.

I'd simplified somewhat in the original post, so a bit more background:

I'm investigating a list of 2000+ .xls files for a series of 12 different data type items, e.g, a list of 198 names, 14 street name identifiers, 122 telephone number area codes etc. Each list of items lives in a different table in an Access db. Each file is investigated for each value, until a certain number have been found and the file can be flagged as containing that type of personal data.

The primary procedure loops through a sequence of files listed in a table. This is the first recordset to be opened, rstFiles.

These files are opened using an Excel Automation object in the primary procedure.

For each file in rstFiles, the series of find operations is run for each list of data type items by opening each list in the rstValues recordset and then calling the secondary procedure (Excel_Find) for each value in the list.

In order to use this modular structure, I have to grab the already open instance of Excel I created in the primary procedure. Hence I'm using GetObject in the secondary.

Phew

Moving the find operation direct to the main procedure and bypassing the GetObject call completely doesn't give me a speed benefit.

Any other suggestions?

 

[firefytr]

If you know where to look and the data structure permits, you could think about using autofilter. Also, maybe think about loading the open workbooks/worksheets names into an array - they're much faster to loop through than an actual object.

-----------
Regards,
Zack Barresse

 

[xlbo]

Not 100% sure on my technical terminology but I believe using the 'object' data type means you are using Late Binding to the excel app - you may want to try early binding by specifying

Dim appXL as Excel.Application

quite willing to be corrected here.......

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[firefytr]

No, you're quite correct Geoff.

-----------
Regards,
Zack Barresse

 

[xlbo]

that'd be a 1st then ;-)

ps - looking at updating the FAQs from your comments Zack

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[idbr]

Geoff,

I'm opening the workbook with early binding in the main proc, but have to grab it using late binding in the secondary otherwise I get a new instance.

Firefytr,

Don't know the data structure I'm afraid - that's part of the problem :-(. Hence having to take on the ridiculous job of about 10 squillion find operations in a row...

As for the loop, I don't think the recordset is the problem, it's the find operation itself that is taking so long. My guess is that in some way it is a referencing problem, but am not sure how, why or what?? %-)

Thanks, Iain

 

[kjv1611]

Take an Advil, Asprin, or Tylenol. Sounds like you've got a headache!

 

[xlbo]

why not have the applicaiton object as a global / public variable so that it is available to both the primary sub and the function.....

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[firefytr]

You could do that to most of your variables, actually..

Dim c As Excel.Range
Dim strFirstAddress As String
Dim i As Long 'changed from Integer
Dim appXL As Excel.Application

That is if you have referenced the Excel OM.

Also, what is the counter for anyway?

Try adding, around this line..

appXL.DisplayAlerts = False

.. these lines ..

appXL.DisplayAlerts = False
appXL.Screenupdating = False
appXL.Enableevents = False

Make sure you turn them back to True when you are finished with your primary routine though.

-----------
Regards,
Zack Barresse

 

[xlbo]

yeh - that way you don't need any late binding or to get any of the objects as they are already available to you

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[idbr]

Thanks Gents, will have a look at that.

Firefytr, the counter is so I can dump out of the find routine once I've found three instances.

kj1611, reckon Hemlock might be a better idea, lol ;-)