VBA to update Powerpoint Text from Excel Data in Cells

[tbakbradley]

Hello all, it's been quite a while since I've been on here and have not worked much VBA in a few years. I have a simple Excel File that consists of things like Customer Name, Address, ID Number etc. Each one of those are in Cells. B1, B2, B3. I have a Powerpoint "Sketch" if you will with some Icons and lines that never change on Slide 1. What changes is the Text on the Powerpoint, based on what is provided on the Spreadsheet. To avoid retyping all that in Powerpoint, I'd like VBA that opens the Canned Powerpoint File and updates the Text where needed. In PowerPoint, I've clicked on a Text Box and went to Arrange/Selection Pane and defined the names: CsutomerName, Address, IDNumber.

Forgive me for not remembering how to post this correctly with the VBA Script on here.

With this code, my TestSketch.PPTX File opened up, but the CustomerName Text Box didn't update with the value in B1 of the Excel File

Sub ExportObjectToPowerPoint()



'Second Try

Dim MyPPT As Object

Dim PPTPres As PowerPoint.Presentation



Set MyPPT = CreateObject("Powerpoint.application")



MyPPT.Visible = True

MyPPT.presentations.Open "\\Path\TestSketch.PPTX"

MyPPT.Slides(1).Shapes("CustomerName").TextFrame.TextRange.Characters = "B1"



End Sub

After that, I realized from some other sites, I needed to add Set the Powerpoint Presentation, but when I add this, I get a Compile Error (Method or data Member not Found). I found another site that said to check the "Microsoft PowerPoint 16.0 Object Library" in VBA Editor under Tools Which I did.



Any help would be helpful.

Sub ExportObjectToPowerPoint()



'Second Try

Dim MyPPT As Object

Dim PPTPres As PowerPoint.Presentation



Set MyPPT = CreateObject("Powerpoint.application")

Set PPTPres = PowerPoint.Presentation



MyPPT.Visible = True

MyPPT.presentations.Open "\\Path\TestSketch.PPTX"

MyPPT.Slides(1).Shapes("CustomerName").TextFrame.TextRange.Characters = "B1"



End Sub

 

[Edgar_]

I'll get you on the right track so you can keep working on your project with more ease and less confusion.

This will require a reference:

Sub ExportObjectToPowerPoint()
    Dim MyPPT As PowerPoint.Application
    Set MyPPT = CreateObject("Powerpoint.Application")
 
    Dim PPTPres As PowerPoint.Presentation
    Set PPTPres = MyPPT.Presentations.Open("\\Path\TestSketch.PPTX")
 
    PPTPres.Slides(1).Shapes("CustomerName").TextFrame.TextRange.Text = [B1]
    PPTPres.Slides(1).Shapes("SomeOtherShape").TextFrame.TextRange.Text = [B2]
    PPTPres.Slides(1).Shapes("AnotherShape").TextFrame.TextRange.Text = [B3]
End Sub

This won't require a reference:

Sub ExportObjectToPowerPoint()
    Dim MyPPT As Object
    Set MyPPT = CreateObject("Powerpoint.Application")
 
    Dim PPTPres As Object
    Set PPTPres = MyPPT.Presentations.Open("\\Path\TestSketch.PPTX")
 
    PPTPres.Slides(1).Shapes("CustomerName").TextFrame.TextRange.Text = [B1]
    PPTPres.Slides(1).Shapes("SomeOtherShape").TextFrame.TextRange.Text = [B2]
    PPTPres.Slides(1).Shapes("AnotherShape").TextFrame.TextRange.Text = [B3]
End Sub

As you can see, if you want to avoid adding the reference, you must declare an object.

Remarks:
I took the liberty of modifying your Characters method to Text because I think you want to write something there, but I might be wrong about that.
The square bracket syntax is a shorthand to evaluate what's on that cell of the active sheet, but you can use Cells, Range and other things to reference those cells.

 

[tbakbradley]

Thank you. The first option worked perfectly

 

[tbakbradley]

The above code from @Edgar_ worked perfectly. But I noticed what I was trying to do, I had to create Multiple Text Boxes in Ppt and Define each one of those. That is fine for most things, but when I'm building a Customer Name, City, State and Zip in PowerPoint over a set Icon, it would be nice if all that that was concatenated in the Main Text Box and centered. I guess as long as the text box is already marked "Centered" that part is ok.

If B1= Customer Name
B2 = City
B3 = State
B4= Zip

How would I adjust the code to Concatenate all of those cells into ONE Powerpoint Text Box called "CustomerAddress"? I've concatenated before just within Excel in VBA, but I'm running into issues with it not working when using the Excel Cells to concatenate for one Text Box in Power Point.

Thanks.

 

[Edgar_]

If you want each piece of data in a separate line:

Sub ExportObjectToPowerPoint()
    Dim MyPPT As PowerPoint.Application
    Set MyPPT = CreateObject("Powerpoint.Application")
 
    Dim PPTPres As Object
    Set PPTPres = MyPPT.Presentations.Open("\\Path\TestSketch.PPTX")
    
    If [B1] = "Customer Name" Then
        PPTPres.Slides(1).Shapes("CustomerName").TextFrame.TextRange.Text = [B2] & vbCrLf & [B3] & vbCrLf & [B4]
    End If
 
End Sub

If you want it separated by comma, instead of [B2] & vbCrLf & [B3] & vbCrLf & [B4], use [B2] & ", " & [B3] & ", " & [B4]

 

[tbakbradley]

Thank you. It's been a while and now I recall but I didn't remember the vbCrLf for the carriage return.

 

[tbakbradley]

Thank you again @Edgar_ for the help on this one. As I'm doing more work with my Automation, I realized that in that last section of concatenating various Excel Cells into a PowerPoint Text Box with the Line breaks, I wanted to try and make the First Line in the Text Box Bold. I was hoping it would be as simple as HTML with the Font, but I'm having difficulties finding a method of making only the first Text in first line Bold.

For the code example below, I'd like to make ONLY [B2] Bold. The rest not bold. I can make the entire Text Box Bold, but can't figure out how to make just that first line (B2) Bold.

Sub ExportObjectToPowerPoint()
    Dim MyPPT As PowerPoint.Application
    Set MyPPT = CreateObject("Powerpoint.Application")
 
    Dim PPTPres As Object
    Set PPTPres = MyPPT.Presentations.Open("\\Path\TestSketch.PPTX")
    
    PPTPres.Slides(1).Shapes("CustomerName").TextFrame.TextRange.Text = [B2] & vbCrLf & [B3] & vbCrLf & [B4]
[B]'Test to make entire textbox of "CustomerName" Bold. Trying to figure out how to make just B2 above Bold
 PPTPres.Slides(1).Shapes("CustomerName").TextFrame.TextRange.Font.Bold = msoTrue[/B]
 
End Sub

 

[Edgar_]

Try this:
PTPres.Slides(1).Shapes("CustomerName").TextFrame.TextRange.Lines(1).Font.Bold = msoTrue

 

[Edgar_]

Just in case, if you wanted to format by characters, you would use the Characters member, which I think you were using in your first post.

For example, if you wanted apply bold formatting to 3 characters starting from the 9th character in the text, you would use this:
PTPres.Slides(1).Shapes("CustomerName").TextFrame.TextRange.Characters(9,3).Font.Bold = msoTrue
Nikola Tesla ---> Nikola Tesla

 

[tbakbradley]

Just in case, if you wanted to format by characters, you would use the Characters member, which I think you were using in your first post.

For example, if you wanted apply bold formatting to 3 characters starting from the 9th character in the text, you would use this:
PTPres.Slides(1).Shapes("CustomerName").TextFrame.TextRange.Characters(9,3).Font.Bold = msoTrue
Nikola Tesla ---> Nikola Tesla

Just in case, if you wanted to format by characters, you would use the Characters member, which I think you were using in your first post.

For example, if you wanted apply bold formatting to 3 characters starting from the 9th character in the text, you would use this:
PTPres.Slides(1).Shapes("CustomerName").TextFrame.TextRange.Characters(9,3).Font.Bold = msoTrue
Nikola Tesla ---> Nikola T

Try this:
PTPres.Slides(1).Shapes("CustomerName").TextFrame.TextRange.Lines(1).Font.Bold = msoTrue

The Lines(1) is perfect. That was so simple, but I could find anything on that. Thank you so much.

I've not had an issue when Trying to have the SaveAs Dialog Box pop up on an Excel file, but it was a bit more cumbersome to have the SaveAs Dialog Box come up in Powerpoint after all the Text was written. I did figure it out, but I want the SaveAs box to open NOT to the current File Path Directory of the PowerPoint File that was opened and edited based on the Excel Macro, but I want it to show a different Path, but where the User still has to type in their own file name and click Save.

I tried

savePath = "C:\Users\"
chDir savePath
MyPPT.CommandBars.ExecuteMso "FileSaveAs"

After the Powerpoint file is updated, the SaveAs Dialog Box appears, but it's still under the File Folder of the current Power Point file that is open. I'm trying to prevent the user from accidentally Saving the edited file overtop of the Master file that had opened at the beginning. So wanted the SaveAs to pop up automatically to a different folder then they could save it wherever they want on their hard drive and with any file name they wanted.