Excel VLookup in VB6

[skvabbili]

I am getting "The remote server machine does not exist or is unavailable"

I am trying to access the VLookup function in my VB code (running as a COM+ component) to loopkup a range in excel file and update some data to database. The current sheet is Sheet1 and the range SRng is on Sheet2

Set xlMyApp = CreateObject("Excel.Application")
xlMyApp.Workbooks.Open ("BudFile.xls")
xlMyApp.Workbooks(1).Application.DisplayAlerts = False
.....
.....
Set rngSp = xlMyApp.Workbooks(1).Worksheets("Sheet2").Range("SRng")

lsSpID = Application.VLookup(Trim(xlCell.Value), rngSp, 2, False)
If IsError(lsSuppID) Or Trim(CStr(lsSpID)) = "" Then
'.....do someting
Else
'....do something else
End If

Any solutions please.......

[si_the_geek]

Welcome to VBForums! :wave:

The problem is that you are using Application, rather than your applciation object variable (xlMyApp).

A couple of other things.. using "xlMyApp.Workbooks(1).Application.DisplayAlerts" is a bit redundant - as .Application refers back to xlMyApp (ie: you could just do "xlMyApp.DisplayAlerts").

Also, you are not using a variable for the Workbook, which is recommended (depending on settings, it is possible that your CreateObject wont create a new instance, but work with an existing one that already has books open). You can see examples of how to do this in my tutorial (link in my signature).

[skvabbili]

Thank you... :wave:

I tried using myXlApp.WorksheetFunction.VLookup but could not get to it. It throws an error.

Is this function available elsewhere?

Would myXlApp.Workbooks(1).Application.VLookup() give the same problem

Could u please provide me a code snippet for the scenario.

[si_the_geek]

I'm sure I've seen it done before, but I cant find the post :(

The closest I could find was this alternative:
http://www.vbforums.com/showthread.php?t=376315