excel - 从关闭的 Excel 工作簿中读取数据

Question

我正在尝试从多个共享 Excel 文档中读取数据而不打开它们。除了我写的文件之外，它们都在同一个网络目录中。

这是我正在提取的数据示例:

=SUMPRODUCT(COUNTIFS('[Tracker_v1.2.xlsm]Work Log'!$D:$D,B5,'[Tracker_v1.2.xlsm]Work Log'!$J:$J,{"XXXX","YYYY"}))
=SUMPRODUCT(COUNTIFS('[B Tracker_v1.1.xlsm]Work Log'!$D:$D,B6,'[B Tracker_v1.1.xlsm]Work Log'!$J:$J,{"XXXX","YYYY"})+COUNTIFS('[A Tracker_v1.1.xlsm]Work Log'!$D:$D,B6,'[A Tracker_v1.1.xlsm]Work Log'!$J:$J,{"XXXX","YYYY"}))

我尝试使用文件路径'\\网络路径\[A Tracker_v1.1.xlsm]工作日志'!

有没有一种方法可以在不手动打开文档的情况下读取数据？

Answer 1

由于您不想手动打开文件，我怀疑您可能会接受自动打开它们。下面是一个关于如何使用 VBA 打开和关闭某些文件以及执行计算的简单示例。从技术上讲，您仍然会打开这些文件，但只会打开一小会儿，并且不会实际显示它们。为了便于说明，假设文件夹 "C:\MyPath\" 中有两个文件 FileA.xlsm 和 FileB.xlsm，其中A1 到 A3 范围内的以下数据:

FileA           FileB
1               4444
22              55555
333             666666

以下代码将在立即窗口中打印每列的总和。

Sub OpenClosedFiles()
' Opens some files, does some calculations and then closes those files.

    Application.ScreenUpdating = False ' Hide the files during the microseconds while they're open.

    ' Define the path:
    Const sPath As String = "C:\MyPath\" ' <--- Replace; don't forget the last backslash.

    Dim rngA, rngB As Range
    Dim sFileA, sFileB As String
    Dim wbA, wbB As Workbook

    sFileA = "FileA.xlsm"
    sFileB = "FileB.xlsm"

    Set wbA = Workbooks.Open(sPath & sFileA)
    Set wbB = Workbooks.Open(sPath & sFileB)

    Set rngA = wbA.Worksheets(1).Range("A1:A3")
    Set rngB = wbB.Worksheets(1).Range("A1:A3")

    ' Do calculations on the ranges here, for example:
    Debug.Print "Sum of FileA: " & Application.WorksheetFunction.Sum(rngA)
    Debug.Print "Sum of FileB: " & Application.WorksheetFunction.Sum(rngB)

    wbA.Close
    wbB.Close

    Application.ScreenUpdating = True
End Sub