How do I lookup data in another workbook?

[Stapes]

Hi

I have one workbook which is a template for Order Forms and another
that is the Product Listing. I am creating a userform in the Order
Form.xls. When a Part Number is entered on the Order Form, I want to
open Product Listing.xls and look up various fields on that record.
I have got as far as opening the file:

Workbooks.Open ("C:\Documents and Settings\stephen\Desktop\AC Global
\AC Global Complete Product Listing.xls"). I have been looking at
VLookup, but that doesn't have a parameter for a different workbook
name.

How do I do that?

Stapes

 

[Dave Peterson]

The lookup range can be a range in another workbook.

Is there a reason you just don't plop a formula into a cell. Then you don't
even need to open the "sending" workbook.

If you decide to go with a formula, you can build the formula with that
"sending" workbook open and then close that file and you'll see the syntax that
you have to use.

But if you absolutely want to use VBA:

Dim myRng as range
dim res as variant
dim myVal as Variant 'string, long???
set myrng = workbooks.open("C:\yourfilename.xls") _
.worksheets("sheetnamehere").range("a:b")

myVal = "whatever"
res = application.vlookup(myval, myrng, 2, false)
if iserror(res) then
msgbox "not found"
else
msgbox res
end if

myrng.parent.parent.close savechanges:=false