excel - SUM data from recordset in VBA - Stack Overflow

admin管理员组

文章数量:1403493

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.

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.

• excel
• vba
• dbo

Share Improve this question Follow asked Mar 20 at 12:42 Jelovac MaglajJelovac Maglaj 3377 bronze badges 3

• Might be easier to use Power Query to get the data? Available through Data > Get & Transform Data – Darren Bartrup-Cook Commented Mar 20 at 12:50
• What I need is the SUM of those 12 consecutive cells which are in Recordset, to put in "D2" WIll this not overwrite the value in D2 you just put there with Sheet3.Range("D2").CopyFromRecordset rst? – Storax Commented Mar 20 at 12:58
• That is how it functioning now, but I don't need whole range, just SUM of the range! – Jelovac Maglaj Commented Mar 20 at 13:12

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 2

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.

本文标签： excelSUM data from recordset in VBAStack Overflow