using VLOOKUP from MS Access

[Guest]

I'm openning an excel workbook with many spreadsheets from MS Access by using
the following code:

Dim xlapp As New Excel.Application
Dim xlbook As New Excel.Workbook

Set xlbook = xlapp.Workbooks.Open(strFileName, True)

How could I use VLOOKUP to find a needed number from Sheet1 in this workbook?

Thanks

 

[Guest]

In excel you can use

Application.vlookup(What, Where, 1, False)

So I would think that you could try

xlbook.vlookup.(What, Where, 1, False)

Let me know if that works...

 

[Dave Peterson]

This worked in MSWord with a reference to MSExcel.

Option Explicit
Sub testme()

Dim xlApp As Excel.Application
Dim xlBook As New Excel.Workbook
Dim strFileName As String
Dim res As Variant
Dim myRng As Excel.Range
Dim myVal As Variant

strFileName = "C:\my documents\excel\book1.xls"

myVal = "asdf3"

Set xlApp = New Excel.Application
xlApp.Visible = True 'nice for testing

Set xlBook = xlApp.Workbooks.Open(strFileName, True)
Set myRng = xlBook.Worksheets("Sheet1").Range("a1:E99")

res = xlApp.VLookup(myVal, myRng, 2, False)

If IsError(res) Then
MsgBox "No match"
Else
MsgBox res
End If

xlBook.Close savechanges:=False
xlApp.Quit

Set myRng = Nothing
Set xlBook = Nothing
Set xlApp = Nothing

End Sub

 

[Guest]

Thanks a lot, Jim.

I'm trying this after the workbook's openning but may be something wrong
with how I'm using the path to the file or a range. So, it's not working.

With xlbook

curCost = .WorksheetFunction.VLookup(strRecipeNum,
"'S:\Operations\Products\Recipes\[MRP-Recipes.xls]'!A1:C400", 12, False)

End With

 

[Guest]

Thanks a lot, Dave.

The code goes without any error messages. But, just my vlookup function
cannot return any value. It returns error 2042.

It looks like i'm doing the same, but .....

Set xlbook = xlApp.Workbooks.Open(strFileName, True)

Set myRng = xlbook.Worksheets("Legend").Range("C3:L500")

'With xlbook
' .Worksheets("Legend").Range("C3:L500").Name = "d"
'End With ' I've checked, just in case, whether the range can be set up there.

varCost = xlApp.VLookup(strVal, myRng, 12, False)

I think, there is some simple mistake, but I cannot see it.

 

[Guest]

I found a mistake.

If I'm using the range such as C3:L500, I should use 10 for column (not 12).

Thanks everybody.