How to create and format various charts in powerpo

I have been trying to find a solution, be it a macro or a simple solution, to create and format charts in a powerpoint presentation. So far I could not find anything that would solve my issue. The idea is to source data from a fairly big excel file and then create several charts on several powerpoint slides. That is, one big excel file and 10 powerpoint slides, with 8 individual charts on each slide. I tried this: http://mahipalreddy.com/vba.htm#ppgraph, but that did not help at all.

How can I solve this?

回答1:

This is the approach I would use:

Set up the charts initially in PPT using Insert Chart.
Then from VBA, for each chart collect the data from the Excel source file and store the data in array variables.
Use these variables to update the chart's series data (alternatively update the powerpoint chart's embedded worksheet .ChartData).

There are other methods like using OLEObjects to link/embed, but frankly those are a pain to work with, and can pose problems if the file(s) are on a shared drive, if they're moved or renamed, etc.

Here is the general framework I describe above.

This will require a good amount of modification on your end -- for example this is configured only for 1 chart on 1 slide, and I have no idea how your data in Excel is arranged, so I just put in some dummy code to show how I would capture some values from Excel, you'll obviously need to fine tune that with a good amount of code so that it is dynamic enough to work on all charts (this can be done easily enough if your data is organized well, and you know your way around Excel VBA).

Option Explicit
Option Base 1

Sub GetChartDataFromXLS()
Dim wbFileName As String '## full filename & path of the Excel file.'
Dim oXL As Object
Dim xlWB As Object
Dim xlWS As Object
Dim cl As Object
Dim c As Long
Dim shp As Shape
Dim cht As Chart
Dim srs As Series
Dim x As Long
Dim sArray() As Variant '## temporary array for each series, will be stored in chtData array.'
Dim chtData() As Variant '## I would use this array to store several arrays from the Excel file.'
Dim s As Long

wbFileName = "C:\users\david_zemens\desktop\dummy chart data.xlsx"

Set oXL = CreateObject("Excel.Application")
oXL.Visible = True

Set xlWB = oXL.Workbooks.Open(wbFileName)

'## iterate over the shapes in the slide.'
For Each shp In ActivePresentation.Windows(1).Selection.SlideRange(1).Shapes
    '## check to see if this shape is a chart.'
    If shp.HasChart Then
        '## set the chart variable.'
        Set cht = shp.Chart

        '## clear out any existing series data in the chart'
        For s = cht.SeriesCollection.Count To 1 Step -1
            Set srs = cht.SeriesCollection(s)
            srs.Delete
        Next

        '##Your code to get the chtData will go in this block:'
        '##
        Set xlWS = xlWB.Sheets(1) ' ##Modify to get the correct sheet where the data for this chart resides'
        '## It will probably be something like this, which '
        '   iterates over some columns and collects data in to a series'
        '   of arrays, stored within chtData array '

        For x = 1 To 3 'However Many Series you need to add:'
            'Assuming data series begins in column A, etc...'
            c = 1
            For Each cl In xlWS.Range("A1:A10").Offset(0, x - 1)
                ReDim Preserve sArray(c)
                sArray(c) = cl.Value
                c = c + 1

            Next
            'ReDim Preserve the chtData array
            ReDim Preserve chtData(x)
            chtData(x) = sArray

        Next x
        '## End collection of the chart data.

        '## Expose the data sheet but minimize it to preserve updating
        cht.ChartData.Activate
        cht.ChartData.Workbook.Application.WindowState = -4140

        '## Now, take that data and insert it to the chart
        If LBound(chtData) >= 1 Then
            For s = LBound(chtData) To UBound(chtData)
                '## Add a new series to the chart
                Set srs = cht.SeriesCollection.NewSeries
                    srs.Values = chtData(s)  '## Modify this line to point at the appropriate array from chtData'
                    'manipulate the other series properties here '
                    'srs.Name = "whatever the series name"  '
                    'srs.XValues = "whatever the series value"   '
                    '# etc...
                    '# etc...
            Next 'Next series...
        End If

        '## Close the chartdata sheet.
        cht.ChartData.Workbook.Close
    End If
Next

oXL.ActiveWorkbook.Close
oXL.Quit
On Error Resume Next
Set oXL = Nothing
Set xlWB = Nothing
Set xlWS = Nothing
On Error GoTo 0
End Sub

This method does not write to the chart's data sheet. Frankly I see that as an unnecessary step if you are creating a macro-driven dashboard, there should not be any reason to need the data sheet, but if that is needed for some reason, we can modify the way the chart's series are created.

回答2:

Another method would be to use a free charting plugin for PowerPoint called oomfo @ http://oomfo.com

Using oomfo, you can build charts that are connected to live Excel sheets. Once you've built a chart connected to the Excel data source, whenever the Excel worksheet gets updated, and the presentation is viewed, the charts automatically pull in the latest data. You'll just need to ensure that PowerPoint should have access to that Excel file (either locally or remotely).

Link to documentation of Excel data source is at http://docs.oomfo.com/charts/1.0/contents/chart_data/data_excel.html