CODEWITHSUNDEEP
excelvba
CJ0206
CJ0206

Reputation: 95

Read data from a closed Excel workbook

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

I have tried using file paths '\\network path\[A Tracker_v1.1.xlsm]Work Log'!

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

Upvotes: 4

Views: 12120

Answers (3)

Egalth
Egalth

Reputation: 1000

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

Upvotes: 3

Harassed Dad
Harassed Dad

Reputation: 4704

You can use ADO and the Excel ODBC driver to treat closed workbooks as databases. You can then use SQL to retrieve data. But it assumes that you have information laid out in tables with table headings. AFAIK that's the only way to deal with a spreadsheet without opening it.

There's a walk through here

Upvotes: 1

scenography
scenography

Reputation: 320

Would it work to link to the closed files? (In Excel 2016, click Data > Get Data > From File > From Workbook.) Then the VBA code would be only ActiveWorkbook.RefreshAll.

Upvotes: 0

Related Questions