none
Copy and paste macro (match destination format) in PPT 2007 RRS feed

  • Question

  • Hey everyone! And thanks in advance.

    I am trying to build a PPT/macro that will eventually pull information from Excel into Powerpoint (i.e. I have client information for hundreds of clients in Excel files that are formatted exactly the same and I'd like to build a template powerpoint presentation that uses a macro to import each company's data, .png's, and graphs, which can then be saved as that company's presentation.)

    I think it's possible and the game-plan is as follows:

    1. Keep two files, one Excel and one ppt, in  a folder together. The ppt is called "Template" and the Excel is called "Supplement".

    2. Save the desired customer's excel file as "Supplement" so the data is updated in Excel.

    3. Build a macro that accesses Supplement and pulls the data into Template.

    4. Run the macro in Template and then save Template as "Customer X.pptx"

    I'm running into a lot of problems in step 3 and I'm just getting started. I know it's because I'm new to this. I've done a few basic macros in Excel but I've always just recorded them and that's not an option in ppt (at least 2007). So far, I'm just trying to copy the customer's name into the ppt and I'm struggling big time. Here's what I have for a macro so far:

    Sub Update()
    '
    ' Basic copy and paste command
    '

    ' Select and Copy Customer name
        ActivePresentation.Slides(1).Select
        ActiveWindow.Selection.SlideRange.Shapes("TextBox 2").Select
        ActiveWindow.Selection.ShapeRange.TextFrame.TextRange.Select
        ActiveWindow.Selection.Copy
    ' Paste on Slide 2
        ActivePresentation.Slides(2).Select
        ActiveWindow.Selection.SlideRange.Shapes("TextBox 1").Select
        ActiveWindow.Selection.ShapeRange.TextFrame.TextRange.Select
        ActiveWindow.View.PasteSpecial DataType:=wdPasteText
    End Sub


    It works when I'm already on slide one (there must be something wrong with my "ActivePresentation.Slides(1).Select" method? But when I delete everything but that command and run it, it takes me to slide 1...) but won't match the destination formatting (I think that is the right method for PasteSpecial to match destination?). Also, you'll notice that I'm not pulling from Excel yet, just using a TextBox on the first slide (which is hidden, btw.) In that regard, I have set up the presentation to tunnel into the Excel and am hopeful I can figure out what that's called in ppt, but that's a future step.

    Do I need any heading commands? I tried a couple of tutorials that weren't very helpful in that regard. Any advice would be phenomenal. Thanks so much! And seriously, I have very few skills (obviously) but if I can help anyone else in any way, I'd love to. Thanks!

    Ryan

    Friday, July 12, 2013 3:59 AM

Answers

  • Hi Ryan,

    You can replace your code with the following and of course set the TextBox names appropriately for your needs. For what you are trying to do, you don't need to select the slides or text frames, you only need to reference them. Also with PowerPoint, it is best to stay away from the clipboard if you can, especially when you start manipulating images.

    Sub Update()
        Dim ppt As Presentation
        Set ppt = ActivePresentation
        ppt.Slides(2).Shapes("Title 1").TextFrame.TextRange.Text = ppt.Slides(1).Shapes("Subtitle 2").TextFrame.TextRange.Text
    End Sub
    


    Kind Regards, Rich ... http://greatcirclelearning.com

    Friday, July 12, 2013 2:09 PM

All replies

  • Hi Ryan

    I think your best option for assistance on this project is the Communities. Not because your question is off-topic here, it's not and theoretically you're in the right place. But the fact is that most of the PowerPoint specialists, including the devs, hang out in the PowerPoint section of the Communities:

    http://answers.microsoft.com/en-us/office/forum/powerpoint?tm=1373626532560

    Unfortunately, I'm not able to move your message there, so you'd need to re-post. FWIW, though, I'm pretty sure what you want to do is possible :-) Say "Hi" to the folks over there, for me!


    Cindy Meister, VSTO/Word MVP, my blog

    Friday, July 12, 2013 10:57 AM
    Moderator
  • Hi Ryan,

    You can replace your code with the following and of course set the TextBox names appropriately for your needs. For what you are trying to do, you don't need to select the slides or text frames, you only need to reference them. Also with PowerPoint, it is best to stay away from the clipboard if you can, especially when you start manipulating images.

    Sub Update()
        Dim ppt As Presentation
        Set ppt = ActivePresentation
        ppt.Slides(2).Shapes("Title 1").TextFrame.TextRange.Text = ppt.Slides(1).Shapes("Subtitle 2").TextFrame.TextRange.Text
    End Sub
    


    Kind Regards, Rich ... http://greatcirclelearning.com

    Friday, July 12, 2013 2:09 PM
  • That worked great! Thanks!
    Monday, July 15, 2013 1:36 AM
  • Rich,

    How do I reference my embedded Excel file by range? I need to pull from the Excel file...

    Monday, July 15, 2013 3:30 AM