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