VLOOKUP into another workbook

[L Mehl]

Hello --

Can someone show me the format for using VLOOKUP in VBA to get a value from
an xls in another folder?

In a worksheet
=VLOOKUP(1,TA_TestScript.xls!TestCases,2,FALSE)

TA_TestScript.xls is the closed file

works, but I can't figure out the correct combination of [ ], " ", etc. to
make it reference an xls in another folder, using VBA.

Thanks for any help.

Larry Mehl

 

[Bob Phillips]

Hi Larry,

=VLOOKUP("Chelsea",'D:\Bob\My Documents\My Spreadsheets\[Premiership
2003.xls]League Table'!$B$3:$Z$22,25,FALSE)

'D:\Bob\My Documents\My Spreadsheets\[Premiership 2003.xls] is the workbook

League Table is the sheet
--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[L Mehl]

Bob --

Thanks.

Is that code for use in VBA to get a value from an xls in another folder?

I get the error
"Expected: Expression"
when I adapt it to my scenario.

Thanks for any help.

Larry Mehl

Hi Larry,

=VLOOKUP("Chelsea",'D:\Bob\My Documents\My Spreadsheets\[Premiership
2003.xls]League Table'!$B$3:$Z$22,25,FALSE)

'D:\Bob\My Documents\My Spreadsheets\[Premiership 2003.xls] is the workbook

League Table is the sheet
--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Hello --

Can someone show me the format for using VLOOKUP in VBA to get a value from
an xls in another folder?

In a worksheet
=VLOOKUP(1,TA_TestScript.xls!TestCases,2,FALSE)

TA_TestScript.xls is the closed file

works, but I can't figure out the correct combination of [ ], " ", etc. to
make it reference an xls in another folder, using VBA.

Thanks for any help.

Larry Mehl

 

[Bob Phillips]

Larry,

No it is worksheet formula. It is exactly as I created on my system and did
work.

Post your adapted version and let's see if we can work out what it should
be.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Bob --

Thanks.

Is that code for use in VBA to get a value from an xls in another folder?

I get the error
"Expected: Expression"
when I adapt it to my scenario.

Thanks for any help.

Larry Mehl

Hi Larry,

=VLOOKUP("Chelsea",'D:\Bob\My Documents\My Spreadsheets\[Premiership
2003.xls]League Table'!$B$3:$Z$22,25,FALSE)

'D:\Bob\My Documents\My Spreadsheets\[Premiership 2003.xls] is the workbook

League Table is the sheet
--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Hello --

Can someone show me the format for using VLOOKUP in VBA to get a value from
an xls in another folder?

In a worksheet
=VLOOKUP(1,TA_TestScript.xls!TestCases,2,FALSE)

TA_TestScript.xls is the closed file

works, but I can't figure out the correct combination of [ ], " ",

etc.

to

 

[L Mehl]

Hi Bob,

Thanks for the offer of help.
The result of building the lookup in workbook on c:
(in part, by pointing to the data lookup range in the other workbook)
is
=VLOOKUP(1,'C:\...path...\TA_TestScript.xls'!TestCaseInfo,3,FALSE)

Using this in VBA as
variable =
Application.WorksheetFunction.VLOOKUP(1,'C:\...path...\TA_TestScript.xls'!Te
stCaseInfo,3,FALSE)
gives the error
"Expected: Expression"

Is this enough for you to comment on?

Larry

Larry,

No it is worksheet formula. It is exactly as I created on my system and did
work.

Post your adapted version and let's see if we can work out what it should
be.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Bob --

Thanks.

Is that code for use in VBA to get a value from an xls in another folder?

I get the error
"Expected: Expression"
when I adapt it to my scenario.

Thanks for any help.

Larry Mehl

Hi Larry,

=VLOOKUP("Chelsea",'D:\Bob\My Documents\My Spreadsheets\[Premiership
2003.xls]League Table'!$B$3:$Z$22,25,FALSE)

'D:\Bob\My Documents\My Spreadsheets\[Premiership 2003.xls] is the workbook

League Table is the sheet
--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Hello --

Can someone show me the format for using VLOOKUP in VBA to get a value
from
an xls in another folder?

In a worksheet
=VLOOKUP(1,TA_TestScript.xls!TestCases,2,FALSE)

TA_TestScript.xls is the closed file

works, but I can't figure out the correct combination of [ ], " ",

etc.

to
make it reference an xls in another folder, using VBA.

Thanks for any help.

Larry Mehl

 

[Frank Kabel]

Hi Larry
try changing
=VLOOKUP(1,'C:\...path...\TA_TestScript.xls'!TestCaseInfo,3,FALSE)
to
=VLOOKUP(1,'C:\...path...\[TA_TestScript.xls]'!TestCaseInfo,3,FALSE)
the [...] is required

Frank

Hi Bob,

Thanks for the offer of help.
The result of building the lookup in workbook on c:
(in part, by pointing to the data lookup range in the other workbook)
is
=VLOOKUP(1,'C:\...path...\TA_TestScript.xls'!TestCaseInfo,3,FALSE)

Using this in VBA as
variable =
Application.WorksheetFunction.VLOOKUP(1,'C:\...path...\TA_TestScript.xl
s'!Te
stCaseInfo,3,FALSE)
gives the error
"Expected: Expression"

Is this enough for you to comment on?

Larry

Larry,

No it is worksheet formula. It is exactly as I created on my system
and did work.

Post your adapted version and let's see if we can work out what it
should be.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Bob --

Thanks.

Is that code for use in VBA to get a value from an xls in another
folder?

I get the error
"Expected: Expression"
when I adapt it to my scenario.

Thanks for any help.

Larry Mehl


Hi Larry,

=VLOOKUP("Chelsea",'D:\Bob\My Documents\My
Spreadsheets\[Premiership 2003.xls]League
Table'!$B$3:$Z$22,25,FALSE)

'D:\Bob\My Documents\My Spreadsheets\[Premiership 2003.xls] is the
workbook

League Table is the sheet
--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Hello --

Can someone show me the format for using VLOOKUP in VBA to get a
value from an xls in another folder?

In a worksheet
=VLOOKUP(1,TA_TestScript.xls!TestCases,2,FALSE)

TA_TestScript.xls is the closed file

works, but I can't figure out the correct combination of [ ], "
", etc. to make it reference an xls in another folder, using VBA.

Thanks for any help.

Larry Mehl

 

[L Mehl]

Hi Frank --

I remember seeing brackets in an example, and then forgot where I saw it.

It still does not work for me.

That notation works in a cell in the xls.

My problem is how to achieve the same result VBA code.

I "duplicated" your notation into VBA, and improvised a little:
Application.WorksheetFunction.VLookup _
(1,
Worksheets("C:\...path...\[TA_TestScript.xls]TestCases").Range("TestCaseInfo
"), 2, 0)

Running in the Immediate Window, I got the error
"Run-time error '9':
Subscript out of range"

Are the " marks in the right place?
Is "Range" needed?
Do I need "Worksheets"?

[ ] are as you described them.

Many combinations to try ...

Thanks in advance for any further help.

Larry

Hi Larry
try changing
=VLOOKUP(1,'C:\...path...\TA_TestScript.xls'!TestCaseInfo,3,FALSE)
to
=VLOOKUP(1,'C:\...path...\[TA_TestScript.xls]'!TestCaseInfo,3,FALSE)
the [...] is required

Frank

Hi Bob,

Thanks for the offer of help.
The result of building the lookup in workbook on c:
(in part, by pointing to the data lookup range in the other workbook)
is
=VLOOKUP(1,'C:\...path...\TA_TestScript.xls'!TestCaseInfo,3,FALSE)

Using this in VBA as
variable =
Application.WorksheetFunction.VLOOKUP(1,'C:\...path...\TA_TestScript.xl
s'!Te
stCaseInfo,3,FALSE)
gives the error
"Expected: Expression"

Is this enough for you to comment on?

Larry

Larry,

No it is worksheet formula. It is exactly as I created on my system
and did work.

Post your adapted version and let's see if we can work out what it
should be.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Bob --

Thanks.

Is that code for use in VBA to get a value from an xls in another
folder?

I get the error
"Expected: Expression"
when I adapt it to my scenario.

Thanks for any help.

Larry Mehl


Hi Larry,

=VLOOKUP("Chelsea",'D:\Bob\My Documents\My
Spreadsheets\[Premiership 2003.xls]League
Table'!$B$3:$Z$22,25,FALSE)

'D:\Bob\My Documents\My Spreadsheets\[Premiership 2003.xls] is the
workbook

League Table is the sheet
--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Hello --

Can someone show me the format for using VLOOKUP in VBA to get a
value from an xls in another folder?

In a worksheet
=VLOOKUP(1,TA_TestScript.xls!TestCases,2,FALSE)

TA_TestScript.xls is the closed file

works, but I can't figure out the correct combination of [ ], "
", etc. to make it reference an xls in another folder, using VBA.

Thanks for any help.

Larry Mehl