I'm using VBScript to create a line scatter plot from columns of data in Excel 2003. It comes out fine, but I want to edit some of the properties of the chart, such as background color and axis labels. I did this manually in Excel and recorded a macro, which gave me the following VBA code:
ActiveChart.PlotArea.Select
With Selection.Border
.ColorIndex = 16
.Weight = xlThin
.LineStyle = xlContinuous
End With
With Selection.Interior
.ColorIndex = 2
.PatternColorIndex = 1
.Pattern = xlSolid
End With
ActiveChart.Axes(xlCategory).Select
With Selection.TickLabels
.ReadingOrder = xlContext
.Orientation = 45
End With
ActiveChart.Axes(xlValue).AxisTitle.Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.ReadingOrder = xlContext
.Orientation = xlHorizontal
End With
ActiveChart.ChartArea.Select
End Sub
This looks fine for VBA, but I'm having trouble converting it to VBScript. How should I get started? This is my code at the moment:
Set objChart = objExcel.Charts.Add()
With objExcel.ActiveChart
.ChartType = xlXYScatterLinesNoMarkers
.SeriesCollection(1).Interior.Color = RGB(255, 0, 0)
.HasTitle = True
.ChartTitle.Text = "usage"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Time"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "units"
.HasLegend = False
.SetSourceData objWorksheet.Range("E1","F" & LastRow), xlColumns
.SetSourceData objWorksheet.Range("E1:F200"), xlColumns
End With
The line .SeriesCollection(1).Interior.Color = RGB(255, 0, 0) causes an error: "Unable to set the color property of the Interior class". I'm guessing I shouldn't call .SeriesCollection right under .Activechart. Any suggestions? At this point I'd just be happy to be able to change the background color of the chart to white, and rotate the x-axis labels 45 degrees.
Thank you in advance.
Best Answer
Order matters. Before you can access a series, you have to add it first. Same goes for other properties, like
HasTitle
. Also, the lines of an XY diagram don't have an interior color, that's only available in charts that show an interior (like pie charts or column charts). You probably mean the border color here. Change your code to this:and it should work, provided you have defined the symbolic constants.