Is there a way to tell PowerPoint not to open Exce

Question

Slide.Shapes.AddChart() automatically opens Excel. Even if I quickly do Chart.ChartData.Workbook.Application.Visible = false, it still shows a little while. This makes automating chart creation error-prone as the user has to try not to touch the Excel applications that keeps popping up.

Opening a presentation with WithWindow = false will still open Excel when creating new charts.

Answer 1

I would suggest another methdology to over come the same.

1. In the powerpoint VBA add refrences to "Microsoft Excel 12.0 Object Library"

2. Ensure the user that for this operation none of the excel must be open via yuser form popup before the operation.

3. In the VBA create an excel and set its parameters in the following code

4. Add the powerpoint chart, the user wouldnt be able to see the opening of the underlying excel sheet upon adding chart excet the excel tabs which can be controled via code.

Sample Code:

Option Explicit

Sub AddExcelChartSample()

    Dim xlApp As Excel.Application, xlWkbk As Excel.Workbook

    Dim pres As PowerPoint.Presentation, sld As PowerPoint.Slide, iCount As Integer, chtShape As PowerPoint.Shape

    'Open up the excel instance and set parameters


    Set xlApp = New Excel.Application
    With xlApp
        .WindowState = xlNormal
        .Top = -1000
        .Left = -1000
        .Height = 0
        .Width = 0
    End With


    Set sld = PowerPoint.ActiveWindow.View.Slide



    For iCount = 1 To 10

        Set chtShape = sld.Shapes.AddChart(xlLine)
        Set xlWkbk = chtShape.Chart.ChartData.Workbook
        With xlWkbk

            .Sheets(1).Range("A2").Value = "Test 1"
            .Sheets(1).Range("A3").Value = "Test 2"
            .Sheets(1).Range("A4").Value = "Test 3"

        End With

        chtShape.Chart.Refresh

        xlWkbk.Close False


    Next iCount


    xlApp.Quit

End Sub

Answer 2

This behavior is "by design" and Microsoft is not interested in changing. This is the way the UI functions.

What you could do would be to create the chart in Excel (using either the interop or OpenXML), then import (insert) that file into PowerPoint.

Check this link from MSDN

Answer 3

Here's a possible work around.

Sub ChartExample()
Dim s As Shape
Set s = Application.Presentations(1).Slides(1).Shapes.AddOLEObject(ClassName:="Excel.Chart")
End Sub

You would then manipulate the chart you added via the s.OLEFormat.Object. I only experimented slightly, but it does not open an external Excel application and I did not see any extreme flickering unless I activated the object. A trade off is that at least in Powerpoint 2010, you need to convert it to use all of the features. If this doesn't work you could always try web components.

Edit: I don't understand why this method causes a problem, but to try to assist further here is a little more code that shows actually manipulating the object. This was written with objects instead of workbooks etc, so that no references need to be made. It only demands the user have Excel on their machine.

Option Explicit
Const xlcolumns = 2
Sub ChartExample()
Dim s As Shape
Dim wb As Object, chart As Object, data As Object
Set s = Application.Presentations(1).Slides(1).Shapes.AddOLEObject(ClassName:="Excel.Chart")
Set wb = s.OLEFormat.Object
Set chart = wb.Sheets(1)
Set data = wb.Sheets(2)
'Set the range for the chart data
chart.setsourcedata Source:=data.Range("A1:C7"), PlotBy:= _
        xlcolumns
'Update data values for the chart
data.Range("B1").Value = "Column Label 1"
data.Range("C1").Value = "Column Label 2"
data.Range("A2:C7").clearcontents
data.Range("A2").Value = "Row Label"
data.Range("B2").Value = 7
data.Range("C2").Value = 11
End Sub