How do I lookup data in another workbook?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Stapes - 10 Dec 2007 11:59 GMT

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

Reply to this Message

Dave Peterson - 10 Dec 2007 12:14 GMT

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

> Hi
>
[quoted text clipped - 12 lines]
>
> Stapes

Signature

Dave Peterson

Reply to this Message

Rate this thread: