Find data on an Excel spreadsheet with VB

[otomatis]

Hi all,

I am trying to manipulate an Excel spreadsheet. I want to search the A column for a string from a text box of vb6 then display it. I can't get it to work in my VB6 app. What am I doing wrong ?

Dim mailmwo As Excel.Application
Dim mwoBook As Excel.Workbook
mwonum = txtScan

Set mailmwo = New Excel.Application
mailmwo.Workbooks.Open "C:\mailmwo\mailmwo.xls", , , , "password", "password"
mailmwo.Sheets("MWOs - Do Not Sort!").Select
mailmwo.Range("A2").Select
mailmwo.Selection.Find(What:=mwonum), After:=ActiveCell, LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ ' <- line generated by Excel macro record

The error is :

'' gives runtime error '91' : Object variable or With block variable not set

[debasisdas]

Have you included the Excel library into the project ?

[otomatis]

Yes, i have included the Excel library.

This code is running if not use variable.
but i want the keyword input from vb (txtscan.text)

dim mwonum 
mwonum = txtScan.text

mailmwo.Selection.Find(What:="[e-mail]"), After:=ActiveCell, LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ ' <- line generated by Excel macro record

[AndreRet]

Try the following -

Dim mailmwo As Excel.Application
Dim mwoBook As Excel.Workbook
mwonum = txtScan.Text

Set mailmwo = New Excel.Application
Set mwoBook = New Excell.Workbook

[monarchmk]

Runtime 91 in search pattern in Excel VBA (and in your code) is error if nothing is found. Use

On error resume next 'Enable error handler
Selection.Find ... 'Your find command with corrected parenthesis
On Error goto 0 'Disable Error handler

You have no problem. Except this errors down. Parenthesis closes on wrong location. (I assume that this is only error when you put code here as Excel will make error pop up for this also )

mailmwo.Selection.Find(What:=mwonum[B])[/B], After:=ActiveCell, LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ ' <- line generated by Excel macro record

It should close before .Activate on find like this

mailmwo.Selection.Find(What:=mwonum, After:=ActiveCell, LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
 False, SearchFormat:=False[B])[/B].Activate

red lines are lines that I had to add to get to .Activate

[trilok31]

Dear otomatis here is the solution for opening excel file and reading and finding value in particular column
i hav a excel file with following informatio sno,name,fname,cat,mobile

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

Private Sub cmdfind_Click()
If rs.State = adStateOpen Then rs.Close
    rs.Open ("select * from [sheet1$] where name='" & skey.Text & "'"), cn, adOpenStatic, adLockReadOnly
If rs.RecordCount > 0 Then
    Text1.Text = rs!fname
    Text2.Text = rs!cat
    Text3.Text = rs!mobile
Else
    Text1.Text = "NO MATCH FOUND"
End If
End Sub

Private Sub Form_Load()
Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
cn.Open ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\db.xls;Extended Properties=""Excel 8.0;HDR=YES;""")
End Sub

and in vb form took four text boxes and one command button on command button click even i search the sheet1 and then show the found result in other text boxes

one thing is there that in my sheet first value for each column is there heading thats why i put HDR=YES, os it will treat first values as field names

cn.Open ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\db.xls;Extended Properties=""Excel 8.0;HDR=YES;""")

this code is tested and working fine at my end if need further help please let me know....

[AndreRet]

People, please read the OP's thread carefully. He can open etc.:)

The error

'' gives runtime error '91' : Object variable or With block variable not set

is referring to an object that has not been declared, but it was used in code.

The error is most probably because the workbook was not declared, hence my code -

Set mwoBook = New Excell.Workbook

[monarchmk]

No.. Error handler in Excel when you are using FIND command return nonsense error.
Problem is with Excel search engine. Runtime error 91 is bassicaly "no cells containing searched value". selection.find itnernal error ahndler makes mistake. I've used FIND in a lot of Workbooks with VBA and everywhere when i forgot Error handler, i received same error.

Problem that @otomatis had when search works with fixed text and does not work with variable is probably due to extra spaces in textbox or formatin a while FIND syntax. As otomantis never publish here full selection.find i can never tell if it is complete ok. For start I wrote that there is extra parenthesis.
I sumulated whole situation with excel and vb code and everything work as i suggested above in previous post. Only error handler is problem and one parenthesis.

Here is my VB Code. Just make one workbook wiht several filed cells and try it for your selfs.

Private Sub Command2_Click()
Dim mailmwo As Excel.Application
Dim mwoBook As Excel.Workbook
mwonum = Trim(txtscan.Text)

Set mailmwo = New Excel.Application
mailmwo.Visible = True
mailmwo.Workbooks.Open "C:\asus\book1.xls", , , , "", ""   ''Change path and Workbook name to keep it simple
mailmwo.Worksheets("Sheet2").Select

mailmwo.Sheets("MWOs - Do Not Sort!").Select
mailmwo.Range("A1").Select
Debug.Print mailmwo.Range("A1").Value
'Notice, After mwonum variable there is no parenthesis
'With this coding you will receive Runtime 91 
'If you add following code everythig will work!
'ON ERROR RESUME NEXT    'NEW CODE
mailmwo.Selection.Find(What:=mwonum , After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate
        Debug.Print mailmwo.ActiveCell.Address
'ON ERROR GOTO 0      'NEW CODE to avoid RunTime 91

Yes, i have included the Excel library.

This code is running if not use variable.
but i want the keyword input from vb (txtscan.text)

dim mwonum 
mwonum = txtScan.text

mailmwo.Selection.Find(What:=[COLOR="Red"]"[e-mail]"[/COLOR]), After:=ActiveCell, LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ ' <- line generated by Excel macro record

end quote.

[AndreRet]

I have tested this and it seems you were correct, so my apologies. Your code worked fine.:)

[otomatis]

Thanks all for replay,

This code is working but for first find value only, next find is not working.

here is code :

Private Sub Command1_Click()
Dim mailmwo As Excel.Application
Dim mwoBook As Excel.Workbook
mwonum = Trim(txtscan.Text)

Set mailmwo = New Excel.Application
mailmwo.Visible = True
mailmwo.Workbooks.Open "C:\VB6\test.xls", , , , "", ""   
mailmwo.Worksheets("Sheet2").Select

'mailmwo.Sheets("MWOs - Do Not Sort!").Select
mailmwo.Range("A1").Select
Debug.Print mailmwo.Range("A1").Value
'Notice, After mwonum variable there is no parenthesis
'With this coding you will receive Runtime 91 
'If you add following code everythig will work!
ON ERROR RESUME NEXT    'NEW CODE
mailmwo.Selection.Find(What:=mwonum , After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate
        Debug.Print mailmwo.ActiveCell.Address
ON ERROR GOTO 0      'NEW CODE to avoid RunTime 91

Here is data on excel (sheet2) :
red
white
blue
green
gray
black
purple

example for first find of value is black, the result is $A$6 (black) is working. But for next find (ie.gray) is it always appoint to $A$1 (red).

I still couldn't solve the problem.

[monarchmk]

Ok, now we have concept error... with this code on every click you open new Excel file...

in form_load event or another command button event which will be tagged "Open Excel" put code for excel opening (lines 6-9)
Lines 2-3 put it in front of first line and change Dim to Public
Lines 4, 12-22 are ok...

Now when you activate form (or click open button), it will open Excel file (first change lines 6-9)
With every click on CommandButton1 code will go through open excel file and search for text stated in txtscan box.

Also at the end do not forget to close worksheet, either with form or with another button.

[otomatis]

Hi monarchmk,

The result is still same, working for first find only

Here is code :

Private Sub Command1_Click()
Public mailmwo As Excel.Application
Public mwoBook As Excel.Workbook
mwonum = Trim(txtscan.Text)

Set mailmwo = New Excel.Application
mailmwo.Visible = True
mailmwo.Workbooks.Open "C:\VB6\test.xls", , , , "", "" 
mailmwo.Worksheets("Sheet2").Select

'mailmwo.Sheets("MWOs - Do Not Sort!").Select
mailmwo.Range("A1").Select
Debug.Print mailmwo.Range("A1").Value
'Notice, After mwonum variable there is no parenthesis
'With this coding you will receive Runtime 91 
'If you add following code everythig will work!
ON ERROR RESUME NEXT    'NEW CODE
mailmwo.Selection.Find(What:=mwonum , After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate
        Debug.Print mailmwo.ActiveCell.Address
ON ERROR GOTO 0      'NEW CODE to avoid RunTime 91

mailmwo.Workbooks.Close
'set mailmwo.Worksheets = Nothing
Set mwoBook = Nothing
Set mailmwo = Nothing

End Sub

[monarchmk]

You did not read my post at all????

I sad move lines 6-9 in new procedure FORM_LOAD like this

Private Sub Form_Load()
Set mailmwo = New Excel.Application
    mailmwo.Visible = True
    mailmwo.Workbooks.Open "C:\VB6\test.xls", , , , "", ""
    mailmwo.Worksheets("Sheet2").Select

End Sub

Also close codes should be in FORM_TERMINATE like this

Private Sub Form_Terminate()
    mailmwo.Workbooks.Close
    Set mwoBook = Nothing
    Set mailmwo = Nothing
End Sub

Above ALL STATEMENTS you should put this

Public mailmwo As Excel.Application
Public mwoBook As Excel.Workbook
...
...
...

And in Command1_Click procedure delete following lines 2,3,6,7,8,9,24,26,27 because
2,3 were moved in-front of all code
6,7,8,9 were moved in FORM_LOAD
24,26,27 were moved in FORM_TERMINATED

Now if you follow this everything will work.

[otomatis]

The code i was posted before could work normally if application is close/form terminate. But i don't want to terminate/close the application to find another value.

[monarchmk]

Thats because with every click you open new excel application. Please read my previous post, all of your problems are solved there.