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

## Sum Up Cells

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

```
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
```

- Build vertical summary from Columns with Count
- Excel - Create a Unit Conversion sheet - Multiple links
- update cell when column header matches a list
- Delete text in cell subject to Worksheet_Change
- Counting the number of visible rows after autofilter
- Excel 2007: AVERAGEIF, SUMIF, COUNTIF, MAXIF, MINIF across multiple sheets, multple rows
- Import CSV data from a txt file skipping the first line and adding headers
- Simplifying SumIFs formulas for efficient excel formula
- I'd like to find out how to find the current streak of non-negative numbers in a row of data in Excel
- ValueError: Invalid character found in sheet title
- decrypt excel files
- Excel table search funcion that looks for partial string match
- Combine macros to filter on today's and tomorrow's date
- Converting line breaks to commas in excel sheet using Powershell
- Combinations of numbers arranged side by side as many as a random number (VBA)
- How can I remove ONLY leading and trailing spaces while leaving spaces in between words alone with an excel formula?
- VBA Date as integer
- Sliding Window Auto Increment Range
- Count cells with different conditional ranges
- Turning flattened pivots data into tables -Error tables can't overlap - Excel Vba
- How do I get only a specific part of a cell in Excel or Numbers?
- Calculate the Legendre symbol of two integers in Excel
- Unable to get text wrapping or vertical centering to work with xlsxwriter
- Number stored as text warning in excel using POI
- VBA Excel paste to the columns in regular intervals
- Convert filtered values from formulas to values
- Sorting Dictionary by nested class objects data [VBA]
- Events does not appear when making an Outlook query from Excel
- The script works well when I use MSXML2.XMLHTTP.6.0, but it fails miserably when I switch to MSXML2.serverXMLHTTP.6.0
- How to convert text string (d h m s) to time format in excel