I found a mistake.

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

Thanks everybody.



"Alex" wrote:

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.

"Dave Peterson" wrote:

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

Alex wrote:

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

--

Dave Peterson