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

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

"Walter Briscoe" <(E-Mail Removed)> skrev i meddelelsen
news:(E-Mail Removed)...
>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

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)


Walter Briscoe wrote:
>
> 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

--

Dave Peterson

In message <(E-Mail Removed)> of Fri, 20 Mar 2009
08:05:58 in microsoft.public.excel.programming, Dave Peterson
<(E-Mail Removed)> writes

Thanks to you and to Per Jessen <(E-Mail Removed)> for help.

>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]
--
Walter Briscoe

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..._july_15_1999/

Walter Briscoe wrote:
>
<<snipped>>
> >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]
> --
> Walter Briscoe

--

Dave Peterson