Passing Excel Sheet Name to PowerPoint

[Kipo]

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.

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

 

[Kipo]

anyone have any ideas?

 

[John_w]

My testing with Excel/PP 2003 failed with Automation error on the .Shapes.PasteSpecial ppPasteShape line, but try this within the With pptSlide clause:

.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.

 

[Kipo]

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