Passing Excel Sheet Name to PowerPoint

Kipo

New Member
Joined
Apr 17, 2009
Messages
10
Hi All,

I'm having a little problem passing an excel 2007 sheet name to Powerpoint 2007 as the slide title. Not sure if its a powerpoint issue or an excel issue but hopefully someone here will be able to help.

I'm using the code below to copy all charts from excel to powerpoint (each slide has its own chart) but i would like to pass the sheet name to the slide title.

The charts are created by a reporting tool that i have created (its a macro driven pivottable that is able to produce about 100 reports or charts - not sure if this matters) so i needed a way to quickly of exporting the charts to powerpoint.

Code:
Sub xlCopyChartsBook()
Dim xlBookName As String
Dim xlBook As Workbook
Dim pptApp As PowerPoint.Application
Dim pptPres As PowerPoint.Presentation
Dim pptSlide As PowerPoint.Slide
Dim pptShape As PowerPoint.Shape
Dim xlChSheet   As Chart
Dim i As Integer
Dim Count As Integer
    
Count = 0 'initialise count variable
i = 1
Application.CutCopyMode = True
If xlBookName = vbNullString Then xlBookName = ActiveWorkbook.Name 'set workbook
    Set xlBook = Workbooks(xlBookName)
    
    Set pptApp = CreateObject("PowerPoint.Application") 'create powerpoint slide
    
    'Set pptApp = CreateObject("PowerPoint.Application")
    'Set pptPres = pptApp.Presentations.Add(msoTrue) ' create a new presentation
    ' or open an existing presentation
    ' Set pptPres =
    'pptApp.Presentations.Open ("C:\Foldername\Filename.ppt")

    Set pptPres = pptApp.Presentations.Add(msoTrue)
On Error Resume Next
    pptPres.ApplyTemplate "[URL="file://\\new"]new[/URL] brand.pot" ' apply a slide template
On Error GoTo 0
    For Each xlChSheet In xlBook.Charts
    
        'ActiveSheet.ChartObjects(i).Activate ' selects the chart object by its index number
        xlChSheet.Select
        ActiveSheet.ChartArea.Select
        ActiveSheet.ChartArea.Copy
            With pptPres.Slides
            Set pptSlide = .Add(.Count + 1, ppLayoutTitleOnly) ' add a slide
            End With
            With pptSlide
            '.Shapes.PasteSpecial DataType:=ppPasteMetafilePicture
            '.Shapes.PasteSpecial DataType:=ppPasteHTML
            
            .Shapes.PasteSpecial ppPasteShape
            '.Shapes.PasteSpecial ppPasteRTF
            '.Shapes.PasteSpecial ppPasteOLEObject
            '.Shapes.PasteSpecial DataType:=ppPasteOLEObject
            '.Shapes.PasteSpecial link:=True
            '.Shapes.PasteSpecial ppPasteDefault
                With .Shapes(.Shapes.Count) ' sizes the graph on the slide
                .Left = 25
                .Top = 115
                .Width = 600
                .Height = 400
                End With
            End With
    i = i + 1
    
Next xlChSheet
    
Application.CutCopyMode = False ' end cut/copy from Excel
On Error Resume Next ' ignore errors
Set pptSlide = Nothing
Set pptPres = Nothing
pptApp.Visible = True ' display the application
Set pptApp = Nothing
   
Application.DisplayAlerts = True
Exit Sub
     
End Sub
 

Some videos you may like

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,716
My testing with Excel/PP 2003 failed with Automation error on the .Shapes.PasteSpecial ppPasteShape line, but try this within the With pptSlide clause:
Code:
.Shapes.Title.TextFrame.TextRange.Text = xlChSheet.Name
Running the Macro Recorder within Powerpoint whilst adding a title will show you the objects and properties involved in adding a title.
 
Last edited:

Kipo

New Member
Joined
Apr 17, 2009
Messages
10
thanks, i'll try this on monday!

i did try to use macro recorder with ppt2007 (using alt+t M R) but it didnt seem to work, i manipulated all the elements on the slide but nothing in the recorder output
 

Watch MrExcel Video

Forum statistics

Threads
1,127,735
Messages
5,626,587
Members
416,192
Latest member
steinach

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top