VBAinspire
Programmer
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:
Thank you so much for your help! Any suggestions would be appreciated very much!
Run-time error '-2147417851(80010105) Method 'SetSourceData' of object '_Chart' failed
This is the line that is causing the problem
.SetSourceData Range("A" & startRow & ""
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!