ITPub博客

首页 > 应用开发 > IT综合 > A VBA Function to Get a Value From a Closed File

A VBA Function to Get a Value From a Closed File

原创 IT综合 作者:jenny_xu99 时间:2005-08-24 12:33:04 0 删除 编辑

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.

[@more@]

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/217838/viewspace-805038/,如需转载,请注明出处,否则将追究法律责任。

上一篇: CPIM 考试
请登录后发表评论 登录
全部评论

注册时间:2011-02-01

  • 博文量
    68
  • 访问量
    777166