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 XLFilesBudget directory on drive C:.

Sub TestGetValue()
    p = "c:XLFilesBudget"
    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:XLFilesBudget"
    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.