Excel VLookup in VB6

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.......

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).

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.

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