Hi all,

I want to take data that is in one field and make a chart of it. Basically I want it to be a pie chart showing comparison between two values. The field stores a number one or two depending on the option that the user selects in a form. Do I somehow make a query that would separate these values. What should my approach be? Also there are other pie charts that I have. Basically I want the ability to take these charts and automatically update a powerpoint presentation with the correct information. Any help would be greatly appreciated.

Thanks,

Chris

Hi Chris,

Thought I'd tackle this bit. You wouldn't update a PPT presentation from the chart objects on a report, but rather from the data source the Access report charts share with a PPT chart on a slide. (You can also insert static snapshots or screen captures of Access reports into PPT slides, but the approach used below updates a PPT slide dynamically).

The following sub procedure accepts two arguments: the path/name of a saved PPT presentation, and a data source (I'm using an Access query).

PPT Presentation -

I created a new PPT presentation with 1 slide, inserted a Chart object and changed the Chart Type to Pie, then saved the PPT file, naming it PPTPresentation.ppt. That's all you need to do with the presentation. The code finds the PPT slide (I hardcoded the slide number - 1), finds the Pie Chart control (there can be only 1 chart on the slide for the code to work), and populates the chart from the Access query. Save the presentation with the updated data, and you're ready to present it.

Access data soure -

1. A table with the following data.

tblItems
2. A query based on tblItems:

qryItemCount

SELECT t.ItemText, Count(t.ItemText) AS ItemCount
FROM tblItems AS t
GROUP BY t.ItemText;

The query result set looks like:

When this result set is loaded into the PPT Pie Chart, you get two slices (78% "1's", 22% "2's")

Code Module -

Paste the following into a standard module and call it with:

Call UpdatePPTPieChart("C:\PPTPresentation.ppt", "qryItemCount")

You need a reference set to ADO.

********************************************

Sub UpdatePPTPieChart(strSavedPPTFileName As String, strDataSource As String)

    ' Power Point Application and Presentation variables
    Dim objPPTApplication As Object
    Dim objPowerPointSlide As Object
    Dim intShapeCount As Integer
    Dim blnFindPieChart As Boolean

    ' Reference variables to the Pie Chart object
    Dim objPieChart As Object
    Dim objDataSheet As Object
    Dim intFieldCount As Integer

    ' Data access object variables
    Dim cnn As ADODB.Connection
    Dim rst As ADODB.Recordset
    Dim fld As ADODB.Field
    Dim lngRowCount

    Set cnn = CurrentProject.Connection
    Set rst = New ADODB.Recordset
    rst.Open strDataSource, cnn, adOpenForwardOnly, adLockReadOnly, adCmdTable

    ' Open and activate an instance of PowerPoint
    Set objPPTApplication = CreateObject("Powerpoint.application")
    objPPTApplication.Activate

    'Load Slide 1 (or the slide containing your chart object)
    Set objPowerPointSlide = _
        objPPTApplication.Presentations.Open(strSavedPPTFi leName).Slides(1)
    blnFindPieChart = False

    ' Iterate through the Slide's Shapes collection looking for
    ' any MSGraph.Chart objects.
    For intShapeCount = 1 To objPowerPointSlide.Shapes.Count

        ' Type 7 = OLE object.
        If objPowerPointSlide.Shapes(intShapeCount).Type = 7 Then

            If objPowerPointSlide.Shapes(intShapeCount).OLEFormat .ProgId = _
                "MSGraph.Chart.8" Then
                Set objPieChart = _
                objPowerPointSlide.Shapes(intShapeCount).OLEFormat .Object

                ' The Pie Chart exists on the Slide
                blnFindPieChart = True
            End If
        End If
    Next intShapeCount

    If blnFindPieChart Then

    ' Set the reference to the PieChart's Datasheet collection.
    Set objDataSheet = objPieChart.Application.DataSheet
    objDataSheet.Cells.Clear

    ' Load the DataSheet cells from the ADO Recordset.
    intFieldCount = 1

    For Each fld In rst.Fields
        objDataSheet.Cells(intFieldCount, 1).Value = _
            rst.Fields(intFieldCount - 1).Name
        intFieldCount = intFieldCount + 1
    Next fld

    lngRowCount = 1

    Do While Not rst.EOF
        intFieldCount = 1

        For Each fld In rst.Fields
            objDataSheet.Cells(intFieldCount, lngRowCount + 1).Value = _
                rst.Fields(intFieldCount - 1).Value
            intFieldCount = intFieldCount + 1
        Next fld

        lngRowCount = lngRowCount + 1
        rst.MoveNext

    Loop

    ' Update the Pie Chart.
    objPieChart.Application.Update

    rst.Close
    cnn.Close

    End If

End Sub

After the code runs, Power Point will be open. Just save the updated presentation and you're ready to run it in Power Point.

'Course, the chart doesn't have to be a pie chart. The code will work for any chart type.

HTH,

Bob

Could you please tell me what versions of powerpoint and access is being used. i am using 2010 and it does not seem to recognize the ojbect reference .?I have added the references