Charting with MSChart

Does it have to a be a MSChart ? Or is an Excel object an option too ?

I'm a little short on time right now, but here is some sample code from Microsoft. KBase article number is Q176443 (http://support.microsoft.com/support/kb/articles/Q176/4/43.ASP)

Sub CreateGraphFromFile()
    Dim oGraph As Object
    Dim oDataSheet As Object
    Dim shpGraph As PowerPoint.Shape
    Dim lRowCnt As Long
    Dim lColCnt As Long
    Dim lValue As Long   ' Add the graph to the slide.
    With ActivePresentation.Slides(1)
        ' Create the Graph object on the slide.
        Set shpGraph = .Shapes.AddOLEObject(ClassName:="MSGraph.Chart", _
                                          Link:=msoFalse)
    End With
    ' Set references.
    Set oGraph = shpGraph.OLEFormat.Object
    Set oDataSheet = oGraph.Application.DataSheet
    ' Place headers into data table.
    ' Create four column headers.
    oDataSheet.Cells(1, 2).Value = "Col1"
    oDataSheet.Cells(1, 3).Value = "Col2"
    oDataSheet.Cells(1, 4).Value = "Col3"
    oDataSheet.Cells(1, 5).Value = "Col4"
    ' Create four row headers.
    oDataSheet.Cells(2, 1).Value = "Row1"
    oDataSheet.Cells(3, 1).Value = "Row2"
    oDataSheet.Cells(4, 1).Value = "Row3"
    oDataSheet.Cells(5, 1).Value = "Row4"

    ' We already know the number of columns and rows
    ' from the data we are importing.
    Open "<YourPathAndFileNameHere>" For Input As #1
    ' Get four rows of data.
    For lRowCnt = 2 To 5
        For lColCnt = 2 To 5
            ' Get value from file.
            Input #1, lValue
            ' Place in data table.
            oDataSheet.Cells(lRowCnt, lColCnt).Value = lValue
        Next lColCnt
    Next lRowCnt
    Close #1   ' Release the reference.
    Set oGraph = Nothing
    Set oDataSheet = Nothing
End Sub

A while ago I did a comparison programming project in VB3, using the graphing control that came with VB3 Professional, MSGraph and Excel. Excel was the most powerful, but came with a lot of overhead. The VBX control was the best performer but the offered the least. So, IMHO, MSGraph 5 was the best, by not coming with too much overhead while still offering power. I never repeated the experiment with later versions of VB.

Microsoft used to offer a help file and type library to ease development with VBA and MSGraph. I suspect they still do.

If you get stuck, I'll try to spend more time on this later.

Sorry, but I dont know an ActiveX control which does charts.

Just found the VBA help file for Office 97 (Graph 8). It's called Vbagrp8.hlp and its located in the VALUPACK folder of the Office 97 CD ROM.

You might want to take a look at that help file if you have the CD.

Cri, thanks.  I really want an MS Chart.

I should have mentioned this earlier, but I am using VB6 and Office 2000.

KevinW1,

Your solution looks promising.

Now I only need a way to select a chart from a slide in an existing powerpoint.
(The slide in the powerpoint contains one chart)

As VB6, there is a Visual Basic section in E-E, VBA/Powerpoint is a (microsofty) subset only.

Ok, below we have a little exploratory fun code. I wrote it, then stepped through it, trying out various permutations.

It contains three constants. The first, bNEW_FILE, indicates that whether or not this run-through will create a new file or open an existing file. The second, bNEW_CHART, indicates whether or not you are creating a new chart or referring to an existing chart (this, I believe, was the heart of your last comment). The last, sEXISTINGFILE, is the name of an existing PowerPoint presentation, used when you set the first constant to False.

I marked the code I think you are most interested in with lots of asterisks.

Dont forget to make a reference to the PowerPoint object library (and the MSGraph library if you are using the previous code I submitted).

' ----------------------------------
Function ufnPowerPointChart() As Long
    Dim oApp As PowerPoint.Application
    Dim oSl As PowerPoint.Slide
    Dim oShape As PowerPoint.Shape

    ' Two constants help us play with this code. We can use
    ' the first to open an existing presentation or create
    ' a new presentation. For the second constant, we can
    ' choose to create a new chart, if we are opening an
    ' existing chart (we will have to create a new chart
    ' for a new presentation).
    Const bNEW_FILE = False
    Const bNEW_CHART = (bNEW_FILE Or False)
    Const sEXISTINGFILE = "D:\Data\PPt97\AF2.ppt"

    ' Two constants to help determine if the shape object
    ' contains a graph. This is only necessary if you dont
    ' predefine the shape name.
    Const OLE_EMBED = 7
    Const OLE_LINK = 10

    Set oApp = New PowerPoint.Application

    If bNEW_FILE Then
        ' You set the constant to create a new presentation.
        oApp.Presentations.Add
        Set oSl = oApp.ActivePresentation.Slides.Add(1, ppLayoutBlank)
   
    Else
        ' You set the constant to open an existing presentation.
        oApp.Presentations.Open sEXISTINGFILE
        Set oSl = oApp.ActivePresentation.Slides(1)
    End If

    If bNEW_CHART Then
        ' You set the constant to create a new chart.
        ' (Or you have a new presentation, which obviously
        ' couldnt have a chart already.)
        Set oShape = oSl.Shapes.AddOLEObject( _
                        Left:=120, _
                        Top:=110, _
                        Width:=480, _
                        Height:=320, _
                        ClassName:="MSGraph.Chart.8" _
                        )
        ' This is useful. We can close this presentation
        ' and reopen it later, yet we can still refer to
        ' the shape containing the graph by this name.
        oShape.Name = "MyGraph"

    Else
        ' ************************
        ' This is where you get an
        ' existing chart from an
        ' existing presentation.
        ' ************************
        ' ************************
        ' So you chose to get a previously created chart.
        Dim sh As PowerPoint.Shape

        ' Did you previously name the chart? If not, this
        ' will trigger an error. You could always handle
        ' the error, then return and go to the next code.
        Set oShape = oSl.Shapes("MyGraph")

        For Each sh In oSl.Shapes
            If (sh.Type = OLE_EMBED) Or (sh.Type = OLE_LINK) Then
                If TypeName(sh.OLEFormat.Object) = "Chart" Then
                    ' This is a graph.
                    Exit For
                Else
                End If
            Else
            End If
        Next

    End If

    oApp.ActivePresentation.Save
    oApp.ActivePresentation.Close

    Set oShape = Nothing
    Set oSl = Nothing
    Set oApp = Nothing

End Function
' ----------------------------------

Let me know how this turns out.

By the way, this code will run in VB6 or in some other Microsoft Office application. I tested it in MS Word 97 for the those purists who believe that they should only see VBA code here. (I also tested it in VB6.)

Dirk,

Here's code that actually changes the values that the chart is based on.

Private Sub Command1_Click()
  'For this to work, set references to
  'MS PowerPoint 9.0 Object Library
  'and to MS Graph 9.0 Object Library

  'Declare variables  
  Dim pp As New PowerPoint.Application
  Dim pres As PowerPoint.Presentation
  Dim ch As Graph.Chart
 
  'Open up a presentation
  Set pres = pp.Presentations.Open("c:\test\test.ppt")
 
  'I assume that the chart is the second object on the first slide
  Set ch = pres.Slides(1).Shapes(2).OLEFormat.Object
 
  'Change cell values
  ch.Application.DataSheet.Cells(2, 2).Value = 60
  ch.Application.DataSheet.Cells(2, 3).Value = 65
  ch.Application.DataSheet.Cells(2, 4).Value = 70
   
  'Save the presentation and quit PowerPoint
  pres.Save
  pp.Quit

  'Clear object variables
  Set ch = Nothing
  Set pres = Nothing
  Set pp = Nothing
End Sub

Ture Magnusson
Karlstad, Sweden

Dirk,

When testing the code again, I found that it generated an error unless PowerPoint was made visible. This code should work better...

Private Sub Command1_Click()
  'For this to work, set references to
  'MS PowerPoint 9.0 Object Library
  'and to MS Graph 9.0 Object Library

  'Declare variables
  Dim pp As New PowerPoint.Application
  Dim pres As PowerPoint.Presentation
  Dim ch As Graph.Chart
 
  'Show PowerPoint
  pp.Visible = True
   
  'Open up a presentation
  Set pres = pp.Presentations.Open("c:\test\test.ppt")
   
  'I assume that the chart is the second object on the first slide
  Set ch = pres.Slides(1).Shapes(2).OLEFormat.Object
   
  'Change cell values
  ch.Application.DataSheet.Cells(2, 2).Value = 60
  ch.Application.DataSheet.Cells(2, 3).Value = 65
  ch.Application.DataSheet.Cells(2, 4).Value = 70
     
  'Save the presentation and quit PowerPoint
  pres.Save
  pp.Quit

  'Clear object variables
  Set ch = Nothing
  Set pres = Nothing
  Set pp = Nothing
End Sub

/Ture

Kevin,

Thank you for your code.
It works well, and solves my problem.

One final question though : how do I name an object or shape from PowerPoint ?

Ture, Thank you for you help, though it was basically repeating what Kevin had mentioned before.

I will give the points to Kevin, as soon as my last question is answered.

Thank You Both !

- Dirk

djsteven, sorry to use your question, but ture, after pouncing on every question, became an occasional visitor...

ture, https://www.experts-exchange.com/jsp/qShow.jsp?ta=msoffice&qid=10243092  I tried to rouse you through your homepage is still open. Besides the points adjusted to 150, it has a certain 'potential'.

Thanks, cri! I'll take a look at it...

/Ture

Question:
How do I name an object or shape from PowerPoint?

Answer:
Except through VBA code, as shown above, you don't.

If you go the VBA route, just set the Name property. You don't need to

do this at the point of creation, as long as you can identify the

correct Shape object. The value of the Name property does persist,

again as shown in my code above.

Let me know if this helps.

Thanks Kevin

- Dirk