如何使用VBA动态引用PowerPoint幻灯片 [英] How to dynamically reference PowerPoint slides using VBA

问题描述

我已经编写/编译了一个宏，该宏可打开Excel文件，创建PowerPoint图表，并使用Excel文件中工作表中的数据填充图表工作表.

I've written/compiled a macro that opens an Excel file, creates a PowerPoint chart and populates the chart worksheet with data from a worksheet in the Excel file.

我正在尝试更改宏以遍历Excel文件的工作表，并且:

I'm trying to alter the macro to loop through the Excel file's worksheets and:

1. 为每个工作表创建一张PowerPoint幻灯片和图表
2. 使用来自Excel文件中工作表中的数据填充PowerPoint图表

当前，当我运行宏时，将正确创建第一个PowerPoint图表和幻灯片.为Excel文件的第二个工作表创建了第二张幻灯片，但是PowerPoint图表没有正确创建.我正在测试宏的工作簿有两个工作表.

Presently when I run the macro, the first PowerPoint chart and slide is created correctly. The second slide is created for the Excel file's second worksheet but the PowerPoint chart is not created correctly. The workbook that I'm testing the macro on has two worksheets.

动态引用每张新的PowerPoint幻灯片的正确方法是什么?到目前为止，我一直在使用:

What is the correct way to dynamically reference each new PowerPoint slide? As of now I've been using:

Set pptWorkSheet = pptWorkBook.Worksheets(ActivePresentation.Slides.Count) 'sorta works-changed 8/19

当我进入调试器时，它显示为 ActivePresentation.Slides.Count = 2 ，因此我不确定为什么不将数据传输到第二张PowerPoint图表中.

When I go to the debugger it says ActivePresentation.Slides.Count = 2 so I am not sure as to why its not transferring the data to the second PowerPoint chart.

在这里，我可能还没有正确地引用Excel文件工作表:

I also may not be referring to the Excel file worksheets correctly here:

pptWorkSheet.Range("a2:b5").Value = xlWB.ActiveSheet.Range("a2:b5").Value

下面是完整的宏:

Sub CreateChartAllWKs()

'Create variables
    Dim myChart As Chart
    Dim pptChartData As ChartData
    Dim pptWorkBook As Excel.Workbook
    Dim pptWorkSheet As Excel.Worksheet
    Dim xlApp As Excel.Application
    Dim xlWB As Workbook
    Dim xlWS As Worksheet  

' Create new excel instance and open relevant workbook
    Set xlApp = New Excel.Application
    xlApp.Visible = True 'Make Excel visable
    Set xlWB = xlApp.Workbooks.Open("C:\filepath\ExcelData.xlsm", True, False)  'Open relevant workbook

'Loop through each worksheet in xlWB and transfer data to new pptWorkBook and
'create new PowerPoint chart
    For Each xlWS In ActiveWorkbook.Worksheets

        'Add a new slide where we will create the PowerPoint worksheet and chart
            ActivePresentation.Slides.Add ActivePresentation.Slides.Count + 1, ppLayoutText
            ActiveWindow.View.GotoSlide ActivePresentation.Slides.Count
            Set activeSlide = ActivePresentation.Slides(ActivePresentation.Slides.Count)

        ' Create the chart and set a reference to the chart data.
            Set myChart = activeSlide.Shapes.AddChart.Chart 'changed 8/19
            Set pptChartData = myChart.ChartData

        ' Set the PowerPoint Workbook and Worksheet references.
            Set pptWorkBook = pptChartData.Workbook
            Set pptWorkSheet = pptWorkBook.Worksheets(ActivePresentation.Slides.Count) 'sorta works-changed 8/19

        ' Add the data to the PowerPoint workbook.
            pptWorkSheet.ListObjects("Table1").Resize pptWorkSheet.Range("A1:B5")
            pptWorkSheet.Range("Table1[[#Headers],[Series 1]]").Value = "Items"
            pptWorkSheet.Range("a2:b5").Value = xlWB.ActiveSheet.Range("a2:b5").Value 'transfer data from ExcelWB to pptWorkSheet (i.e. the PowerPoint workbook)

        ' Apply styles to the chart.
            With myChart
                .ChartStyle = 4
                .ApplyLayout 4
                .ClearToMatchStyle
            End With

        ' Add the axis title.
            With myChart.Axes(xlValue)
                .HasTitle = True
                .AxisTitle.Text = "Units" 
            End With

        'Apply data labels
            myChart.ApplyDataLabels
   Next xlWS

' Clean up the references.
    Set pptWorkSheet = Nothing
' pptWorkBook.Application.Quit
    Set pptWorkBook = Nothing
    Set pptChartData = Nothing
    Set myChart = Nothing
'Clean up Excel references.
    Set xlApp = Nothing
'Option to close excel workbook
    'ExcelWB.Close
End Sub

推荐答案

我认为您遇到的问题是PowerPoint和Excel如何存储幻灯片编号和工作表编号.PowerPoint带有幻灯片的至少3个不同属性，包括幻灯片ID"，幻灯片索引"和幻灯片编号".它们都是不同的，使您在尝试引用它们时感到很痛苦.我想做的实际上是在创建幻灯片时正确设置幻灯片的引用:

I think the problem you are running into is how PowerPoint and Excel store slide numbers and worksheet numbers. PowerPoint at least 3 different attributes with Slides, including "Slide IDs", "Slide Indexes" and "Slide Numbers". They are all different and make things a pain when you are trying to reference them. What I like to do is actually set the reference of the slide right when I am creating the slide:

Set CurSlide = ActivePresentation.Slides.Add(ActivePresentation.Slides.Count + 1, ppLayoutText)

这样一来，当您创建幻灯片时，您现在就可以对其进行引用了.

This way right when you create the slide you now have a reference to it.

此外，我发现使用数字作为工作表引用也会引起问题，因为如果您引用第5个工作表，则可能根本不是第5个工作表.您必须查看Excel的VBA编辑器，以查看哪些工作表获得了什么参考.但是，如果您能够引用工作表名称，例如"Sheet1"，"Sheet2"，"OtherWorksheet"等，则可以使事情变得容易得多.如果您制作一个名为"5"的工作表，然后使用它来调用工作表，则可以更清楚地了解这一点.

Additionally I find that using a number as a worksheet reference can also cause issues since if you reference the 5th worksheet it may not be the 5th worksheet at all. You have to look in the VBA editor of Excel to see what sheet gets what reference. However if you are able to refer to the worksheet name such as "Sheet1", "Sheet2", "OtherWorksheet" etc. You can make things a lot easier. To put this a little more in perspective if you make a sheet named "5" and then call the worksheet with.

Set ws = ActiveWorkBook.WorkSheets(5)

它不起作用.您将需要使用

It will not work. You would need to use

Set ws = ActiveWorkBook.Worksheets("5")

希望这是有道理的.这部分不是必需的，但是如果遇到问题，它会使调试变得容易得多.我建议这样做的方法不在下面的代码中，因为我没有您的工作簿.

Hopefully that makes sense. This part is not necessary but it makes debugging a lot easier if you do run into issues. The way I would recommend to do this is not in my code below because I don't have your workbook.

Set PPtWorkSheet = pptWorkBook.Worksheets("Sheet" & CurSlide.SlideIndex) 

我重新编写了几行代码，并使其能够正常工作.但是，我没有您的工作簿的副本，因此我不是100％确信这会起作用.如果仍然无法从幻灯片索引中引用工作表，请考虑更改工作簿上的工作表名称.

I re-wrote a few lines of your code and I was able to get it to work. However I do not have a copy of your workbook so I am not 100% sure this would work. Consider changing the worksheet names on your workbook if you still have trouble referencing the worksheet from the slide Index.

下面是经过修改的代码，如果您有任何疑问，请通知我.

The revised code is below let me know if you have any questions.

Sub CreateChartAllWKs()

'Create variables
        Dim myChart As Chart
        Dim pptChartData As ChartData
        Dim pptWorkBook As Excel.Workbook
        Dim pptWorkSheet As Excel.Worksheet
        Dim xlApp As Excel.Application
        Dim xlWB As Excel.Workbook
        Dim xlWS As Excel.Worksheet
        Dim CurSlide As Slide 'new from update

' Create new excel instance and open relevant workbook
        Set xlApp = New Excel.Application
        xlApp.Visible = True 'Make Excel visable
        Set xlWB = xlApp.Workbooks.Open("C:\filepath\ExcelData.xlsm", True, False)  'Open relevant workbook

'Loop through each worksheet in xlWB and transfer data to new pptWorkBook and
'create new PowerPoint chart
        For Each xlWS In ActiveWorkbook.Worksheets

                'Add a new slide where we will create the PowerPoint worksheet and chart
                        'Set CurSlide = ActivePresentation.Slides.Add ActivePresentation.Slides.Count + 1, ppLayoutText
                        ActiveWindow.View.GotoSlide ActivePresentation.Slides.Count
'This is my recommendation
                        Set CurSlide = ActivePresentation.Slides.Add(ActivePresentation.Slides.Count + 1, ppLayoutText)

                ' Create the chart and set a reference to the chart data.
                        Set myChart = CurSlide.Shapes.AddChart.Chart 'changed 8/19
                        Set pptChartData = myChart.ChartData

                ' Set the PowerPoint Workbook and Worksheet references.
                        Set pptWorkBook = pptChartData.Workbook
                        Set pptWorkSheet = pptWorkBook.Worksheets(CurSlide.SlideIndex) 'From Update

                ' Add the data to the PowerPoint workbook.
                        pptWorkSheet.ListObjects("Table1").Resize pptWorkSheet.Range("A1:B5")
                        pptWorkSheet.Range("Table1[[#Headers],[Series 1]]").Value = "Items"
                        pptWorkSheet.Range("a2:b5").Value = xlWB.ActiveSheet.Range("a2:b5").Value 'transfer data from ExcelWB to pptWorkSheet (i.e. the PowerPoint workbook)

                ' Apply styles to the chart.
                        With myChart
                                .ChartStyle = 4
                                .ApplyLayout 4
                                .ClearToMatchStyle
                        End With

                ' Add the axis title.
                        With myChart.Axes(xlValue)
                                .HasTitle = True
                                .AxisTitle.Text = "Units"
                        End With

                'Apply data labels
                        myChart.ApplyDataLabels
     Next xlWS

' Clean up the references.
        Set pptWorkSheet = Nothing
' pptWorkBook.Application.Quit
        Set pptWorkBook = Nothing
        Set pptChartData = Nothing
        Set myChart = Nothing
'Clean up Excel references.
        Set xlApp = Nothing
'Option to close excel workbook
        'ExcelWB.Close
End Sub

这篇关于如何使用VBA动态引用PowerPoint幻灯片的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！