build a chart in Excel with data from access

[davikokar]

Hallo,

I would like an Access Module to take some data from a table a then build a graph in Excel using thata data.

The problem is that I don't know how to set some of the properties of the chart, like the ChartType, SetSourceData...

I tried to use the code generated by a macro but access doesn't seem to recognize it.

Here what I have so far:

Sub Export_To_Excel()

'variables

Dim strSQL As String
Dim dBase As DAO.Database
Dim rs As DAO.Recordset
Dim xlApp As Object
Dim Sht As Object
Dim uRow As Integer
Dim i As Integer


'create recordset

DoCmd.SetWarnings False
strSQL = "SELECT tbl_Payment.Pay_Amount, tbl_Payment.Pay_Date FROM tbl_Payment WHERE ((Year([Pay_Date])= " & [Forms]![frm_menu]![cbo_year].Value & "));"
Set dBase = CurrentDb()
Set rs = dBase.OpenRecordset(strSQL, dbOpenDynaset)
     
     
     
'Create Excel object

Set xlApp = CreateObject("Excel.Application")
xlApp.Workbooks.Add
xlApp.Visible = True
Set Sht = xlApp.ActiveWorkbook.Sheets(2)


' Pass data to worksheet.
         
 uRow = 1
 Do Until rs.EOF
    For i = 0 To rs.Fields.Count - 1
        Sht.Cells(uRow, i + 1).Value = rs(i)
        Next i
    rs.MoveNext
    uRow = uRow + 1
 Loop



' format date

With Sht
    .Columns("B").NumberFormat = "dd/mm/yyyy;@"
End With



'add chart

Dim oChart As Object

Set oChart = xlApp.charts.Add()
oChart.Type = xlLineMarkers 'this would not work

'Sht.charts.Add
'Sht.ActiveChart.ChartType = 65 ' xlLineMarkers
'Sht.ActiveChart.SetSourceData Source:=Sheets("Sheet2").Range("A1:A3"), PlotBy:=xlColumns
'Sht.ActiveChart.SeriesCollection(1).XValues = "=Sheet2!R1C2:R3C2"
'Sht.ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"
'With Sht.ActiveChart
'    .HasTitle = True
'    .ChartTitle.Characters.Text = "Statistics"
'    .Axes(xlCategory, xlPrimary).HasTitle = True
'    .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "2003"
'    .Axes(xlValue, xlPrimary).HasTitle = True
'    .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Amount"
'End With



'Clear variables

Set Sht = Nothing
xlApp.Quit
Set xlApp = Nothing
Set rs = Nothing
Set dBase = Nothing
End Sub

Do you have an example a good link or suggestions?

Thanks

 

[PHV]

You may try something like this typed, untested):
'add chart
Dim oChart As Object
Set oChart = xlApp.ActiveWorkbook.Charts.Add()
With oChart
.Type = 65 '65=xlLineMarkers
.SetSourceData Source:=Sht.Range("A1:A3"), PlotBy:=2 '2=xlColumns
.SeriesCollection(1).XValues = "=Sheet2!R1C2:R3C2"
.Location Where:=2, Name:="Sheet1" '2=xlLocationAsObject
.HasTitle = True
.ChartTitle.Characters.Text = "Statistics"
.Axes(1, 1).HasTitle = True '1=xlCategory, 1=xlPrimary
.Axes(1, 1).AxisTitle.Characters.Text = "2003"
.Axes(2, 1).HasTitle = True '2=xlValue
.Axes(2, 1).AxisTitle.Characters.Text = "Amount"
End With

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[MichaelRed]

... but ... but ... why?

The chart "applet" is available to all the MS suite of productivity programs, so it can just as easily (at least) be done in Ms. A. as the spreadsheet thinggggyuyyy. The interface to the chart looks a little bit different, but that is just due to the difference between the concepts of a database and a spreadsheet.




MichaelRed