Excel charting options using VBScript

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Through the help of TechNet, I'm now able to create a simple VBScript to
automatically generate an Excel chart. Where can I find the detail
options/methods that I can use to specify more detail information, such as
X-Axes label, similar to objChart.ChartTitle.Characters.Text = "Chart Title"?
 
The best way is through a learn macro. Go to tools menu on worksheet and
select Macro - Record New macro. The go to the chart and perrform the
formating maully. Then stop recording and use the recorded macro to make you
own adjustments.

The recorded macro is usually a very specific macro with precise cell
locations. Your job is to make it work in a general purpose macro.
 
Thanks Joel. I tried that before and yet was unable to map the macro calls to
VBCrsipt calls. I'll try again.

Is there a reference manual on line for all these Excel/Chart VBScript calls?

Thanks.
 
Not really. If you need help with modifying the macros I will be glad to
help. The chart macros are a little tricky to modify.
 
Ok, the first one is about the title/label:

With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "EAGLE Servers CPU Utilization 11/02/07"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = _
"15 Minutes Interval"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "%Utilized
(Busy)"
End With

I can set the Titlt as:
objChart.HasTitle = True
objChart.ChartTitle.Characters.Text = "EAGLE Servers CPU Utilization 11/02/07"

How about the X-axes (xlCategory) and Y-axes (xlValue) label?

Thanks much!
 
I got this from a recorded macro

ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection(1).Select
ActiveChart.PlotArea.Select
ActiveChart.Axes(xlValue).Select
With ActiveChart.Axes(xlValue)
.MinimumScale = 3
.MaximumScaleIsAuto = True
.MinorUnitIsAuto = True
.MajorUnitIsAuto = True
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With
 
I'm now getting the following error (dialog box):

Error: Unable to get the Axes property of the Chart class
Code: 800A03EC

On the line:
With .Axes(xlCategory)

I'm running this test.vbe script from the Wndows Explorer. Any ideas?

Thanks!
 
Here is some code I wrote a couple of years ago that I know works. I now it
took me a long time to get working correctly. I believe my problem was the
..ACTIVATE and .SELECT wasn't in my original code.

Anoth probl,em is when a chart first gets creatted in is on a Chart
worksheet. The name of the chart gets changed whan you put in in a
Worksheet. Keeping the correct name is tricky.

For Each AllCharts In Worksheets(TemperatureSheetName).Shapes

ChartName = "Temp Chart" + Str(SerialNumber)

If InStr(AllCharts.Name, "Temp Chart") = 0 Then
AllCharts.Name = ChartName

Worksheets(TemperatureSheetName).ChartObjects(ChartName).Activate
ActiveChart.Axes(xlValue).Select
With ActiveChart.Axes(xlValue)
.MinimumScale = 90
.MaximumScale = 160
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With

Worksheets(TemperatureSheetName).Shapes(ChartName).Top = _
Worksheets(TemperatureSheetName). _
Rows((ChartRowOffset * (ModChartNumber)) + 1).Top
Worksheets(TemperatureSheetName).Shapes(ChartName).Left = _
Worksheets(TemperatureSheetName).Columns(MyColumnOffset).Left
End If

Next AllCharts
 
When automating Excel from vbscript you don't have access to either named
arguments or Excel's constants.

Try replacing xlCategory with its actual value (which you can find in the
Excel Object Browser: hit F2 in the Excel VBE)

Tim
 
Back
Top