Creating chart in excel from Access

[TUSSFC]

I've been googling like mad. There's plenty of info on creating charts in access reports ... but here's what I need to do.

1 Excel Workbook
1 Worksheet
1 table of data

I already have this worksheet opened via other VBA in my Access database (i've been exporting a query to the excel file and formatting the data nicely).

I need to generate a new line chart based on the data in the worksheet.

The range cannot be specified - the number of records the query returns will change, so it needs to be entirely dynamic.

It would help if I'm referring to the worksheet name as I intend on having multiple worksheets and creating a chart on each one.

To summarise:

* Instance of the relevant workbook is open

1/ Select a specific worksheet by name
2/ Find the data range (starting at A1: and ending at the last populated row/column of data)
3/ Use this range as my chart data source
4/ Generate a line chart and place it below the last row in my table (using the range found out in Step 2)

Not overly bothered about the graph being anything fancy - simple line graph with value points will do.

Row 1 will be the X axis (Date)
Rows 2 onwards will be the values and have 1 legend line each
Column A, Rows 2 onwards contains the legend names
The Y axis will be the "Total Number"

An example of the data would be:

Date_____28/01/2009____29/01/2009____30/01/2009
Stat 1____10___________20____________50
Stat 2____100__________120___________150
Stat 3____110__________220___________250

The slightest of help in getting started would be great. Am happy to do most of the leg work ;-)

Thanks.

 

[chergh]

http://www.access-programmers.co.uk/forums/showthread.php?t=156081 should be a good starting point

 

[TUSSFC]

Thanks for the pointer. I've made an "ok" start. My code is creating the chart object itself, setting the correct chart type and the correct data range.

However ... it is not plotting any data.

I need to set the following:

X Axis Labels - set to range =$B$1:$U$1 (this will never change)

Then I need to dynamically create a number of Series' based on the number of rows of data in my data range. This number of rows could change, so this must be dynamic. I can set the total number of records (ie, rows) to a variable if required (i'd prefer it if the code can just check how many rows of data there is in excel).

The Series Name needs to be based on the value in Column A. For example, =$A$2, =$A$3, etc.

The Series Value needs to be based on the data held in columns B-U (the number of columns will be constant). For example, where the series name is A2 the value will be =$B$2:$U$2, where the series name is A3 the value will be =$B$3:$U$3, etc.

I appreciate any help on this part.

My code so far is:

Set objExcel = New Excel.Application
 
objExcel.Visible = True
objExcel.DisplayAlerts = False
objExcel.EnableEvents = False
objExcel.ScreenUpdating = False
 
Set xlBook = objExcel.Workbooks.Open(strOutput)
 
Dim cht As Excel.Chart
Dim cht_sourcedata As Excel.Range
Dim cht_title As String
Dim x_name As String
Dim y_name As String
 
Set dbCurrent = CurrentDb
Set rs = dbCurrent.OpenRecordset("SELECT * FROM MOD7_tbl_StatCats")
 
Do While Not rs.EOF
 
strStatCat_Title = rs("StatCat_Title")
Set xlSheet = xlBook.Worksheets(strStatCat_Title)
 
xlSheet.Range("A1").Value = "Date"
xlSheet.Columns.AutoFit
 
'test graph
 
Set cht = objExcel.Charts.Add
Set cht_sourcedata = xlSheet.Range("A1")
 
cht_title = strStatCat_Title
x_name = "Date"
y_name = "Number"
 
With cht
    .ChartType = xlLineMarkers
    .SetSourceData cht_sourcedata.CurrentRegion
    .PlotBy = xlRows
    .Location xlLocationAsObject, strStatCat_Title
End With
 
With objExcel.ActiveChart
    .HasTitle = True
    .HasLegend = True
    With .ChartTitle
        .Top = 1
        .AutoScaleFont = False
        .Characters.Text = cht_title
        .Font.Size = 12
        .Font.Bold = True
        .Shadow = True
        .Border.LineStyle = xlSolid
    End With
 
    With .ChartGroups(1)
        .GapWidth = 20
        .VaryByCategories = False
    End With
 
    With .Axes(xlCategory)
        With .TickLabels
            .Font.Size = 8
            .Alignment = xlCenter
            .Orientation = xlHorizontal
        End With
 
        .HasTitle = True
        .AxisTitle.Caption = x_name
 
    End With
 
    With .Axes(xlValue)
        .HasTitle = True
        .AxisTitle.Caption = y_name
        .AxisTitle.Left = 1
    End With
 
    With .PlotArea
        .Interior.ColorIndex = xlNone
        .Top = 26
        .Height = 175
        .Left = 17
        .Width = 342
    End With
 
    .Axes(xlCategoryScale).TickLabels.Font.Size = 8
End With
 
'end test graph
 
rs.MoveNext
Loop
 
rs.Close
Set rs = Nothing
dbCurrent.Close
Set dbCurrent = Nothing
 
xlBook.Save
 
objExcel.DisplayAlerts = True
objExcel.EnableEvents = True
objExcel.ScreenUpdating = True
 
xlBook.Close
Set xlBook = Nothing
 
objExcel.Application.Quit
Set objExcel = Nothing

 

[TUSSFC]

Hmm.

I've tried this snippet of code:

    With .SeriesCollection(1)
        .XValues = xlSheet.Range("=" & strStatCat_Title & "!$B$1:$U$1")
    End With
    
    Dim cNumCols As Integer
    Dim cNumRows As Integer
    Dim i As Integer
    cNumCols = 20
    cNumRows = 5 'just using to test
    i = 1
    
    For i = 1 To cNumRows
    strThisRow = i + 1
    With .SeriesCollection(i)
        .Values = xlSheet.Range("=" & strStatCat_Title & "!$B$" & strThisRow & ":$U$" & strThisRow & "")
        .Name = xlSheet.Range("=" & strStatCat_Title & "!$A$" & strThisRow & "")
    End With
    i = i + 1
    Next i

This part is working:

With .SeriesCollection(1)
.XValues = xlSheet.Range("=" & strStatCat_Title & "!$B$1:$U$1")
End With

But With .SeriesCollection(i) is throwing up error:

Runtime error 1004
Method 'SeriesCollection' of object '_Chart' failed

:-s Any ideas?

 

[TUSSFC]

Woohoo ... it's been a day of tinkering and i'm getting cloooooser

I seem to be talking to myself in this thread, but hopefully this trial and error might be of use to others at some point.

Anyway. I'm now up to using this code:

    Do Until .SeriesCollection.Count = 0
        .SeriesCollection(1).Delete
    Loop
    With .SeriesCollection.NewSeries
        .XValues = xlSheet.Range("=" & strStatCat_Title & "!$B$1:$U$1")
    End With
    
    Dim cNumCols As Integer
    Dim cNumRows As Integer
    Dim i As Integer
    cNumCols = 20
    cNumRows = 5
    i = 1
    For i = 1 To cNumRows
    strThisRow = i + 1
    With .SeriesCollection.NewSeries
        .Values = xlSheet.Range("=" & strStatCat_Title & "!$B$" & strThisRow & ":$U$" & strThisRow & "")
        xlBook.Save
        .Name = xlSheet.Range("=" & strStatCat_Title & "!$A$" & strThisRow & "")
    End With
    i = i + 1
    xlBook.Save
    Next i

The funny this is, it's now only plotting EVEN row numbers on the charts. Those even rows are within the first 6 rows of the worksheet but only ever 2, 4 and 6. How odd!

 

[chergh]

Have you tried pasting the sub I linked exactly as it is and then after the line in your code:

Set xlBook = objExcel.Workbooks.Open(strOutput)

add

Set xlSheet = xlBook.Worksheets(strStatCat_Title)

call CreateGraph(xlsheet.range("A1"), "Chart Title", "X-Axis Name", "Y-axis Name", objExcel)

 

[TUSSFC]

Doh, no need for

i = i + 1

Sweet. Remove that from my code above and it works lovely. It loops through worksheets and adds a chart on each. Sets a series for each row dynamically.