7

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.

simendsjo
  • 4,739
  • 2
  • 25
  • 53

3 Answers3

8

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

  • I haven't tested this, but how is this different from Slide.AddChart? Doesn't this open the Excel workbook automatically? – simendsjo Jul 23 '12 at 08:45
  • @simendsjo Code also has data required to create chart. So it might not popup excel. I couldn't try above code as i rarely use windows. –  Jul 23 '12 at 15:54
  • It is exactly what I'm doing (Shapes.AddChart), so it will open excel even when called from code – simendsjo Jul 24 '12 at 07:54
  • @simendsjo updated my answer. This behavior is even seen in Microsoft office 2013 consumer preview. –  Jul 25 '12 at 12:47
  • I see.. It's an awful design! I'm been fighting PowerPoint for nearly 2 weeks now, and it's pretty obvious it isn't created to allow seamless automation. The problem creating the chart in excel and adding to PowerPoint has several problems. The chart isn't "integrated" with PowerPoint and has to open Excel for editing, and also PowerPoint features like color themes doesn't work. The OpenXML solution is something I'll research for the next version, just need to get this one out the door :) – simendsjo Jul 25 '12 at 18:17
  • The link is dead, it's better to include relevant bits here. – Khalil Apr 27 '21 at 13:32
1

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
Daniel
  • 12,982
  • 3
  • 36
  • 60
  • I want the data for the chart present in the powerpoint too. It seems I have to embed or link an Excel workbook in order to use this, which probably is even worse for my use-case unfortunately. – simendsjo Jul 18 '12 at 08:21
  • The data is in Sheet1 of the Excel.Chart object, which you can modify via code. See my edited answer. – Daniel Jul 18 '12 at 12:54
  • Thanks, but this still just "works". The graph is added as a picture rather than an actual chart. PowerPoint cannot manipulate the chart as it was added through PowerPoint. You cannot apply a chart template after it's added (or you can, it just doesn't do anything). It's not added to a chart placeholder, etc etc etc... – simendsjo Jul 18 '12 at 18:11
  • All right, I had no idea you had so many specific requirements for the chart. Sorry this is of no value to you. – Daniel Jul 18 '12 at 18:38
  • 1
    I probably should have specified it a bit more. It should be possible to do some optional post-processing to the chart from PowerPoint after it has been added. It's the first time I'm automating PowerPoint, and I'm quite baffled of the many quirks I'm stumbled upon already.. I'm afraid I'll have to tell the users to don't touch their machines while the presentations are generated - don't think I'll get a medal for that behavior :/ – simendsjo Jul 18 '12 at 18:46
  • Right... Or you have an older version chart. It's still manipulatable, just double click and say Edit Existing. Otherwise, you convert it and it looks like any other chart. But like you said, not very medal worthy. – Daniel Jul 18 '12 at 18:54
  • @Daniel Do you have any idea how to insert an existing chart from Excel to PowerPoint using AddOleObject? I cannot get this https://stackoverflow.com/questions/2262686/how-to-insert-an-excel-chart-into-word-using-addoleobject to work so I was hoping that maybe you could help? Thanks! – chriscode Sep 21 '21 at 07:46
0

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