Retrieving a value from a closed workbook

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

End Function

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.

Sub TestGetValue()

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.

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

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?

0 0


Post a comment