VBA for Copying Charts from Excel to Powerpoint

[DMurray3]

Hi All,

I am using Excel 2007 (xlsx) & PowerPoint 2007 (ppt).

I have used J.Peltier's recommended macro to copy multiple charts as "pictures" to ppt. However, thanking Jon for his explanation why to copy as a "picture", I need to copy my charts as Chart Objects as well use the chart titles as "slide titles" and of course the alignment of the chart in the corresponding slide.

I would highly appreciate if someone could guide me to change the "CopyPicture.." to copy the chart as an "object"?

I have tried doing the modification myself, using but in all attempts, I have failed miserably.

Any help will be highly appreciated.

Kind regards, DMurray3

 

[Tom Urtis]

I imagine Jon would have the solution to your question somewhere on his excellent site.

Still, just to clarify a point, you wrote this:

"...need to copy my charts as Chart Objects"

You are saying then, that you want to copy the charts in your workbook from chart sheets, and only from chart sheets, because they are chart objects, as opposed to embedded charts which are chartobject objects. Please confirm yay or nay on that for starters.

 

[DMurray3]

Thanks Tom for your interest...

I have looked through Jon's site and have not found the answer I'm looking for. I have found others sites with code that partially addresses what I need... but these don't move the chart's title to the slide's title.

After your reply I thought it best to provide an example -including the macro being used- which I am including in this post. I am also detailing what it is I need more clearly as well as any progress I have made -if any- since my original thread.

As can be seen in the my example, my chart titles are built using references to formulas and cells; when using Jon's macro, and these are "restored", they are siwtched to "plain text". I reference "formulas and cells" for my chart titles because I have to translate my charts to 3 different languages and it is easier to simple change the "cells" that make up my titles rather than retyping the translations.

The example included herein has 4 charts on the worksheet; in my "real case scenario" my workbook has multiple worksheets each with +60 charts that I must transfer over to a PPT file. And this is a process I have to do on a monthly basis. That is why getting this macro working correctly is so urgently needed.

Again, I would kindly appreciate any guidance/code recommendations to adapt this specific macro to my requirement.

Many thanks & kind regards,

DMurray3

 

[DMurray3]

Thanks Tom for your interest...

I have looked through Jon's site and have not found the answer I'm looking for. I have found others sites with code that partially addresses what I need... but these don't move the chart's title to the slide's title.

After your reply I thought it best to provide an example -including the macro being used- which I am including in this post. I am also detailing what it is I need more clearly as well as any progress I have made -if any- since my original thread.

As can be seen in the my example, my chart titles are built using references to formulas and cells; when using Jon's macro, and these are "restored", they are siwtched to "plain text". I reference "formulas and cells" for my chart titles because I have to translate my charts to 3 different languages and it is easier to simple change the "cells" that make up my titles rather than retyping the translations.

The example included herein has 4 charts on the worksheet; in my "real case scenario" my workbook has multiple worksheets each with +60 charts that I must transfer over to a PPT file. And this is a process I have to do on a monthly basis. That is why getting this macro working correctly is so urgently needed.

Again, I would kindly appreciate any guidance/code recommendations to adapt this specific macro to my requirement.

Many thanks & kind regards,

DMurray3

I apologize for this re-post: I did not realize soon enough that I could not "attatch" my example case. Please disregard the original example link and refer to the example workbook image that I have placed on http://es.scribd.com/doc/51632243.

The macro's code I have so far is as follows:

Sub ChartsAndTitlesToPresentation()
'***DTM: Taken from http://peltiertech.com/Excel/XL_PPT.html#chartstitlesslides

' Set a VBE reference to Microsoft PowerPoint Object Library

Dim PPApp As PowerPoint.Application
Dim PPPres As PowerPoint.Presentation
Dim PPSlide As PowerPoint.Slide
Dim PresentationFileName As Variant
Dim SlideCount As Long
Dim iCht As Integer
Dim sTitle As String

' Reference existing instance of PowerPoint
Set PPApp = GetObject(, "Powerpoint.Application")
' Reference active presentation
Set PPPres = PPApp.ActivePresentation
PPApp.ActiveWindow.ViewType = ppViewSlide

For iCht = 1 To ActiveSheet.ChartObjects.Count
    With ActiveSheet.ChartObjects(iCht).Chart
                
        ' get chart title
        If .HasTitle Then
            sTitle = .ChartTitle.Text
        Else
            sTitle = ""
        End If
        
        ' remove title (or it will be redundant)
        .HasTitle = False
        
        ' ***DTM_start: Commenting this out because I need the chart "itself" not a "picture copy"
        ' copy chart as a picture
        '.CopyPicture _
        '    Appearance:=xlScreen, Size:=xlScreen, Format:=xlPicture
        '***DTM_end

        ' ***DTM_start: Added this part to copy the chart "itself"
         ActiveSheet.ChartObjects(iCht).Activate
         ActiveChart.ChartArea.Copy
        '***DTM_end

        ' restore title
        If Len(sTitle) > 0 Then
            .HasTitle = True
            .ChartTitle.Text = sTitle
        End If
    End With
    
    ' Add a new slide and paste in the chart
    SlideCount = PPPres.Slides.Count
    Set PPSlide = PPPres.Slides.Add(SlideCount + 1, ppLayoutTitleOnly)
    PPApp.ActiveWindow.View.GotoSlide PPSlide.SlideIndex
    With PPSlide
        ' paste and select the chart picture
        .Shapes.Paste.Select
        ' align the chart
        PPApp.ActiveWindow.Selection.ShapeRange.Align msoAlignCenters, True
        PPApp.ActiveWindow.Selection.ShapeRange.Align msoAlignMiddles, True
        .Shapes.Placeholders(1).TextFrame.TextRange.Text = sTitle
    End With

Next

' Clean up
Set PPSlide = Nothing
Set PPPres = Nothing
Set PPApp = Nothing

End Sub

Again my apologies and thanks for all help I can be favored with.

DMurray3

 

[Tom Urtis]

I'm not completely following you.

First, I asked if the charts are on chart sheets and not embedded onto worksheets because you first mentioned "chart objects". It kind of looks like the charts are embedded after all, but you never said specifically, so would you please say yes embedded, or yes chart sheets, are the chart objects to be copied. It makes a difference in the code.

Second, what version of Office are you using.

Third, as I understand the issue, you want to copy a bunch of charts (embedded or on chart sheets to be clarified) and paste each one on its own PowerPoint slide. The thing about formulas and titles is what I don't get. Are you expecting the PowerPoint slide to be linked to a worksheet cell's formula? Please clarify your expectations about chart titles and cell formulas, and your chart type(s) to be copied.

 

[Jon Peltier]

It depends why you need to insert the chart and not just a picture.

You could just copy the chart area and paste on the slide. In 2003 and earlier this is an issue because each paste inserts a copy of the entire workbook behind the visible chart in PowerPoint. Oops: 60 charts = huge file sizes, and there's a risk of inadvertently sharing proprietary data. The way around this might be paste link, or in Excel move the chart and data into a separate workbook, so you're only pasting what you need.

If the issue is needing to support multiple languages, maybe it makes as much sense to run the macro multiple times, producing multiple presentations, one for each language.

 

[DMurray3]

Hi Tom & Jon... to both of you my thanks for your interest.

Tom: Charts are embedded on my worksheets. Sorry for not responding the first time around...In my first thread I did mention I was using Excel 2007 & PPT 2007.

Jon: The ppts/xlsx files are prepared by different users and I have the repsonsability to review content & form. Having the actual charts in the ppt -rather than copies- simplifies my editing/correction or even adjusting the elements for clarity. Size is not a major concern as once the ppt file is ready, we save a .pptx version for our files and a second in .pdf for distribution.

Both: Using cells and formulas to build titles (Chart Titles, Axis Titles, category/series names etc.) simplifies our translation: we do not have to re-type all these elements (many of which are repetetive) and only have to "translate" the "fixed text". We will always need to run the macro every time we neen a new ppt file.

The major benefit I get from posting on these forums is the insight experts like yourselves provide... I appareciate your commments.

After a lot of trial and error (again, I have recently started to use macros and still have a lot of triuble finding my way around writting these up...) I was able to come up with some changes to Jon's code, which are working for my case. My main trouble was understanding that "charts" in Excel, when in ppt are refered to as "Shapes"...

FYI, my modified code is provided:

Sub ChartsAndTitlesToPresentation()
'DTM: DMurray3
'DTM: Original code obtained from http://peltiertech.com/Excel/XL_PPT.html#chartstitlesslides

'DTM: The original code copies charts as "pictures" to ppt. I have modified the code so that
'DTM: charts are copied as an actual "chart".

' Set a VBE reference to Microsoft PowerPoint Object Library

Dim PPApp As PowerPoint.Application
Dim PPPres As PowerPoint.Presentation
Dim PPSlide As PowerPoint.Slide
Dim PresentationFileName As Variant
Dim SlideCount As Long
Dim iCht As Integer
Dim sTitle As String

' Reference existing instance of PowerPoint
Set PPApp = GetObject(, "Powerpoint.Application")
' Reference active presentation
Set PPPres = PPApp.ActivePresentation
PPApp.ActiveWindow.ViewType = ppViewSlide

For iCht = 1 To ActiveSheet.ChartObjects.Count
    With ActiveSheet.ChartObjects(iCht).Chart
    'DTM: We have commented out the section on the "title" because it alters our chart titles in excel (during the "restore")
    'DTM: to plain text and we need to keep those titles based on cells & formulas.
    'DTM: We have moved the "ChartTitle to SlideTitle" to the ppt macro portion of the code further below.
    
    'DTM: ' get chart title
    'DTM: If .HasTitle Then
    'DTM:   sTitle = .ChartTitle.Text
    'DTM:   Else
    'DTM:   sTitle = ""
    'DTM: End If
        
    'DTM: ' remove title (or it will be redundant)
    'DTM: .HasTitle = False
        
    'DTM: Commenting this out because I need the chart "itself" not a "picture copy"
    'DTM: ' copy chart as a picture
    'DTM: .CopyPicture _
    'DTM:    Appearance:=xlScreen, Size:=xlScreen, Format:=xlPicture
        
    'DTM: Added this part to copy the chart "itself"
         ActiveSheet.ChartObjects(iCht).Activate
         ActiveChart.ChartArea.Copy
        
    'DTM: ' restore title
    'DTM: If Len(sTitle) > 0 Then
    'DTM:   .HasTitle = True
    'DTM:   .ChartTitle.Text = sTitle
    'DTM: End If
    End With
    
    'DTM: Macro code in PPT starts here..
    
    ' Add a new slide and paste in the chart
    SlideCount = PPPres.Slides.Count
    Set PPSlide = PPPres.Slides.Add(SlideCount + 1, ppLayoutTitleOnly)
    PPApp.ActiveWindow.View.GotoSlide PPSlide.SlideIndex
    With PPSlide
        ' paste and then select the chart again
        .Shapes.Paste.Select
        ' align the chart
        PPApp.ActiveWindow.Selection.ShapeRange.Align msoAlignCenters, True
        PPApp.ActiveWindow.Selection.ShapeRange.Align msoAlignMiddles, True
        
    'DTM: Working with the chart copied from Excel is referenced as a "Shape" in PowerPoint; PPT will also assign a new
    'DTM: index number to the Shape.
    
    'DTM: Including the code to update the SlideTitle with the ChartTitle. We assume the Chart is the "last" shape
    'DTM: in the Slide, so we use "(.Shapes.Count)" as the index.
       With .Shapes(.Shapes.Count)
        ' get chart title
                If .Chart.HasTitle Then
                    sTitle = .Chart.ChartTitle.Text
                Else
                    sTitle = "***No Chart Title Available***" 'To warn user...
                End If
        ' remove title from chart (or it will be redundant in the Slide)
            .Chart.HasTitle = False
        End With
        
        ' load ChartTitle in SlideTitle
        .Shapes.Placeholders(1).TextFrame.TextRange.Text = sTitle
    
    'DTM: We do not require "restoring" the chart title, given the title change is only applicable to the ppt.
    
    End With

Next

' Clean up
Set PPSlide = Nothing
Set PPPres = Nothing
Set PPApp = Nothing

End Sub

I am in a learning curve... If you identify a better way to do the above, your guidance will be more than appreciated.

Kind regards, DMurray3

 

[DMurray3]

Some final notes to answer Tom's and Jon's comments/questions:

- The charts we build are basic bar/column/line charts;
- We only copy the "chart"; data from Excel is never transfered to ppt;
- Yes, we understand copying the chart from Excel to PPT (ie. not as a Picture), may -to some extent- compromise access to proprietary info, but given we distribute the resulting ppt in pdf format, this risk is eliminated.

Further on this last point: On some ocasions I have tried to access the worksheet underlying the actual charts (by doble-clicking the chart when in the original PPT), and can only access if the link to that worksheet file is updated/available. If I transfer the "ppt" file to another directory/station without the underlying workbook, PPT displays the "unavailable link" message.

- To further clarify using cell references/formulas for chart / axis titles in Excel, they are needed -as such only in "Excel"; when the chart gets copied to ppt, they can be pure plain "text". If you look closely at Jon's original code, when the chart title eliminated to avoid redundancy and subsequently is "restored" back, the "restore" is done so from the contents of the "stitle" variable, which as a result "over-writes" the contents of the original "title" in Excel. This is specifically what I wanted to avoid: loosing the original cell/formula that makes up the chart/axes titles.

- Again, as clarification, for the quick translation to other languages: in that text which will be translated, we do the following :

a) have a fiield/cell in our workbook called "Lang_Setting_field" which can hold 3 values=1, or 2, or 3; these correspond to the language we need (1=English; 2=Spanish; and 3=Portuguese).

b) Throughout the workbook, in all cells that have fixed text to be translated, we use the following formula: =IF(Lang_Setting_field=1;"TextInEnglish";IF(Lang_Setting_field=2;"TextInSpanish";"TextInPortuguese"))

c) If we change the value in cell/field noted in (a) above, the entire workbook/worksheets as well as all charts, are automatically translated/updated.

I trust this explains our process. I am more than interested in any thoughts to improve how we are taking advantage of Excel and PPT. I enjoy this learning process very much, moreover if the "teachers" are experts such as yourselves.

Kind regards, DMurray3

 

[Jon Peltier]

It's possible to access that formula. You need to use some ancient functions from the old Excel 4 Macro language. Here's a simple demo:

Sub GetTitleFormula()
  Dim sFmla As String
  
  With ActiveChart
    .ChartTitle.Select
    sFmla = ExecuteExcel4Macro("GET.FORMULA(SELECTION())")
    .ChartTitle.Delete
    .HasTitle = True
    .ChartTitle.Text = sFmla
  End With
End Sub

This will insert the formula (must be in R1C1 format) back into the chart title. It also reapplies the chart type's default formatting to the chart title and shrinks the plot area to make (too much) room for the title, so more formatting is probably required.

If I were going to do this, I'd probably make copy of the chart in the worksheet, remove the title, copy this chart and paste it sans title into PowerPoint, then delete the copy in the worksheet.

Excel makes nothing particularly easy, eh?

 

[DMurray3]

Again many thanks Jon for your tips and explanation... I will try your alternatives and see what I can come up with.

And.. yes indeed... Excel -after the basics- doesn´t make anyhthing particularly easy...

Thanks and kind regards.