Create and Arrange Graphs in Excel from Access (1 Viewer)

Status
Not open for further replies.

chergh

blah
Local time
Today, 10:13
Joined
Jun 15, 2004
Messages
1,414
This is generic code that will create and arrange graphs in excel from access, you need to output the data to graph into excel first.

Parameters:

cht_sourcedata: The first cell of the data you want to graph passed as an excel range

cht_title: The title you want for your graph

x_name: The title for your x-axis

y_name: The title for your y-axis

xlapp: The excel application object you created when transferring over your data


Code:
Sub CreateGraph(cht_sourcedata As Excel.Range, cht_title As String, _
    x_name As String, y_name As String, xlapp As Excel.Application)

Dim cht As Excel.Chart

Set cht = xlapp.Charts.Add

With cht
    .ChartType = xlColumnClustered
    .SetSourceData cht_sourcedata.CurrentRegion
    .PlotBy = xlColumns
    .Location xlLocationAsObject, "Sheet1"
End With
    
With xlapp.ActiveChart
    .HasTitle = True
    .HasLegend = False

    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 Sub

This arranges the charts in the worksheet so they can all be seen.

Parameters:

HowManyWide: How many graphs to display in a row e.g. If you have 6 graphs and specify the parameter as 3 the graphs will be arranged in 2 rows of 3

ws: The worksheet where the graphs are.

Code:
Sub ArrangeCharts(HowManyWide As Integer, ws As Excel.Worksheet)
    
    Dim Chart As Excel.ChartObject
    Dim A As Integer
    Dim B As Integer
    Dim NextB As Integer
    Dim i As Integer

    A = 0
    B = 0

    NextB = 0
    i = 1

    For Each Chart In ws.ChartObjects

        Chart.Left = A
        Chart.Top = B

        A = A + Chart.Width

        If B + Chart.Height > NextB Then
            NextB = B + Chart.Height
        End If

        i = i + 1
      
        If i > HowManyWide Then
            i = 1
            A = 0
            B = NextB
        End If

    Next Chart

End Sub
 
Last edited by a moderator:
Status
Not open for further replies.

Users who are viewing this thread

Top Bottom