VBA does not include a method to retrieve a value from a closed workbook file. You can, however, take advantage of Excel's ability to work with linked files. This section contains a VBA function (GetValue, which follows) that retrieves a value from a closed workbook. It does so by calling an XLM macro, which is an old-style macro used in versions prior to Excel 5.
Testing for Membership in a Collection
The following function procedure is a generic function that you can use to determine whether an object is a member of a collection:
Private Function IsInCollection(Coln As Object, _ Item As String) As Boolean Dim Obj As Object On Error Resume Next Set Obj = Coln(Item) IsInCollection = Not Obj Is Nothing
This function accepts two arguments: the collection (an object) and the item (a string) that might or might not be a member of the collection. The function attempts to create an object variable that represents the item in the collection. If the attempt is successful, the function returns True; otherwise, it returns False.
You can use the IsInCollection function in place of three other functions listed in this chapter: RangeNameExists, SheetExists, and WorkbookIsOpen. To determine whether a range named Data exists in the active workbook, call the IsInCollection function with this statement:
MsgBox IsInCollection(ActiveWorkbook.Names, "Data")
To determine whether a workbook named Budget is open, use this statement:
MsgBox IsInCollection(Workbooks, "budget.xls")
To determine whether the active workbook contains a sheet named Sheet1, use this statement.
MsgBox IsInCollection(ActiveWorkbook.Worksheets, "Sheet1")
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
The GetValue function takes four arguments:
♦ path: The drive and path to the closed file (for example, "d:\files")
♦ file: The workbook name (for example, "budget.xls")
♦ sheet: The worksheet name (for example, "Sheet1")
♦ ref: The cell reference (for example, "C4")
The following Sub procedure demonstrates how to use the GetValue function. 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.
p = "c:\XLFiles\Budget" f = "99Budget.xls" s = "Sheet1" a = "A1"
Another example follows. This procedure reads 1,200 values (100 rows and 12 columns) from a closed file and then places the values into the active worksheet.
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
The GetValue function does not work if used in a worksheet formula. Actually, there is no need to use this function in a formula. You can simply create a link formula to retrieve a value from a closed file.
Was this article helpful?