Getting values from closed workbooks

[claytorm]

Hi,

I want to retrieve values from a specified cell in a closed workbook
Excel seems to let me do this if i open the workbook I am refering to
and this continues to function even if I change the reference afte
closing the referenced worksheet.

However, as I am potentially refering to hundreds of files, it is no
practical to open each sheet I wish to refer to.

Is there a solution whereby I can provide the file path and cel
reference, and have a value returned?

I found a suggested VBA solution (copied below) a
http://j-walk.com/ss/excel/tips/tip82.htm. However, since I don't kno
much about VBA (I'm learning), I couldn't put it to any use. If anyon
could explain it, I'd be most grateful.

Bertie.

VBA Function to Get a Value From a Closed File
VBA does not include a method to retrieve a value from a closed file
You can, however, take advantage of Excel's ability to work with linke
files.

This tip contains a VBA function that retrieves a value from a close
workbook. It does by calling an XLM macro.

Note:
You cannot use this function in a worksheet formula.

The GetValue Function
The GetValue function, listed below takes four arguments:

path: The drive and path to the closed file (e.g., "d:\files")
file: The workbook name (e.g., "99budget.xls")
sheet: The worksheet name (e.g., "Sheet1")
ref: The cell reference (e.g., "C4")

Private Function GetValue(path, file, sheet, ref)
' Retrieves a value from a closed workbook
Dim arg As String

' Make sure the file exists
If Right(path, 1) <> "\" Then path = path & "\"
If Dir(path & file) = "" Then
GetValue = "File Not Found"
Exit Function
End If

' Create the argument
arg = "'" & path & "[" & file & "]" & sheet & "'!" & _
Range(ref).Range("A1").Address(, , xlR1C1)

' Execute an XLM macro
GetValue = ExecuteExcel4Macro(arg)
End Function

Using the GetValue Function
To use this function, copy the listing to a VBA module. Then, call th
function with the appropriate arguments. The Sub procedure belo
demonstrates. It simply displays the value in cell A1 in Sheet1 of
file named 99Budget.xls, located in the XLFiles\Budget directory o
drive C:.

Sub TestGetValue()
p = "c:\XLFiles\Budget"
f = "99Budget.xls"
s = "Sheet1"
a = "A1"
MsgBox GetValue(p, f, s, a)
End Su

 

[Earl Kiosterud]

Clay,

If the cells you need are in fixed positions, you can use links. Links work
with closed files:

='[MyFile.xls]Sheet1'!$A$1

If you need to use things like VLOOKUP to find the data, and the table isn't
large, you can make links to the entire table (each cell), so it appears in
the local workwook. Use a link as above, change to relative, and copy all
over with the fill handle.

='[MyFile.xls]Sheet1'!A1
='[MyFile.xls]Sheet1'!A2, etc.

This may get unwieldy and slow with large tables.

Since you have hundreds of files, it sounds as if the file name being used
in any given situation may be a variable (in a cell, perhaps, and subject to
change). In that case, an event sub can be fired that will build a link
from the address information (file name, cell location) in cells or
elsewhere. The link will then ditifully extract the data from the closed
file.
--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

Hi,

I want to retrieve values from a specified cell in a closed workbook.
Excel seems to let me do this if i open the workbook I am refering to,
and this continues to function even if I change the reference after
closing the referenced worksheet.

However, as I am potentially refering to hundreds of files, it is not
practical to open each sheet I wish to refer to.

Is there a solution whereby I can provide the file path and cell
reference, and have a value returned?

I found a suggested VBA solution (copied below) at
http://j-walk.com/ss/excel/tips/tip82.htm. However, since I don't know
much about VBA (I'm learning), I couldn't put it to any use. If anyone
could explain it, I'd be most grateful.

Bertie.

VBA Function to Get a Value From a Closed File
VBA does not include a method to retrieve a value from a closed file.
You can, however, take advantage of Excel's ability to work with linked
files.

This tip contains a VBA function that retrieves a value from a closed
workbook. It does by calling an XLM macro.

Note:
You cannot use this function in a worksheet formula.

The GetValue Function
The GetValue function, listed below takes four arguments:

path: The drive and path to the closed file (e.g., "d:\files")
file: The workbook name (e.g., "99budget.xls")
sheet: The worksheet name (e.g., "Sheet1")
ref: The cell reference (e.g., "C4")

Private Function GetValue(path, file, sheet, ref)
' Retrieves a value from a closed workbook
Dim arg As String

' Make sure the file exists
If Right(path, 1) <> "\" Then path = path & "\"
If Dir(path & file) = "" Then
GetValue = "File Not Found"
Exit Function
End If

' Create the argument
arg = "'" & path & "[" & file & "]" & sheet & "'!" & _
Range(ref).Range("A1").Address(, , xlR1C1)

' Execute an XLM macro
GetValue = ExecuteExcel4Macro(arg)
End Function

Using the GetValue Function
To use this function, copy the listing to a VBA module. Then, call the
function with the appropriate arguments. The Sub procedure below
demonstrates. It simply displays the value in cell A1 in Sheet1 of a
file named 99Budget.xls, located in the XLFiles\Budget directory on
drive C:.

Sub TestGetValue()
p = "c:\XLFiles\Budget"
f = "99Budget.xls"
s = "Sheet1"
a = "A1"
MsgBox GetValue(p, f, s, a)
End Sub

 

[claytorm]

Earl,

Thanks for your reply. I still can't get it to return a value from
closed sheet. I'm using

=C:\Documents and Settings\Administrator\My Documents\Dividen
proj\new\'[UKX.xls]Sheet1'!A1

as my reference. This returns 'The formula you entered contains a
error'. What am i doing wrong?

Thank

 

[Dave Peterson]

It looks like you have your apostrophes in the wrong spot.

='C:\Documents and Settings\Administrator\My Documents\Dividend
proj\new\[UKX.xls]Sheet1'!A1

Watch for line wrap.

I don't type these kinds of formulas myself. I'll open the other workbook, then
start my formula with an equal sign (=).

Then I'll go back to the cell that I want and point at it.

When I close that "sending" workbook, excel will fix my formula to include the
path.

Earl,

Thanks for your reply. I still can't get it to return a value from a
closed sheet. I'm using

=C:\Documents and Settings\Administrator\My Documents\Dividend
proj\new\'[UKX.xls]Sheet1'!A1

as my reference. This returns 'The formula you entered contains an
error'. What am i doing wrong?

Thanks