Automation Power Point Chart Problem

[Rich]

When I use automation to create a chart in Powerpoint I have a strang
problem. I can use two sets of data that are exactly the same and one
will plot correctly and the other plots adding the value to the first
ones value. Can you help me figure out why? Any help is greatly
appreciated.

My data is in a table named tbData
date kansas texas
1/1/2008 4 4
1/2/2008 5 5
1/3/2008 3 3
1/4/2008 6 6

I am running a module with the following code in it:

'Be sure to add "Microsoft PowerPoint 11.0 Object Library" to the
references
'Be sure to add "Microsoft Graph 11.0 Object Library" to the
references
Option Compare Database
Public Function doCharts()
'--- declare any required objects and variables
Dim ppApp As PowerPoint.Application
Dim ppPres As PowerPoint.Presentation
Dim ppCurrentSlide As PowerPoint.Slide
Dim rst As DAO.Recordset
Dim DataField As DAO.Field
Dim sqlStr As String
Dim lHeight, lWidth, lLeft, lTop As Single
Dim shpGraph As Object
Dim oDataSheet As Object
Dim Data_FieldCount As Integer
Dim myRowCount As Integer

'--- open powerpoint and set a pointer from Access to it
Set ppApp = CreateObject("PowerPoint.Application")
'--- make it visible on your monitor
ppApp.Visible = True
'--- open the pre-designed template
Set ppPres = ppApp.Presentations.Open("c:\Presentation1.ppt")
'--- set a pointer to the first slide in the presentation
Set ppCurrentSlide = ppPres.Slides(1)

sqlStr = "Select Date, kansas, texas from tbData"

Set rst = CurrentDb.OpenRecordset(sqlStr) 'Open the table
lHeight = 450
lWidth = 680
lLeft = 60
lTop = 80

Set shpGraph = ppCurrentSlide.Shapes.AddOLEObject(Left:=lLeft,
Top:=lTop, _
Width:=lWidth, Height:=lHeight,
classname:="MSGraph.Chart", _
link:=0).OLEFormat.Object 'create the chart
shpGraph.ChartType = xlLineMarkersStacked 'set the chart type
shpGraph.Axes(1).TickLabels.Font.Size = 8 'set the font for the X
axis
shpGraph.Axes(1).TickLabels.Orientation = xlUpward 'Make the
labels on the X axis rotated
shpGraph.Axes(2).TickLabels.Font.Size = 8 'set the font for the Y
axis
shpGraph.Axes(2).TickLabels.NumberFormat = "0.00" 'set the format
for the numbers on the Y axis
shpGraph.Axes(1).HasTitle = True 'yes the chart has a label for
the X axis
shpGraph.Axes(1).AxisTitle.Font.Size = 8 'set the font for the X
axis label
shpGraph.Axes(1).AxisTitle.Text = "Month" 'add the X axis label
caption
shpGraph.Axes(2).HasTitle = True 'yes the chart has a label for
the Y axis
shpGraph.Axes(2).AxisTitle.Font.Size = 8 'set the font for the Y
axis label
shpGraph.Axes(2).AxisTitle.Text = "PTS" 'add the Y axis label
caption
shpGraph.Axes(2).AxisTitle.Left = 0 'set the location for the Y
axis label

shpGraph.PlotArea.Width = 720 'Make the plot area larger
shpGraph.PlotArea.Left = 35 'set the location for the left side of
the plot area

shpGraph.Legend.Font.Size = 8 'set the font size for the legend
shpGraph.Legend.Left = 65 'set the location for the left side of
the legend
shpGraph.Legend.Top = 20 'set the location for the top of the
legend

Set oDataSheet = shpGraph.Application.DataSheet 'Set the reference
to the datasheet collection
oDataSheet.Cells.Clear 'Clear the datasheet
Data_FieldCount = 1 'Lines to set up row headings

'Loop through the fields collection and get the field names
For Each DataField In rst.Fields
oDataSheet.Cells(Data_FieldCount, 1).Value =
rst.Fields(Data_FieldCount - 1).Name 'get the field name and add it to
the cell
Data_FieldCount = Data_FieldCount + 1 'increment the field
number
Next DataField 'go to the next field

myRowCount = 1 'Beginning row number

'Loop through the recordset
Do While Not rst.EOF 'while it is not the end of the data
Data_FieldCount = 1 'initialize

'Put the values for the fields in the dataset
For Each DataField In rst.Fields 'do for every field
oDataSheet.Cells(Data_FieldCount, myRowCount + 1).Value =
rst.Fields(Data_FieldCount - 1).Value 'get the field data and add it
to the cell
Data_FieldCount = Data_FieldCount + 1 'increment the field
number
Next DataField 'go to the next field
myRowCount = myRowCount + 1 'increment the row number
rst.MoveNext 'move to the next record in the recordset
Loop

'--- remove the pointers from memory
Set ppApp = Nothing
Set ppPres = Nothing
Set ppCurrentSlide = Nothing
End Function

 

[Rich]

never mind ... I figured it out.

I was using --> shpGraph.ChartType = xlLineMarkersStacked
when I should have been using -->shpGraph.ChartType = xlLineMarkers