Search Excel spreadsheet with string from Access form

[matt beamish]

Hi folks. I am trying to make a link between an Access form, and an Excel spreadsheet so that a user can open the spreadsheet with a button click, and initiate the Excel 'Find' function to take them to the cell containing a string lifted from form control. I need the search to run across sheets within the workbook.
The search string is 'Matrixsrch'
The location of the spreadsheet is stored in the database ('GroupsMatrixLoccntrl')

So far, the spreadsheet is opening, but I am not making any progress with the search. I have got a search working successfully with Pdfs, but not managed it with xls/xlsx.

This is what opens the spreadsheet:

Private Sub Command132_Click()

Dim filename As String
Dim searchstring As String
searchstring = Me.Matrixsrch
filename = Me.GroupsMatrixLoccntrl
Dim xlTmp As Excel.Application
Set xlTmp = New Excel.Application
xlTmp.Workbooks.Open filename
xlTmp.Visible = True

 
End Sub

Any help, as ever very appreciated.
Matt

 

[theDBguy]

Hi Matt. Just a thought, but have you looked into the Range.Find method?

 

[matt beamish]

Thanks for heads up theDBguy.

I now have

Private Sub Command132_Click()

On Error GoTo Err_Command132_Click

Dim filename As String
Dim searchstring As String

Dim Xlsh As Excel.Worksheet
Dim xlValues As Variant
Dim xlTmp As Excel.Application
Dim xlPart As Variant
Dim xlByRows As Variant
Dim xlNext As Variant


Set xlTmp = New Excel.Application

searchstring = Me.Matrixsrch
filename = Me.GroupsMatrixLoccntrl

xlTmp.Workbooks.Open filename
xlTmp.Visible = True

xlTmp.ActiveSheet.Cells.Find(What:=searchstring, After:=ActiveCell, LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext).Activate

Exit_Command132_Click:
    Exit Sub

Err_Command132_Click:
    MsgBox Err.Description
    Resume Exit_Command132_Click


End Sub

This decompiles without error, but if I try to run, I get a 'type mismatch' error. Progress though!

 

[theDBguy]

Which line is causing the error?

 

[matt beamish]

xlTmp.ActiveSheet.Cells.Find(What:=searchstring, After:=ActiveCell, LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext).Activate

 

[matt beamish]

However, the search is working as the cell immediately after a cell I am looking for, is the focus when the spreadsheet opens. The error msg is now:

'Run time error '9':
Subscript out of range.

More progress.

 

[matt beamish]

No, I'm wrong, it was because the spreadsheet had saved with that cell as focus - and I had just entered a dummy value to search for, doh. The search is not running at all.

 

[Darrell]

You may need to activate the sheet first.

Something like

xlTmp.Sheets(1).Activate

 

[matt beamish]

Thanks for input. I added that line in, and but the error message remains
'Run time error '9':
Subscript out of range.

 

[matt beamish]

I'd also really like to close any instances of the open Excel file on the machine that this event is being run from, before opening and running the search if anyone can help with that.
thanks

 

[Darrell]

Ok now I've looked at this some more I think there are a couple of things that aren't quite right and one of them is one I put you crook on.. You shouldn't need to declare the variables of the Range.Find and I had you referring to your application when you should be referring to a workbook.

So, I've kind of tested the guts of this in Excel, but not Access so you may want to have a play with this to see if it does what you're after.

Private Sub Command132_Click()

On Error GoTo Err_Command132_Click

Dim filename As String
Dim searchstring As String

Dim xlTmp As Excel.Application
Dim XlBook As Excel.Workbook
Dim Xlsh As Excel.Worksheet

Set xlTmp = New Excel.Application

searchstring = Me.Matrixsrch
filename = Me.GroupsMatrixLoccntrl

Set XlBook = xlTmp.Workbooks.Open filename
xlTmp.Visible = True

Set XlSheet = XlBook.Sheets(1)

With xlTmp
    XlSheet.Activate
    XlSheet.Cells.Find(What:=searchstring, After:=Range("A1"), LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows,   SearchDirection:=xlNext).Activate
End With

Exit_Command132_Click:
    Exit Sub

Err_Command132_Click:
    MsgBox Err.Description
    Resume Exit_Command132_Click

End Sub

 

[matt beamish]

Thanks for this Darrell, I've had a go.

In access vba,
this line

Set XlBook = xlTmp.Workbooks.Open filename

gets highlighted red
with a 'Compile error: Syntax error message.'

if I take out that line and replace with the previous

xlTmp.Workbooks.Open filename

the file opens, but I get
'Object variable or with block variable not set.

_________
Matt

 

[Darrell]

Hi Matt, can you try brackets around filename

 

[matt beamish]

Hi Matt, can you try brackets around filename

I have now bracketed "filename" and with a bit of tweaking my declarations I have the following, which results in the spreadsheet not opening, and
an error message;

Error 438; Object doesn't support this property or method

With this code

Private Sub Command132_Click()

On Error GoTo Err_Command132_Click

Dim filename As String
Dim searchstring As String

Dim xlApp As Excel.Application 'Excel object
Dim XlBook As Excel.Workbook 'Workbookj object
'Dim Xlsh As Excel.Worksheet
Dim Xlsheet As Excel.Worksheet 'Worksheet object
'Set xlApp = New Excel.Application
Set xlApp = CreateObject("Excel.Application")

searchstring = Me.Matrixsrch
filename = Me.GroupsMatrixLoccntrl

Set XlBook = xlApp.Xlbooks.Open(filename)
xlApp.Visible = True

Set Xlsheet = XlBook.Sheets(1)

With xlApp
    Xlsheet.Activate
    'xlsheet.Cells.Find(What:=searchstring, After:=Range("A1"), LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext).Activate
     Xlsheet.Cells.Find(What:=searchstring, After:=ActiveCell, LookIn:=xlvalues, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False).Activate
End With

Exit_Command132_Click:
    Exit Sub

Err_Command132_Click:
    MsgBox "Error " & Err.Number & "; " & Err.Description
    Resume Exit_Command132_Click

End Sub

 

[ebs17]

Approximately :

Set Xlsheet = XlBook.Sheets(1)

With Xlsheet
    'xlsheet.Cells.Find(What:=searchstring, After:=Range("A1"), LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext).Activate
     
     .Cells.Find(What:=searchstring, After:=.ActiveCell, LookIn:=xlvalues, LookAt:=xlPart, _
          SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False,  _
          SearchFormat:=False).Activate
End With

See after: .ActiveCell
A reference to the parent object is necessary.
And: Cells are objects of a sheet, not objects of an Excel instance.

Eberhard

 

[matt beamish]

Thanks Eberhard

See after: .ActiveCell
A reference to the parent object is necessary.
And: Cells are objects of a sheet, not objects of an Excel instance.

Please can you be more explicit?
I should be adding a reference to the parent of what ?

thanks again
Matt

 

[ebs17]

With Xlsheet
.Cells.Find(What:=searchstring, After:=.ActiveCell, LookIn:=xlvalues, LookAt:=xlPart, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, _
SearchFormat:=False).Activate
End With

With the preceding point, the objects Cells and ActiveCell are already referenced on the Excel sheet. Have you ever tested?
When using ActiveCell there should also be an active cell, otherwise it is better to use an address such as .Cells("A2") or .Cells(2, 1)

Note: ActiveCell could be on a different worksheet. Errors are possible without reference to the worksheet.

Eberhard

 

[matt beamish]

Thanks - I understand better.
With this (also making explicit my search string). I get the same 'Error 91; Object variable or With block variable not set'

With Xlsheet
         .Cells.Find(What:="1434", After:=.Cells("A2"), LookIn:=xlvalues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate
End With

re testing. No I hadn't tested as I would need to do that from within Excel and I don't often go into VBA in Excel (lame excuse, I know)

Matt

 

[ebs17]

Object variable or With block variable not set

That exists and still works?

Set xlApp = CreateObject("Excel.Application")
searchstring = Me.Matrixsrch
filename = Me.GroupsMatrixLoccntrl
'Set XlBook = xlApp.Xlbooks.Open(filename)    ' <= False
Set XlBook = xlApp.Workbooks.Open(filename)
xlApp.Visible = True
Set Xlsheet = XlBook.Sheets(1)     ' better: XlBook.Sheets("NameSheet")

Eberhard

 

[matt beamish]

Thanks Eberhard you have sorted this! I changed the
Set XlBook = xlApp.Workbooks.Open(filename) line and that was enough.

How can I ask the find to search across all worksheets?
set Xlsheet is specifying a sheet, and I need to search across all sheets and find duplicates

thanks again - really appreciated
Matt