Need help formatting excel line chart from access 1

[robojeff]

I am attempting to create a chart in excel from an access VB script
and am having some problems formatting some of the chart features.

Currently my code is :

Set objXLChart = .ChartObjects.Add(300, 0, 456, 300)
objXLChart.Activate

With objXLChart.Chart
.SeriesCollection.Add Source:=objXLBook.Sheets("Sheet1").Range("D1" & intLoop - 1)
.HasTitle = True
.ChartTitle.Text = Me.Nactteam & " Stage 3 FPY Data " & Format(Now(), "mm-yy")
.Legend.Position = xlLegendPositionBottom
End With
.Cells(1, 1).Activate

I ran the following macro in excel to format the line in my chart and set the scale
and then attempted to port these commands over to my VB script but am not able
to figure out the syntax for incorporating these macro commands to my VB scrip without getting errors on each one...

Sub Macro1()
'
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.SeriesCollection(1).Select
With Selection.Border
.ColorIndex = 5
.Weight = xlThick
.LineStyle = xlContinuous
End With

With ActiveChart.Axes(xlValue)
.MinimumScaleIsAuto = True
.MaximumScale = 100
.MinorUnitIsAuto = True
.MajorUnitIsAuto = True
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With
End Sub

What am I doing wrong?

Also, is there any tutorials out there on the chart options and fields that can be set?

Thanks

 

[SkipVought]

Hi,

On what statement do you get an error and exactly what error message?

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[robojeff]

SkipVought (Programmer) 15 Jan 09 15:09
Hi,

On what statement do you get an error and exactly what error message?
Skip,

Hi Skip-
I get a "Compile error: Method or data member not found"
on the following lines that are in bold text:

With objXLChart.Chart
.ChartType = xlLine
.ColorIndex = 5
.Weight = xlThick
.LineStyle = xlContinuous
.ChartArea.Border.LineStyle = xlNone
.MinimumScaleIsAuto = True
.MaximumScale = 100
.MinorUnitIsAuto = True
.SeriesCollection.Add Source:=objXLBook.Sheets("Sheet1").Range("D1" & intLoop - 1)
.HasTitle = True
.ChartTitle.Text = Me.Nactteam & " Stage 3 FPY Data " & Format(Now(), "mm-yy")
.Legend.Position = xlLegendPositionBottom
End With

Thanks

 

[SkipVought]

.ColorIndex = 5
.Weight = xlThick
.LineStyle = xlContinuous
all refer to a graphic object in the chart, like a border of some sort.

.ChartArea.Border.LineStyle = xlNone
try
.ChartArea.Border.LineStyle = xlLineStyleNone


.MinimumScaleIsAuto = True
.MaximumScale = 100
.MinorUnitIsAuto = True
all refer to an Axis object


Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[robojeff]

SkipVought (Programmer) 15 Jan 09 16:37

.ColorIndex = 5
.Weight = xlThick
.LineStyle = xlContinuous
all refer to a graphic object in the chart, like a border of some sort.

.ChartArea.Border.LineStyle = xlNone

try

.ChartArea.Border.LineStyle = xlLineStyleNone

.MinimumScaleIsAuto = True
.MaximumScale = 100
.MinorUnitIsAuto = True
all refer to an Axis object

Hi Skip-

Sorry, but I didn't totally understand your response because this charting stuff is pretty foriegn to me and I am a little confused...

I am not sure what syntax that I need to use to make the following code to format the line in my chart:

.ColorIndex = 5
.Weight = xlThick
.LineStyle = xlContinuous

or what syntax that I need to use to make the scaling of the chart features with the following lines of code:

.MinimumScaleIsAuto = True
.MaximumScale = 100
.MinorUnitIsAuto = True

These all work within the Excel spread sheet with this macro but they do not port over to the VB without a compile error so I must be missing something.

The line:

.ChartArea.Border.LineStyle = xlLineStyleNone

does not receive the compile error but I am not sure what it is doing as I am not seeing any indication of anything changing with or without it...

thank you for your patience..

 

[PHV]

Typed, untested:

...
  Set objXLChart = .ChartObjects.Add(300, 0, 456, 300)
  objXLChart.Activate
  With objXLChart.Chart
    .SeriesCollection.Add Source:=objXLBook.Sheets("Sheet1").Range("D1:D" & intLoop - 1)
    .HasTitle = True
    .ChartTitle.Text = Me.Nactteam & " Stage 3 FPY Data " & Format(Now(), "mm-yy")
    .Legend.Position = xlLegendPositionBottom
    With .SeriesCollection(1).Border
      .ColorIndex = 5
      .Weight = xlThick
      .LineStyle = xlContinuous
    End With
    With .Axes(xlValue)
      .MinimumScaleIsAuto = True
      .MaximumScale = 100
      .MinorUnitIsAuto = True
      .MajorUnitIsAuto = True
      .Crosses = xlAutomatic
      .ReversePlotOrder = False
      .ScaleType = xlLinear
      .DisplayUnit = xlNone
    End With
  End With
  .Cells(1, 1).Activate
...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[robojeff]

thanks PH-

Your post was very helpful!

A few of these commands do not work but with a little playing around with them I was able to get the parameters that I need to work.

There is one additional paramter that I would also like to set up and have been trying to get it to work but have not had much luck... this is setting the x values on the bottom of the chart to items in the spread sheet shown in Colum 1, rows 2 - 13.

When using excel to create a macro it sets up the code:

ActiveChart.SeriesCollection(1).XValues = "=Sheet1!R2C1:R13C1"

I modified this to:
.SeriesCollection.XValues = "=Sheet1!R2C1:R13C1"
but get an error stating, "Object doesn't support this property or method"

I received an Application-defined or object-defined error when I tereied the following:

.SeriesCollection.XValues = objXLBook.Sheets("Sheet1").Range("R2C1:R13C1")

What is the correct syntax for this?

thanks again

 

[PHV]

What about this ?
.SeriesCollection[!](1)[/!].XValues = "=Sheet1!R2C1:R13C1"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[robojeff]

Thanks that did the trick!