Use of the VBA Find Statement in VB.net

Hi All,

I am writing a program using VB.net to interogate a data set within an excel spreadsheet. In short, after recieving a string of text from a barcode reader, the program searches down a column to find a match. If one exists then it reads the cell next door and depending on its content (ie, "Keep", "Cull" or otherwise) performs a specific action. As I am a complete novice :huh: , I initially used the For...Next statments to search throught the excel dataset until a match was found. However the dataset can be up to 30,000 rows long.... and therefore made the code run Vslow!!!! So now I am trying to use the Find command from VBA (Excel 2000) to make it quicker. But I am stuck...... please help (I got the skeleton of the code to work in VBA, but not .net).

I have posted the For... next code to show what I was trying to do, and then my atempt at using the Find comand from VBA...... where am I going wrong? VB.net is telling me that my syntax is wrong in the Range(cells etc) statement, the LookIn part of the .Find statement, and the c.Address statement.

For...Next code ...... VSLOW!!!

For x = 1 To NoSample
If x < NoSample Then
MyXLApp.Sheets("Sheet1").Select()
If ID = MyXLApp.Cells(x, 1).Value Then
If MyXLApp.Cells(x, 2).Value = "Keep" Then
'Do Stuff,manipulate data, play sound etc

ElseIf MyXLApp.Cells(x, 2).Value = "Cull" Then
'Do Other Stuff,manipulate data, play sound etc

Else
'Do Other Stuff,manipulate data, play sound etc


End If
Exit For
End If
Else
Dim sound As New ClsSound
sound.PlayWav("C:\Program Files\KeepCull\Error.wav")
End If
Next x

FIND code..... it don't wanna work.


Dim NoSample As Integer
Dim Barcode As String
Dim firstAddress As MyXLApp.Range
Dim Action1 as String
Barcode = "Cat"
'Barcode usually user-defined via Form

NoSample = 10
'NoSample usually user-defined via Form
With MyXLApp.Range(Cells(1, 1), Cells(NoSample, 1))

Set c = .Find(Barcode, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
MyXLApp.Range(firstAddress).Select()
MyXLApp.ActiveCell.Offset(0, 1).Select()
Action1 = MyXLApp.ActiveCell.Value
If Action1 = "Cull" Then
'Do stuff, manipulate data, play sound etc
ElseIf Action1 = "Keep" Then
'Do other stuff
Else 'Do other stuff
EndIf
Else
End If
End With

I hope this is all clear.... first time at using the forum.

Thanks in advance kind peoples :rolleyes:

yui79

I'm no .Net expert, but from gossip I've overheard, 'Set x = Object' statements are replaced by 'x = Object' in .Net.

Check out the 'VB.Net Office Integration FAQ' at the top of this board...

Hi Timbo,

Thanks for the reply.... I should have removed the Set from the 'Set x=obj' code, thankfully, .Net actually does this for you if you enter the code (as I did) directly from VBA.

Sadly, this does not fix the problems (ie the LookIn part).....

Cheers anyway

yui79

You need to declare c as a range object and firstAddress should be a string object rather than a range object

Well, declaring c as Range helped.... thanks,

Any ideas where I have gone wrong in the syntax for the following bit?

With MyXLApp.Range(Cells(1, 1), Cells(NoSample, 1))

Cells is an Excel property and therefore needs to be fully referenced. Try this:
With MyXLApp.Range(MyXLApp.Cells(1, 1), MyXLApp.Cells(NoSample, 1))

In addition to making these fixes to get your code actually running properly, the last issue you'll have to consider is having your App close and to be able to close Excel. That is, I believe if you look in your Task Manager, you are almost certainly spawning hanging copies of Excel, running in the background.

This is a lot to throw at a beginner, but you should have a look at the VB.Net Office Integration Tutorial (http://www.visualbasicforum.com/t129690.html) which emphasizes that one must be extremely explicit about the creation and disposal of your COM objects. The first thing that you will have to abandon is the use of the With .. End With construct. :(

Sorry.

Automation in .Net is quite tricky. :(

-- Mike