excelvba

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


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

Solution

  • 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