A 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
Another example is shown below. This procedure reads 1,200 values (100
rows and 12 columns) from a closed file, and places the values into the
active worksheet.
Sub TestGetValue2()
p = "c:\XLFiles\Budget"
f = "99Budget.xls"
s = "Sheet1"
Application.ScreenUpdating = False
For r = 1 To 100
For c = 1 To 12
a = Cells(r, c).Address
Cells(r, c) = GetValue(p, f, s, a)
Next c
Next r
Application.ScreenUpdating = True
End Sub
Caveat
In order for this function to work properly, a worksheet must be active
in Excel. It will generate an error if all windows are hidden, or if the
active sheet is a Chart sheet.