SUM data from recordset in VBA

The idea is to pull data from closed workbook, so I have this:

Option Explicit
Sub DataIzZatvorenogFila()

Dim con As ADODB.Connection
Dim rst As ADODB.Recordset

Set con = New ADODB.Connection
Set rst = New ADODB.Recordset

con.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source= W:\Materijalno\MG i AMBALAŽNI PAPIR\2025\FLUTING\FLUTING.xlsm; " & _
"Extended Properties='Excel 12.0 Xml;HDR=NO';"

con.Open

rst.ActiveConnection = con
rst.Source = "[ZBIRNA$E29:E40]"
rst.Open

Sheet3.Range("D2").CopyFromRecordset rst

rst.Close
con.Close

End Sub

It puls data from closed WB/sheetname(ZBIRNA)/range(E29:E40) and paste it in active WB/Sheet3/cells D2 to D13.

What I need is the SUM of those 12 consecutive cells which are in Recordset, to put in "D2"

I'm not very familiar with Databases and sql commands(not sure if I need it at all), saw someone mentioning DSUM function, but I don't know how to incorporate that here.

Solution

If you only need the SUM in D2, I'd suggest to use the following code

Option Explicit
Sub DataIzZatvorenogFila()
    Dim con As ADODB.Connection
    Dim rst As ADODB.Recordset
    Dim total As Variant
  
    Set con = New ADODB.Connection
    Set rst = New ADODB.Recordset
  
    con.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                           "Data Source= W:\Materijalno\MG i AMBALAŽNI PAPIR\2025\FLUTING\FLUTING.xlsm; " & _
                           "Extended Properties='Excel 12.0 Xml;HDR=NO';"
    con.Open
  
    ' Use SQL to sum the values in the specified range
    rst.Open "SELECT SUM(F1) AS Total FROM [ZBIRNA$E29:E40]", con
  
    ' Retrieve the total from the recordset
    If Not rst.EOF Then
        total = rst.Fields("Total").Value
    Else
        total = 0 ' In case there are no records
    End If
  
    ' Close the recordset and connection
    rst.Close
    con.Close
    ' Place the total in cell D2 of Sheet3
    Sheet3.Range("D2").Value = total
End Sub

Update

F1 refers to the first column of the specified range in the Excel worksheet.