Create chart using RecordSet data in VBA Excel code

Question

I am getting data from Access, copying into Excel sheet and creating a chart using Excel VBA. The data looks like this:

And the chart looks like this:

I want to show all transactions in sheet but in chart I want to show sum of amount of dates. For example on 19/08/2015 total amount 2695, 20/08/2015 total amount 287.

How can I create a chart in VBA Excel to show the sum of each day?

Answer 1

I agree with @ChipsLetten

1.- From SQL you could use something like this:

 ---- This is the sum for each day
SELECT 
    t.DOT, SUM(t.Amount)
    ---- You can add an extra Column with the SUM by Dates
    /*
    t.Dot, t.Category, t.Item, t.Amount
    , SUM(t.Amount) OVER(PARTITION BY t.Dot ORDER BY t.Dot) [SumTrans]
    */
FROM Transactions t
GROUP BY t.DOT
-- If you add the Column you must change "GROUP BY"
-- GROUP BY t.Dot, t.Category, t.Item, t.Amount

Adding the column you could use the DOT and SumTrans series to create the Chart

2.- With pivot table is even better, you can clone your Recordset to create a pivot table that allows you to SUM Amount by DOT

*Wkb is a Workbooks object, Wks is a Worksheet object*

Dim oRstChrt As ADODB.Recordset, oStrm As ADODB.Stream
Dim oChrt As Chart

'' Copy Recordset for the pivot table cache
Set oRstChrt = New ADODB.Recordset
Set oStrm = New ADODB.Stream

'' Create an alternative Recordset for the new pivot table
oRst.Save oStrm
oRstChrt.Open oStrm

'' Set Recordset to cache for a pivot table
Set objPivotCache = _
    Wkb.PivotCaches.Create(SourceType:=xlExternal, Version:=xlPivotTableVersion14)
'' Recordset with Data
Set objPivotCache.Recordset = oRstChrt
'' Assign Range for the pivot table and Name it.
With objPivotCache
    .CreatePivotTable TableDestination:=Wks.Range([Cell]), TableName:=[PivotTable_Name]
End With

Afterwards put the fields you need, in this case DOT and Amount

With Wks.PivotTables([PivotTable_Name])

    With .PivotFields("DOT")
        .Orientation = xlRowField
        .Position = 1
    End With
    '' You can create a second level, but 
    '' if the field is not collapse, your chart will change, 
    '' so keep the field collapse
    '' To Collapse the field put this
    '' Wks.PivotTables([PivotTable_Name]). _
    ''        PivotFields([PivotTableField]).ShowDetail = False
    'With .PivotFields("Item")
        '.Orientation = xlRowField
        '.Position = 2
    'End With

    '' This allows you to SUM the Amount field
    .AddDataField .PivotFields("Amount"), "Total Amount", xlSum

End

'' After the Pivot Table was created you can create your chart
'' Set the object
Set oChrt = Charts.Add
'' Give a name and format
With oChrt
    .Name = "[Chart_Name]"
    .ChartType = xlColumnClustered  '' you could change to 3D
    '' Source Range in this case from the pivot table
    .SetSourceData Source:=Wks.PivotTables([PivotTable_Name]).TableRange2, _
                                PlotBy:=xlRows
    '' Format chart
    .HasTitle = True
    .ChartTitle.Text = "Total Transactions"
    .Axes(xlCategory, xlPrimary).HasTitle = True
    .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Dates"
'    .Axes(xlValue, xlPrimary).HasTitle = True
'    .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Transactions"
End With

Answer 2

You have two choices:

1. Run another SQL query using SUM and GROUP BY to give you the totals. This is the data for your chart.

2. Look at using Pivot Charts in Excel. This will calc the totals for you and also draw the chart.