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, listed below takes four arguments:
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
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
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.[@more@]
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/217838/viewspace-805038/，如需转载，请注明出处，否则将追究法律责任。