Using variable in a vlookup formula in vb.net 1

[july07]

Hello,

I am trying to vlookup employees name in a excel file from vb.net, using a variable in the vlookup formula and store the name is another variable. I have tried the below code but it does not seem to work because of the variable in the vlookup formula.

  xlApp = New Excel.Application
  xlWorkBook = xlApp.Workbooks.Open("C:\LookupTable")
  xlWorkSheet = xlWorkBook.Worksheets("employee_ID")
  Dim EmployeeID = textbox1.text
  Dim Name

  Name = xlApp.WorksheetFunction.VLookup(" & EmployeeID & ", xlWorkSheet.Range("$A$1:G454"), 7, False)

    MsgBox(Name)

The EmployeeID is always a number which changes depending on the text in the textbox. Can somebody help me please? It seems like the variable "EmployeeID" is not reflecting as a number.

Any help will be appreciated.

Thanks.

 

[Andrzejek]

Did you try:

  xlApp = New Excel.Application
  xlWorkBook = xlApp.Workbooks.Open("C:\LookupTable")
  xlWorkSheet = xlWorkBook.Worksheets("employee_ID")[green]
  'Dim EmployeeID = textbox1.text[/green]
  Dim Name

  Name = xlApp.WorksheetFunction.VLookup(" & [blue]textbox1.text[/blue] & ", xlWorkSheet.Range("$A$1:G454"), 7, False)

    MsgBox(Name)

BTW, Name is very bad idea for variable. it is a reserved word in VB

Have fun.

---- Andy

 

[july07]

Thanks Andy, the "name" was just an example, not what is in my actual code.

Another question what if the EmployeeID is from a dataset. for example:

Dim EmployeeID
Dim LstName
xlApp = New Excel.Application  
xlWorkBook = xlApp.Workbooks.Open("C:\LookupTable")  
xlWorkSheet = xlWorkBook.Worksheets("employee_ID")  

Dim I As Integer
For I = 1 To ds.Tables(0).Rows.Count - 1 

      EmployeeID = ds.Tables(0).Rows(I).Item(7)
      LstName = xlApp.WorksheetFunction.VLookup(" & [blue]EmployeeID[/blue]& ", xlWorkSheet.Range("$A$1:G454"), 7, False)

MsgBox (LstName)

Next

 

[SkipVought]

What happens with your code?

BTW,

    LstName = xlApp.WorksheetFunction.VLookup(EmployeeID, xlWorksheet.Range("$A$1:G454"), 7, False)

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[july07]

I am getting the same error with my code and yours, the error is:

" COMException was unhandled
VLookup method of WorksheetFunction class failed"

Thanks.

 

[SkipVought]

Do you have a reference set to the appropriate version Excel Object Library?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[july07]

Yes i do. if i put an actual employeeID ( for example 12568)
i know exist, in the vlookup formula instead of "EmployeeID" it works. it just doesnt work for the varaiable.

Thanks.

 

[SkipVought]

What is the actual value of your variable?

Is it NUMERIC or TEXT? It makes a BIG difference!

It APPEARS, that you are entering a NUMBER (for example 12568) and that is apparently what is stored in your Excel lookup range.

So try this...

    LstName = xlApp.WorksheetFunction.VLookup(CLNG(EmployeeID), xlWorksheet.Range("$A$1:G454"), 7, False)

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[july07]

The actual value for the varaible is numerical.

I tried your code and it works. Thank you so much.

What if the variable is a text, what will change?

Thanks.

 

[SkipVought]

The actual value for the varaible is numerical.

REALLY????
If you print the variable to the debug window, post EXACTLY what prints.

I tried your code and it works.

Well, what that tells me is that your TEXT is CONVERTED to a NUMBER, and THAT works.

Your db value is TEXT.


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[july07]

sorry for the wrong information. The variable is TEXT, i assumed it was numerical since the ID were all number.

Thanks again Skip.

 

[SkipVought]

IMNSHO, it is preferable for IDENTIFIERS to be strings, even if they contain only numeric characters, as very often, leading zeros are significant data. Of course, leading zeros are meaningless for numeric data.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]