如何在基于excel数据的Powerpoint中创建和格式化各种图表？ [英] How to create and format various charts in powerpoint based on excel data?

问题描述

我一直在试图找到一个解决方案，无论是宏还是简单的解决方案，都可以在powerpoint演示中创建和格式化图表。到目前为止，我找不到任何解决我的问题。
这个想法是从一个相当大的excel文件源数据，然后在几个powerpoint幻灯片上创建几个图表。也就是说，一个大的excel文件和10个powerpoint幻灯片，每个幻灯片上有8个独立的图表。
我试过这个： http://mahipalreddy.com/vba.htm#ppgraph

解决方案

如何解决这个问题？ div>

这是我将使用的方法：

1. 使用插入图表在PPT中初始设置图表。


2. 然后从VBA中，为每个图表收集来自Excel源文件
   的数据并将数据存储在 array 变量。


3. 使用这些变量更新图表的系列数据（或者更新powerpoint图表的嵌入工作表 .ChartData ）。

还有其他方法像使用OLEObjects链接/嵌入，但坦率地说，这些是一个痛苦的工作，如果文件在共享驱动器上，如果它们被移动或重命名等。

这里是上面描述的一般框架。

这将需要大量的修改，例如这是配置只有1张幻灯片上的1个图表，我不知道你的数据如何Excel是排列的，所以我只是放入一些虚拟代码，以显示如何从Excel中捕获一些值，你很显然需要微调，与大量的代码，使其足够动态，以适用于所有的图表如果你的数据组织得很好，你知道你的方式Excel VBA）。

  Option Explicit 
 Option Base 1 
 
 Sub GetChartDataFromXLS（）
 Dim wbFileName As String'## full filename& '
 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（）作为每个系列的Variant'##临时数组，将存储在chtData数组。'
 Dim chtData（）作为Variant'##我将使用这个数组来存储Excel文件中的几个数组'
 Dim s As Long 
 
 wbFileName =C：\users\david_zemens \desktop\dummy chart data.xlsx
 
设置oXL = CreateObject（Excel.Application）
 oXL.Visible = True 
 
设置xlWB = oXL.Workbooks.Open（wbFileName）
 
'##遍历幻灯片中的形状'
 For每个shp在ActivePresentation.Windows （1）.Selection.SlideRange（1）.Shapes 
'##检查这个形状是否是图表'
如果shp.HasChart然后
'##设置图表变量。'
设置cht = shp.Chart 
 
'##清除图表中的任何现有系列数据
对于s = cht.SeriesCollection.Count至1步骤-1 
设置srs = cht.SeriesCollection 
 srs.Delete 
接下来
 
'##获取chtData的代码将在这个块中：'
'## 
设置xlWS = xlWB.Sheets（1）'##修改以获取此图表数据所在的正确表单
'##这可能是这样的，'
'遍历一些列，并将数据收集到一系列'
'数组中，存储在chtData数组'
 
对于x = 1到3'你需要添加的系列：'
'假设数据序列从A列开始，等等...'
c = 1 
对于每个cl在xlWS.Range（A1：A10）。 Offset（0，x  -  1）
 ReDim保存sArray（c）
 sArray（c）= cl.Value 
c = c + 1 
 
 b'ReDim保存chtData数组
 ReDim保存chtData（x）
 chtData（x）= sArray 
 
下一个x 
'##结束图表数据的收集。 
 
'##公开数据表，但最小化以保持更新
 cht.ChartData.Activate 
 cht.ChartData.Workbook.Application.WindowState = -4140 
 
'##现在，获取数据并将其插入图表
如果LBound（chtData）> = 1则
对于s = LBound（chtData）到UBound（chtData）
'##向图中添加一个新系列
设置srs = cht.SeriesCollection.NewSeries 
 srs.Values = chtData（s）'##修改此行以指向适当的数组从chtData '
'操纵其他系列属性这里'
'srs.Name =无论系列名称'
'srs.XValues =无论系列值'
' ＃etc ... 
'＃etc ... 
下一个系列... 
结束如果
 
'##关闭chartdata表。 
 cht.ChartData.Workbook.Close 
如果
结束如果
 
 oXL.ActiveWorkbook.Close 
 oXL.Quit 
 On Error Resume Next 
设置oXL =无
设置xlWB =无
设置xlWS =无
出现错误GoTo 0 
结束子
  

此方法不写入图表的数据表。坦率地说，我认为作为一个不必要的步骤，如果你创建一个宏驱动仪表板，不应该有任何理由需要数据表，但如果这是需要的某种原因，我们可以修改图表的系列的创建方式。

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?

解决方案

This is the approach I would use:

1. Set up the charts initially in PPT using Insert Chart.
2. Then from VBA, for each chart collect the data from the Excel source file and store the data in array variables.
3. 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.

这篇关于如何在基于excel数据的Powerpoint中创建和格式化各种图表？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！