Use Excel.WorksheetFunction.Vlookup from Access VBA (AC2007)

[AOB]

Hi guys,

I'm trying to use the Excel VLookup function from within Access to look up a value in a worksheet to update a record in a recordset. Here is the code :

Dim appExcel As Object
Set appExcel = CreateObject("Excel.Application")
blnError = IsError([COLOR=red]appExcel.WorksheetFunction.Vlookup(rst.Fields("myField"), objRange.Address, objRange.Columns.Count, False)[/COLOR])

I get the following error :

Unable to get the VLookup property of the WorksheetFunction class

Can anybody suggest why? objRange is correctly assigned to a range object within the opened worksheet (I can see the assigned address in the debugger) and the field value from the rst recordset is present in the lookup range. It seems Access VBA doesn't recognise .VLookup under .WorksheetFunction but why?

Thanks!

AOB

 

[AOB]

Figured it out - you have to send the whole range object as the argument for the table array (not just the address)

appExcel.WorksheetFunction.Vlookup(rst.Fields("myField"), [COLOR=red]objRange[/COLOR], objRange.Columns.Count, False)