Programmatic Chart Creation

[dccjr]

I have an Access database (FE/BE) with a user interface created in VBA. Via the UI, dynamic queries can be run in the data. These queries "dump" the resulting recordset to a temp table. This is turn is exported to an Excel worksheet for graphing (Pareto charts). Since these Pareto queries vary based on selections made in the UI combo boxes, I am looking for a way to create these bar graphs dynamically based on the data in the worksheet (which changes).

For example: I can run paretos based on event type, event category, work area, or time interval (between two dates). The queries provide the data to a generic worksheet since the calculations are the same. I would like to create on 'template' chart for all of these programmatically.

The reason for this is that depending on the query, the number of items on each axis changes, as does the titles. With this in mind, I have to be able to assertain the number of items in a column, and set the range accordingly.

Not an easy projrct. Any tips, pointers, snippets, etc would be greatly appreciated. I have read the articles from MSDN and several online sources, with little new insight gained.

 

[dccjr]

OK, since I seem to have no input on this, I will be more specific. I have found a Function to do what I want (kind of). I need a little help modifying it though. This creates a chart for each row in the source data sheet. I would like it to chart all of them, but don't know what to change. Anyone...?

Function CreateBarCharts() As Boolean
    Dim myChtObj As ChartObject
    Dim rngChtData As Range
    Dim rngChtXVal As Range
    Dim iColumn As Long
      
    Dim strSheetName As String
    strSheetName = "ChartData"
    Dim WSD As Worksheet
    Set WSD = Worksheets(strSheetName)
      
    Dim strChartSheet As String
    strChartSheet = "ChartOutput"
    Dim CSD As Worksheet
    Set CSD = Worksheets(strChartSheet)
      
    ' get the current charts so proper overwriting can happen
    Dim chtObjs As ChartObjects
    Set chtObjs = CSD.ChartObjects
  
    ' Turn off autofilter mode
    WSD.AutoFilterMode = False
      
    ' Find the last row with data
    Dim finalRow As Long
    finalRow = WSD.Cells(Application.Rows.Count, 1).End(xlUp).Row
      
    Dim i As Integer
      
    ' for each row in the sheet
    For i = 2 To finalRow
   
    '    Dim chartName As String
    '    chartName = WSD.Cells(i, 11).Value
      
        ' Delete chart if it already exists, we are making a new one
        Dim chtObj As ChartObject
        For Each chtObj In chtObjs
            If chtObj.Name = chartName Then
                chtObj.Delete
            End If
        Next
      
        ' define chart data range for the row (record)
        Dim dataString As String
        dataString = "C" & i & ":D" & i
        Set rngChtData = WSD.Range(dataString)
          
        ' define the x axis values
        Set rngChtXVal = WSD.Range("$b$2:$b$13")
  
        ' add the chart
        Charts.Add
        With ActiveChart
              
            ' make a bar chart
            .ChartType = xlColumnClustered
  
            ' remove extra series
            Do Until .SeriesCollection.Count = 0
                .SeriesCollection(1).Delete
            Loop
              
  
            ' add series from selected range, column by column
  
            With .SeriesCollection.NewSeries
                .Values = rngChtData
                .XValues = rngChtXVal
                .Name = "Cost"
            End With
              
            .Location Where:=xlLocationAsObject, Name:=chartSheet
  
        End With
  
        With ActiveChart
            .HasTitle = True
            .ChartTitle.Characters.Text = "Benefits Cost"
            .Parent.Name = WSD.Cells(i, 5).Value
            .Legend.Delete
          
            .Axes(xlCategory).TickLabels.AutoScaleFont = False
            With .Axes(xlCategory).TickLabels.Font
                .Name = "Arial"
                .FontStyle = "Regular"
                .Size = 10
                .Strikethrough = False
                .Superscript = False
                .Subscript = False
                .OutlineFont = False
                .Shadow = False
                .Underline = xlUnderlineStyleNone
                .ColorIndex = xlAutomatic
                .Background = xlAutomatic
            End With
              
            .Axes(xlValue).TickLabels.AutoScaleFont = False
            With .Axes(xlValue).TickLabels.Font
                .Name = "Arial"
                .FontStyle = "Regular"
                .Size = 8
                .Strikethrough = False
                .Superscript = False
                .Subscript = False
                .OutlineFont = False
                .Shadow = False
                .Underline = xlUnderlineStyleNone
                .ColorIndex = xlAutomatic
                .Background = xlAutomatic
            End With
              
            .ChartTitle.AutoScaleFont = False
            With .ChartTitle.Font
                .Name = "Arial"
                .FontStyle = "Bold"
                .Size = 12
                .Strikethrough = False
                .Superscript = False
                .Subscript = False
                .OutlineFont = False
                .Shadow = False
                .Underline = xlUnderlineStyleNone
                .ColorIndex = xlAutomatic
                .Background = xlAutomatic
            End With
       
            With .PlotArea.Interior
                .ColorIndex = 2
                .PatternColorIndex = 1
                .Pattern = xlSolid
            End With
          
        End With
          
        ' Set the height and width
        With CSD.ChartObjects(chartName)
            .Width = 225
            .Height = 175
        End With
    Next i
End Function

Special thanks to Jeremy Zerr for this.

 

[NdNoviceHlp]

What R U doing with the charts...using chart sheets? It's not a presentation thing that your after? Keep the same chart sheet and then delete and replace the series? Maybe you could reset the range for the series rather than deleting it and then re-building it. Maybe be a bit more patient and/or keep adding a bump to this thread and Tushar or Jon will show you the link. The code posted doesn't set the outcome of the function to anything... the function always returns False. Creating charts by a function I'm not sure if I support that. Charting FUNctions is different. Maybe I'll add a link. Good luck and remember all help here is FREE. HTH. Dave
http://www.mrexcel.com/forum/excel-questions/536821-graph-ln-function.html

 

[dccjr]

As I said earlier, my Access database exports query result to a sheet in Excel. On another sheet, I need a chart of this data to open in Print Preview in a window from the Access UI. The user will never see the sheets. The function above does exactly what I need it to do, just not with the correct information. I am putting the function in an OnOpen(or similar) event, so that the chart is created from the data whenever the preview is opened. I am not really familiar with Excel VBA. Anybody have some advice for modifying it to make it create a column for each row of data?

 

[NdNoviceHlp]

Here's an untested trial. Seems like this part of the code should be changed. HTH. Dave

dataString = "C" & i & ":D" & finalrow
Set rngChtXVal = WSD.Range("B" & i & ":B" & finalrow)