Share via

changing a title in a powerpoint slide within a macro in excel

Anonymous
2018-01-05T21:13:15+00:00

I am trying to figure out how to change a title in an existing powerpoint slide from a macro I am running in excel.  The text that I want to place in the title is a variable that I get from excel.   Overall the macro, pulls some data from excel, copies it to an existing powerpoint presentation slide then saves that powerpoint slide as a pdf.  It is in a big while loop so it does this copying and pasting for a list in excel until the list ends.  The macro below works except for the changing title section (I ran the macro without that section and it does everything I need it to do without that section).   I am not sure what is wrong.  I get a type mismatch on  the line  Set shpCurrShape = .Shapes.Title. I tried that section of code in a powerpoint macro and it did change the title so not sure if because I am working in an excel macro things are different.  Appreciate any help anyone is willing to give - I am not that good with vba code.  Just learning and don't use it that often.

Sub addtitletoslide()

 Dim PPapp As PowerPoint.Application

    Dim PPPres As PowerPoint.Presentation

    Dim file1 As String

    Dim investorname As String

    Dim invpath As String

    Dim cor_file_name As String

    Dim DestinationPPT As String

    Dim shpCurrShape As Shape

    Windows("Printing investor sheets.xlsm").Activate

    Sheets("printing").Select

    invpath = Range("g3")       'g3 = "C:\Users\UserName\Documents\Power Point"

    file1 = Range("g2")

    investorname = Range("g8")

    cor_file_name = Range("i8")              'i8 = "My pdf file name"

    Range("i8").Select

    While investorname <> "end"

    DestinationPPT = "C:\Users\username\Documents\company\Investment model\printing macro\template2.pptx"

   'Initialize PowerPoint Object Library

   'to alleviate multiple instances of PowerPoint, best to attempt GetObject first

   'and if not already open then use CreateObject

            'Copy excel file data

            Windows(file1).Activate

            Sheets(investorname).Select

            Range("b1:r46").Select

            Selection.Copy

    On Error Resume Next       'Will error on GetObject if not already open

    Set PPapp = GetObject(, "PowerPoint.Application")

    On Error GoTo 0

    If PPapp Is Nothing Then   'If Nothing then GetObject failed because PowerPoint not already open.

        Set PPapp = CreateObject("PowerPoint.Application")

    End If

    PPapp.Visible = True

    Set PPPres = PPapp.Presentations.Open(Filename:=DestinationPPT)

    'Paste into existing powerpoint template slide that is open

    PPPres.Slides(1).Shapes.Paste

' This is what I added and now I get error of type mismatch on

' PPres.Slides(1).Shapes.Title.TextFrame.TextRange.Text = "new title test" when I just had this line I received an object error

 With PPPres.Slides(1)

If Not .Shapes.HasTitle Then

Set shpCurrShape = .Shapes.addtitle

Else

        Set shpCurrShape = .Shapes.Title  ' this line is where I get type mismatch

End If

With shpCurrShape

With .TextFrame.TextRange

'~~> Set text here

.Text = "BLAH BLAH"

'~~> Alignment

.ParagraphFormat.Alignment = 3

'~~> Working with font

With .Font

.Bold = msoTrue

.Name = "Tahoma"

.Size = 24

.Color = RGB(0, 0, 0)

End With

End With

End With

End With

   'This works from here down

    PPPres.ExportAsFixedFormat invpath & cor_file_name & ".pdf", ppFixedFormatTypePDF, ppFixedFormatIntentPrint

    PPPres.Close

    PPapp.Quit

    DoEvents    'Can error on next code line if not finished Quitting before setting variables to Nothing

   'Cleanup

    Set PPPres = Nothing

    Set PPapp = Nothing

    Windows("Printing investor sheets.xlsm").Activate

    Sheets("printing").Select

    ActiveCell.Offset(1, -2).Select

    investorname = ActiveCell.Value

    While investorname = ""

        ActiveCell.Offset(1, 0).Select

        investorname = ActiveCell.Value

    Wend

    ActiveCell.Offset(0, 2).Select

    cor_file_name = ActiveCell.Value

Wend

End Sub

Microsoft 365 and Office | PowerPoint | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments No comments

Answer accepted by question author

  1. Anonymous
    2018-01-06T13:51:34+00:00

    Does it still error if you declare shpCurrShape as a PowerPoint.Shape? Both the Excel and PowerPoint OM have Shapes.

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. Anonymous
    2018-01-07T21:31:56+00:00

    That was it - thank you!

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments No comments