Read data from a closed Excel workbook

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

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

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.