How can I access data from another workbook without opening it?

Question

I have a VBA function that goes through the worksheets of open workbooks and, for example, adds the values in cell A1 .

I can only specify the names of open workbooks.

I want to not have to open the workbooks, but to work with the values based on the path.

Function test(wbName As String, ByVal wsName As String, ByVal wsName2 As String) As Long
    start = Workbooks(wbName).Worksheets(wsName).Index
    end = Workbooks(wbName).Worksheets(wsName2).Index
    If start <= end Then
        For i = start To end
            test = Workbooks(wbName).Worksheets(i).Range("A1").Value + test
        Next i
    ElseIf start > end Then
        MsgBox ("The start date cannot be later than the end date.!")
    End If
End Function

Answer 1

Sum Up Cells

Option Explicit

Function SumOfA1s( _
    ByVal WorkbookPath As String, _
    ByVal FirstWorksheetName As String, _
    ByVal LastWorksheetName As String) _
As Double
    Const PROC_TITLE As String = "Sum of A1s"
    On Error GoTo ClearError
    
    Dim wb As Workbook: Set wb = Workbooks.Open(WorkbookPath)
    
    Dim First As Long: First = wb.Worksheets(FirstWorksheetName).Index
    Dim Last As Long: Last = wb.Worksheets(LastWorksheetName).Index
    
    If First > Last Then
        MsgBox "The start date cannot be later than the end date!", _
            vbExclamation, PROC_TITLE
        Goto ProcExit
    End If
    
    Dim Value, i As Long, Result As Double
    
    For i = First To Last
        Value = wb.Worksheets(i).Range("A1").Value
        If IsNumeric(Value) Then
            Result = Result + Value
        End If
    Next i
    
    SumOfA1s = Result

ProcExit:
    On Error Resume Next
        If Not wb Is Nothing Then
            wb.Close SaveChanges:=False
        End If
    On Error GoTo 0
    Exit Function
ClearError:
    MsgBox "Run-time error '" & Err.Number & "':" _
        & vbLf & vbLf & Err.Description, vbCritical, PROC_TITLE
    Resume ProcExit
End Function