Search Excel spreadsheet with string from Access form (1 Viewer)

[ebs17]

'Set Xlsheet = XlBook.Sheets(1)

For Each Xlsheet In XlBook.Worksheets
   ' do something
Next

Eberhard

 

[matt beamish]

Thanks very much I'll give it a whirl in a few hours when I'm back at work.
My other wish is to close every instance of the workbook when the search is initiated, or the user will end up with an opened file for every search they do and they will be doing many searches in a session. With the pdf search I set up it was easier to terminate adobe reader before opening the file to be searched.

 

[ebs17]

XlBook.Close SaveChanges:=False
xlApp.Quit

It is true, however, that if there are several possible searches, you would not open and close Excel and workbook every time, but use one instance for everything, long enough to cover all searches. Excel is a large object, opening and closing each time increases the runtime.
Depending on the other use of the workbook, an "eternal" instance can lead to blockages that hinder such third use.

A good compromise would be to create the reference to the workbook in the open event of the form, which contains the Commandbutton and thus itself stands for the searches. In the unloading event, you could then remove the instance.

Eberhard

 

[matt beamish]

'Set Xlsheet = XlBook.Sheets(1)

For Each Xlsheet In XlBook.Worksheets
   ' do something
Next

Eberhard

This code runs and works

Set Xlsheet = XlBook.Sheets("GroupMatrix")
'For Each Xlsheet In XlBook.Worksheets
With Xlsheet
         .Cells.Find(What:=searchstring, After:=.Cells(1, 1), LookIn:=xlvalues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate
End With
'Next

This code results in a return of Object variable or With block variable not set error

For Each Xlsheet In XlBook.Worksheets
With Xlsheet
         .Cells.Find(What:=searchstring, After:=.Cells(1, 1), LookIn:=xlvalues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate
End With
Next

Eberhard

The debugger highlights
" .Cells.Find(What:=searchstring, After:=.Cells(1, 1), LookIn:=xlvalues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate"

 

[ebs17]

This is where my ability to abstraction ends. I haven't worked with excel for a long time, and I don't have a sample file to try either.

Before intensifying the code, I would first like to clarify processes. It seems difficult to suspect and want to find duplicates in many worksheets, also with regard to performance.
As a database developer, I would primarily try to be able to grab and link worksheets as a database table, and also work with a summarized Excel table instead of countless ones. Then the finding would be solvable with a simple query.

Eberhard

 

[matt beamish]

Thanks for your time. I think the duplication issue can be shelved. What is more important is to search until the first match is found. If there is no match, then 'No match'. A repeat search can be implemented by the user from within Excel if necessary. I understand about linking in the data, but the speadsheets have been used for drawing 'matrices' of archaeological layers, and there is no control over what is in what column. To use the data in a table, I think all the cells would need to concentanated into single cells perhaps with ";" separators , but even then there might be cell character length issues.