[分享]不打开文件提取数据

zhaodx

A VBA Function to Get a Value From a Closed File
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   XLFiles\Budget directory on drive C:.

Sub TestGetValue()
    p = "c:\XLFiles\Budget"
    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:\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
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

sunyujie

楼主解释一下吧．

向您问好

最好传附件测试

wsri

很感兴趣的代码,希望论坛上e文好的给翻译一下,然后作成范例供大家学习!

huangyun

QUOTE:

以下是引用zhaodx在2007-3-23 18:24:05的发言：

A VBA Function to Get a Value From a Closed File
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.

VBA提供从关闭的工作簿文件中检索值的方法。然而，可以利用EXCEL的能力来处理链接的文件。这一节包含的VBA函数（GETVALUE)可以关闭的工作簿中检索值，如下所示。其中调用了一个XLM宏，这是EXCEL5以前的版本中使用的老式宏。

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   XLFiles\Budget directory on drive C:.

下面的SUB过程了如何使用GETVALUE函数。其中显示了99Budget.xls文件中SHEET1工作表的单元格A1中的值，该文件位于C盘 XLFiles\Budget 目录下。

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

下面是另一个例子，这个过程从已经关闭的文件中读取了1200个值（100行、12列），然后把这些值放在了活动工作表中。

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
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

楼主可能是摘于美国John Walkenbach著的宝典丛书《EXCEL2003高级VBA编程宝典》的11.4.7《检索已经关闭的工作簿中的值》一节吧，在第256～257页中。

f_qc

试了试确实不错，ExecuteExcel4Macro的实现机理是什么？ XLM macro是什么呢，不明白

f_qc

ａｄｏ也可以不打开读数据，但其实使用数据库方式打开，这种模式？？

zhaodx

其实应用很方便，新建一个EXCEl文件，首先把自定义函数Private Function GetValue(path, file, sheet, ref) ...End Function 插入到你的模块中，然后把2个例子的代码再插入到你模块中，把里面的路径p=...、文件名f=...（工作簿）、工作表s=...、单元格a=...（第一个实例）替换成你的你要测试的文件，创建2命令按钮，然后就可以测试他们，操作正确的话，应该没问题。祝你成功！ 注意：这个自定函数不能当函数用，只能用模块调用。

[此贴子已经被作者于2007-3-24 3:50:20编辑过]

zhaodx

回5楼，我是从网搜到的，是John Walkenbach的免费下载代码，记得论坛有人求助过这样的代码，，感觉有价值，就贡献出来。感谢你的翻译！希望对大家有用！