Can Application.Vlookup access a range in a file which is not open?

[Walter Briscoe]

I run Excel 2003 (11.5612.5606)
I have a file from which I use VLOOKUP to grab single pieces of data.

I use code something like this:
Const stations = "'C:\path value\[stations.xls]Sheet1'!R1C1:R280C38"

Private Sub ReadStationDatum(t As Range,Stashn As String,Col As Integer)
t.FormulaR1C1 = _
"=VLOOKUP(""" & Stashn & """, " & stations & "," & Col & ",FALSE)"
t.Value = t.FormulaR1C1
End Sub

I don't like this code as it writes twice to a cell.

I came up with a half-way house which also works using the following:

Dim stat As Range

Private Sub ReadStationDatum(t As Range,Stashn As String,Col As Integer)
t.Value = Application.VLookup(Stashn, stat, Col, False)
End Sub

Public Sub TopLevelSubroutine()
Dim here As String

here = ActiveWorkbook.name
Workbooks.Open Filename:="C:\path value\\LU\stations.xls"
Set stat = Range("A1:AL280")
Windows(here).Activate
...
End Sub

I don't like that solution because it explicitly opens my lookup file
and adds to code complexity but the following gets a 2015 error as I
feed a string representing a range rather than a range to
Application.VLookup

Const stations = "'C:\path value\[stations.xls]Sheet1'!R1C1:R280C38"

Private Sub ReadStationDatum(t As Range,Stashn As String,Col As Integer)
t.Value = Application.VLookup(Stashn, stations, Col, False)
End Sub

Range(stations) results in an error 1004.

Is it possible to do the conversion directly?

 

[Per Jessen]

Hi

I would do it like this:

Private Sub ReadStationDatum(t As Range, Stashn As String, col As Integer)
Application.ScreenUpdating = False
Set wb = Workbooks.Open(Filename = "C:\path value\\LU\stations.xls")
Set sh = wb.Sheets("Sheet1")
Set stations = sh.Range("A1:AL280")
t.Value = Application.VLookup(Stashn, stations, col, False)
wb.Close
Application.ScreenUpdating = True
End Sub

Regards,
Per

 

[Dave Peterson]

If you want to use =vlookup() in your code, you'll have to open the sending
workbook.

If I had a few cells that need to be populated just once, I'd build the formula
strings, plop them into the cells, calculate, and convert to values.

If I had a larger number of cells, I'd open the sending workbook, then build the
formula strings, fill the ranges with the formulas, convert to values, and close
the sending workbook.

=====
But you'd have to use a real range:
dim Stations as range
'open stations.xls
set stations = workbooks("stations.xls").worksheets("Sheet1").range("a1:AL280")

You could use this syntax, but I find it harder to read:
set stations = application.range("'[stations.xls]sheet1'!a1:al280")
(no drive, no path--and stations.xls needs to be open)

I run Excel 2003 (11.5612.5606)
I have a file from which I use VLOOKUP to grab single pieces of data.

I use code something like this:
Const stations = "'C:\path value\[stations.xls]Sheet1'!R1C1:R280C38"

Private Sub ReadStationDatum(t As Range,Stashn As String,Col As Integer)
t.FormulaR1C1 = _
"=VLOOKUP(""" & Stashn & """, " & stations & "," & Col & ",FALSE)"
t.Value = t.FormulaR1C1
End Sub

I don't like this code as it writes twice to a cell.

I came up with a half-way house which also works using the following:

Dim stat As Range

Private Sub ReadStationDatum(t As Range,Stashn As String,Col As Integer)
t.Value = Application.VLookup(Stashn, stat, Col, False)
End Sub

Public Sub TopLevelSubroutine()
Dim here As String

here = ActiveWorkbook.name
Workbooks.Open Filename:="C:\path value\\LU\stations.xls"
Set stat = Range("A1:AL280")
Windows(here).Activate
...
End Sub

I don't like that solution because it explicitly opens my lookup file
and adds to code complexity but the following gets a 2015 error as I
feed a string representing a range rather than a range to
Application.VLookup

Const stations = "'C:\path value\[stations.xls]Sheet1'!R1C1:R280C38"

Private Sub ReadStationDatum(t As Range,Stashn As String,Col As Integer)
t.Value = Application.VLookup(Stashn, stations, Col, False)
End Sub

Range(stations) results in an error 1004.

Is it possible to do the conversion directly?

 

[Walter Briscoe]

In message <[email protected]> of Fri, 20 Mar 2009
08:05:58 in microsoft.public.excel.programming, Dave Peterson

If you want to use =vlookup() in your code, you'll have to open the sending
workbook.

I find it strange that a workbook can get data from a sending workbook
without that workbook being open but similar functionality is not
available from VBA.

If I had a few cells that need to be populated just once, I'd build the formula
strings, plop them into the cells, calculate, and convert to values.

That is what I first did.

If I had a larger number of cells, I'd open the sending workbook, then
build the
formula strings, fill the ranges with the formulas, convert to values,
and close
the sending workbook.

I did that as an exercise to learn how to make it work.

=====
But you'd have to use a real range:
dim Stations as range
'open stations.xls
set stations = workbooks("stations.xls").worksheets("Sheet1").range("a1:AL280")

You could use this syntax, but I find it harder to read:
set stations = application.range("'[stations.xls]sheet1'!a1:al280")
(no drive, no path--and stations.xls needs to be open)

That is so useful! I found such code did not work with a drive and path.
Are you able to point at any specification documents to confirm this?
(My Excel version is 2003 (11.5612.5606))

[snip]

 

[Dave Peterson]

If you replaced this:
set stations = workbooks("stations.xls").worksheets("Sheet1").range("a1:AL280")
with something like:
set stations = workbooks("C:\somefolder\stations.xls") _
.worksheets("Sheet1").range("a1:AL280")

It won't work.

The workbooks collection expects just the workbook name--no folder, no drive.
That's why the workbook has to be open.

But this would work ok.

Dim StatWkbk as workbook
Dim Stations as range
set statwkbk = workbooks.open(filename:="C:\somefolder\stations.xls")
set stations = statwkbk.worksheets("Sheet1").range("a1:al280")

There are workarounds for retrieving data from other workbooks using VBA,
though. I wouldn't expect any of them to work very quickly with lots of data to
retrieve (or look though).

You can see a way to get a single value here:

Look for GetValue function from John Walkenbach's site:
http://j-walk.com/ss/excel/eee/eee009.txt
or
http://spreadsheetpage.com/index.php/eee/issue_no_9_july_15_1999/

You could use this syntax, but I find it harder to read:
set stations = application.range("'[stations.xls]sheet1'!a1:al280")
(no drive, no path--and stations.xls needs to be open)

That is so useful! I found such code did not work with a drive and path.
Are you able to point at any specification documents to confirm this?
(My Excel version is 2003 (11.5612.5606))

[snip]