Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations SkipVought on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

SetSourceData Failing for Custom Excel Chart

Status
Not open for further replies.

VBAinspire

Programmer
Aug 8, 2001
10
0
0
US
I am trying to automate the creation of several custom Excel charts (Lines on 2 Axes) from Microsoft Access. I am getting the following error:

Run-time error '-2147417851(80010105) Method 'SetSourceData' of object '_Chart' failed

This is the line that is causing the problem
.SetSourceData Range("A" & startRow & "").CurrentRegion

I have tried changing the order of setting the chart type and source data, but it doesn't seem to fix the problem.

Here is the portion of code that creates the charts:

Code:
    'Add a summary LER chart for each Alternative
    For intAlt = 1 To intAltSelected
        xlBook.Charts.Add
        xlBook.ActiveChart.Location WHERE:=xlLocationAsObject, Name:="Index"
        With xlBook.ActiveChart
            .SetSourceData Range("A" & startRow & "").CurrentRegion
            .ApplyCustomType ChartType:=xlBuiltIn, TypeName:="Lines on 2 Axes"
            .HasTitle = True
            .ChartTitle.Characters.Text = varTables(intAlt) & " LER"
            .Axes(xlCategory, xlPrimary).HasTitle = True
            .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Time"
            .Axes(xlValue, xlPrimary).HasTitle = True
            .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Losses"
            .Axes(xlCategory, xlSecondary).HasTitle = False
            .Axes(xlValue, xlSecondary).HasTitle = True
            .Axes(xlValue, xlSecondary).AxisTitle.Characters.Text = "LER"
            .HasLegend = True
            .Legend.Position = xlLegendPositionBottom
            With .SeriesCollection(1)
                .XValues = "=Index!R1C2:R1C" & SumColMax & ""
                .Name = "=""Blue Losses"""
                .Border.ColorIndex = 25   'Blue line for Blue losses
                .MarkerBackgroundColorIndex = 25
                .MarkerForegroundColorIndex = 25
            End With
            With .SeriesCollection(2)
                .Name = "=""Red Losses"""
                .Border.ColorIndex = 3     'Red line for Red losses
                .MarkerBackgroundColorIndex = 3
                .MarkerForegroundColorIndex = 3
            End With
            With .SeriesCollection(3)
                .Border.ColorIndex = 6     'Yellow line for LER
                .MarkerBackgroundColorIndex = 6
                .MarkerForegroundColorIndex = 6
            End With
        End With
        'Cascade charts
        Dim Height As Integer
        Height = xlBook.ActiveChart.HeightPercent
        xlBook.ActiveChart.ChartArea.Select
        xlBook.ActiveSheet.Shapes("Chart " & intAlt).IncrementLeft -1.5
        xlBook.ActiveSheet.Shapes("Chart " & intAlt).IncrementTop intAlt*Height

        startRow = startRow + 4
    Next intAlt

Thank you so much for your help! Any suggestions would be appreciated very much!
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top