Anyone know why the following code returns 'sub or
function not defined' and highlights Vlookup, am I missing
a reference?
Thanks for any help.

Dim Ix As String
Ix = VLookup(c21, "C:\Documents and Settings\Jill\My
Documents\[EmailNames.xls]sheet1!a1:b20", 2, False)
MsgBox Ix
End Sub

Hi
try:
Ix = application.evaluate("=VLookup(c21, 'C:\Documents and
Settings\Jill\My
Documents\[EmailNames.xls]sheet1'!a1:b20, 2, 0)")

--
Regards
Frank Kabel
Frankfurt, Germany

"Jill" schrieb im Newsbeitrag
...
Anyone know why the following code returns 'sub or
function not defined' and highlights Vlookup, am I missing
a reference?
Thanks for any help.

Dim Ix As String
Ix = VLookup(c21, "C:\Documents and Settings\Jill\My
Documents\[EmailNames.xls]sheet1!a1:b20", 2, False)
MsgBox Ix
End Sub

Hi Jill,

You need to use either:

Application.VLookup

or:

Application.WorksheetFunction.VLookup

The first will return an error value if the lookup fails, the second will
generate a VBA error if the lookup fails.

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *


"Jill" wrote in message
...
Anyone know why the following code returns 'sub or
function not defined' and highlights Vlookup, am I missing
a reference?
Thanks for any help.

Dim Ix As String
Ix = VLookup(c21, "C:\Documents and Settings\Jill\My
Documents\[EmailNames.xls]sheet1!a1:b20", 2, False)
MsgBox Ix
End Sub

Hi Frank
Tried your suggestion this returns a 'type mismatch' error
any other suggestions.
thanks Jill
-----Original Message-----
Hi
try:
Ix = application.evaluate("=VLookup(c21, 'C:\Documents and
Settings\Jill\My
Documents\[EmailNames.xls]sheet1'!a1:b20, 2, 0)")

--
Regards
Frank Kabel
Frankfurt, Germany

"Jill" schrieb im
Newsbeitrag
...
Anyone know why the following code returns 'sub or
function not defined' and highlights Vlookup, am I
missing
a reference?
Thanks for any help.

Dim Ix As String
Ix = VLookup(c21, "C:\Documents and Settings\Jill\My
Documents\[EmailNames.xls]sheet1!a1:b20", 2, False)
MsgBox Ix
End Sub

.

Hi
how is ly defined?

--
Regards
Frank Kabel
Frankfurt, Germany

"Jill" schrieb im Newsbeitrag
...
Hi Frank
Tried your suggestion this returns a 'type mismatch' error
any other suggestions.
thanks Jill
-----Original Message-----
Hi
try:
Ix = application.evaluate("=VLookup(c21, 'C:\Documents and
Settings\Jill\My
Documents\[EmailNames.xls]sheet1'!a1:b20, 2, 0)")

--
Regards
Frank Kabel
Frankfurt, Germany

"Jill" schrieb im
Newsbeitrag
...
Anyone know why the following code returns 'sub or
function not defined' and highlights Vlookup, am I
missing
a reference?
Thanks for any help.

Dim Ix As String
Ix = VLookup(c21, "C:\Documents and Settings\Jill\My
Documents\[EmailNames.xls]sheet1!a1:b20", 2, False)
MsgBox Ix
End Sub

.

Essentially Vlookup or any other worksheet function used in VBA or used in
VBA with Evaluate will not work with a closed workbook such as you show.
John Walkenbach does document one method that works which uses
ExecuteExcel4Macro, however, this is very slow. It is often easier/faster
to write you formula in a cell, collect the result, clear the cell.

http://j-walk.com/ss/excel/tips/tip82.htm
A VBA Function to Get a Value From a Closed File

--
Regards,
Tom Ogilvy


"Jill" wrote in message
...
Hi Frank
Tried your suggestion this returns a 'type mismatch' error
any other suggestions.
thanks Jill
-----Original Message-----
Hi
try:
Ix = application.evaluate("=VLookup(c21, 'C:\Documents and
Settings\Jill\My
Documents\[EmailNames.xls]sheet1'!a1:b20, 2, 0)")

--
Regards
Frank Kabel
Frankfurt, Germany

"Jill" schrieb im
Newsbeitrag
...
Anyone know why the following code returns 'sub or
function not defined' and highlights Vlookup, am I
missing
a reference?
Thanks for any help.

Dim Ix As String
Ix = VLookup(c21, "C:\Documents and Settings\Jill\My
Documents\[EmailNames.xls]sheet1!a1:b20", 2, False)
MsgBox Ix
End Sub

.

I guess I should add that Harlan Grove also has a method that opens another
verion of Excel, opens the file and extracts the information, but that
really isn't working on a closed workbook. It is primarily posted as a UDF
method for functions used in worksheets to replace use of the indirect
worksheet function.

Also, Andy Wiggins has a method using sql.request that is supposed to work
and if the data is in a table, it can be accessed using ADO or perhaps the
older DAO.

I believe Frank can post a reference to previous posts on those topics.

--
Regards,
Tom Ogilvy



"Tom Ogilvy" wrote in message
...
Essentially Vlookup or any other worksheet function used in VBA or used in
VBA with Evaluate will not work with a closed workbook such as you show.
John Walkenbach does document one method that works which uses
ExecuteExcel4Macro, however, this is very slow. It is often easier/faster
to write you formula in a cell, collect the result, clear the cell.

http://j-walk.com/ss/excel/tips/tip82.htm
A VBA Function to Get a Value From a Closed File

--
Regards,
Tom Ogilvy


"Jill" wrote in message
...
Hi Frank
Tried your suggestion this returns a 'type mismatch' error
any other suggestions.
thanks Jill
-----Original Message-----
Hi
try:
Ix = application.evaluate("=VLookup(c21, 'C:\Documents and
Settings\Jill\My
Documents\[EmailNames.xls]sheet1'!a1:b20, 2, 0)")

--
Regards
Frank Kabel
Frankfurt, Germany

"Jill" schrieb im
Newsbeitrag
...
Anyone know why the following code returns 'sub or
function not defined' and highlights Vlookup, am I
missing
a reference?
Thanks for any help.

Dim Ix As String
Ix = VLookup(c21, "C:\Documents and Settings\Jill\My
Documents\[EmailNames.xls]sheet1!a1:b20", 2, False)
MsgBox Ix
End Sub

.

[...]
Also, Andy Wiggins has a method using sql.request that is supposed to
work
and if the data is in a table, it can be accessed using ADO or
perhaps the
older DAO.

I believe Frank can post a reference to previous posts on those
topics.

here we go:
http://tinyurl.com/2c62u

Frank

Hi Jill,

I have seen this when the column is of a different data
type (i.e. Number vs Text)...

-----Original Message-----
Anyone know why the following code returns 'sub or
function not defined' and highlights Vlookup, am I
missing
a reference?
Thanks for any help.

Dim Ix As String
Ix = VLookup(c21, "C:\Documents and Settings\Jill\My
Documents\[EmailNames.xls]sheet1!a1:b20", 2, False)
MsgBox Ix
End Sub
.

Before Harlan drops by...

His UDF opens another instance of excel, but retrieves that value from a still
closed workbook.

(Yeah, I got corrected.)

Tom Ogilvy wrote:

I guess I should add that Harlan Grove also has a method that opens another
verion of Excel, opens the file and extracts the information, but that
really isn't working on a closed workbook. It is primarily posted as a UDF
method for functions used in worksheets to replace use of the indirect
worksheet function.

Also, Andy Wiggins has a method using sql.request that is supposed to work
and if the data is in a table, it can be accessed using ADO or perhaps the
older DAO.

I believe Frank can post a reference to previous posts on those topics.

--
Regards,
Tom Ogilvy

"Tom Ogilvy" wrote in message
...
Essentially Vlookup or any other worksheet function used in VBA or used in
VBA with Evaluate will not work with a closed workbook such as you show.
John Walkenbach does document one method that works which uses
ExecuteExcel4Macro, however, this is very slow. It is often easier/faster
to write you formula in a cell, collect the result, clear the cell.

http://j-walk.com/ss/excel/tips/tip82.htm
A VBA Function to Get a Value From a Closed File

--
Regards,
Tom Ogilvy


"Jill" wrote in message
...
Hi Frank
Tried your suggestion this returns a 'type mismatch' error
any other suggestions.
thanks Jill
-----Original Message-----
Hi
try:
Ix = application.evaluate("=VLookup(c21, 'C:\Documents and
Settings\Jill\My
Documents\[EmailNames.xls]sheet1'!a1:b20, 2, 0)")

--
Regards
Frank Kabel
Frankfurt, Germany

"Jill" schrieb im
Newsbeitrag
...
Anyone know why the following code returns 'sub or
function not defined' and highlights Vlookup, am I
missing
a reference?
Thanks for any help.

Dim Ix As String
Ix = VLookup(c21, "C:\Documents and Settings\Jill\My
Documents\[EmailNames.xls]sheet1!a1:b20", 2, False)
MsgBox Ix
End Sub

.




--

Dave Peterson

Thanks - guess I haven't looked at it in a while - so I guess he puts a hard
coded reference to the closed workbook in the new instance of excel, gets
the value returned, then closes everything. (and does it invisibly of
course).

--
Regards,
Tom Ogilvy

"Dave Peterson" wrote in message
...
Before Harlan drops by...

His UDF opens another instance of excel, but retrieves that value from a
still
closed workbook.

(Yeah, I got corrected.)

Tom Ogilvy wrote:

I guess I should add that Harlan Grove also has a method that opens
another
verion of Excel, opens the file and extracts the information, but that
really isn't working on a closed workbook. It is primarily posted as a
UDF
method for functions used in worksheets to replace use of the indirect
worksheet function.

Also, Andy Wiggins has a method using sql.request that is supposed to
work
and if the data is in a table, it can be accessed using ADO or perhaps
the
older DAO.

I believe Frank can post a reference to previous posts on those topics.

--
Regards,
Tom Ogilvy

"Tom Ogilvy" wrote in message
...
Essentially Vlookup or any other worksheet function used in VBA or
used in
VBA with Evaluate will not work with a closed workbook such as you
show.
John Walkenbach does document one method that works which uses
ExecuteExcel4Macro, however, this is very slow. It is often
easier/faster
to write you formula in a cell, collect the result, clear the cell.

http://j-walk.com/ss/excel/tips/tip82.htm
A VBA Function to Get a Value From a Closed File

--
Regards,
Tom Ogilvy


"Jill" wrote in message
...
Hi Frank
Tried your suggestion this returns a 'type mismatch' error
any other suggestions.
thanks Jill
-----Original Message-----
Hi
try:
Ix = application.evaluate("=VLookup(c21, 'C:\Documents and
Settings\Jill\My
Documents\[EmailNames.xls]sheet1'!a1:b20, 2, 0)")

--
Regards
Frank Kabel
Frankfurt, Germany

"Jill" schrieb im
Newsbeitrag
...
Anyone know why the following code returns 'sub or
function not defined' and highlights Vlookup, am I
missing
a reference?
Thanks for any help.

Dim Ix As String
Ix = VLookup(c21, "C:\Documents and Settings\Jill\My
Documents\[EmailNames.xls]sheet1!a1:b20", 2, False)
MsgBox Ix
End Sub

.




--

Dave Peterson

I have been able to correct the problem by placing the
reference table on the sheet that calls the macro (an open
table) the procedure now works perfectly.
Many thanks to all who have contributed
Jill
-----Original Message-----
[...]
Also, Andy Wiggins has a method using sql.request that
is supposed to
work
and if the data is in a table, it can be accessed using
ADO or
perhaps the
older DAO.

I believe Frank can post a reference to previous posts
on those
topics.

here we go:
http://tinyurl.com/2c62u

Frank

.