从封闭的Excel工作簿中读取数据 [英] Read data from a closed Excel workbook

问题描述

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

I am trying to read data from several shared Excel documents without opening them. They are all in the same network directory except for the file I have written.

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

This is a sample of the data I am pulling:

=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"}))

我尝试使用文件路径'\\ network path \ [A Tracker_v1.1.xlsm]工作日志'！

是否可以在不手动打开文档的情况下读取数据?

Is there a way to read data without manually opening documents?

推荐答案

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

Since you don't want to open the files manually, I suspect you might accept to open them automatically. Here's a straightforward example on how to open and close some files and perform calculations using VBA. You will still technically open the files but only for a short moment, and without actually displaying them. For the sake of illustration, assume you have two files FileA.xlsm and FileB.xlsm in the folder "C:\MyPath\" with the following data in range A1 to A3:

FileA           FileB
1               4444
22              55555
333             666666

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

The following code will print the sum of each column in the immediate window.

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

这篇关于从封闭的Excel工作簿中读取数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！