Powerpoint 2007 VBA to paste as enhanced metafile, resized, and placed at specific location

I am pasting Excel tables to Powerpoint 2007 as enhanced metafiles and would like to speed up the process with some VBA code.  The pasted pictures need to be resized to 120% of their original size (or a fixed width could be specified, but the height varies) and need to be placed at a specific location.

I have code to paste as an enhanced metafile (see attached code snippet).  What I need is code to select the object once pasted, resize it, and place it at a specific location.
ActiveWindow.Selection.SlideRange(1).Shapes.PasteSpecial ppPasteEnhancedMetafile

Open in new window

marknlynn3Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Antagony1960Commented:
Just a suggestion: have you thought of preparing what you need to do and then recording a Macro (Tools¦Macro¦Record New Macro...), performing the actions manually and then seeing what Powerpoint produces? There's usually some excess chaff which can be removed from the code, but it shouldn't be too difficult to pick out the bits you need.
0
marknlynn3Author Commented:
Powerpoint 2007 doesn't have a macro recorder, and that's the only version of Powerpoint I have access to.  Also, I need the code to automatically select the pasted picture or to link that picture to a variable so that I can manipulate it with VBA.  I don't think using the macro recorder would give me any insight into how to do that.
0
Antagony1960Commented:
I didn't know MS had dropped macro recording in PP 2007... the b******s! :-D
Luckily for you I have an old machine here with PP 2003 on it so I recorded a macro with it and modified it to suit your requirements. All I had to do was work out how to get the index of the last shape inserted. I haven't been able to test it on 2007 but I see no reason why it wouldn't work.
Sub PasteAndResize()
Dim i As Integer
    ActiveWindow.Selection.SlideRange(1).Shapes.PasteSpecial ppPasteEnhancedMetafile
    'Get the index of last insertion'
    i = ActivePresentation.Slides(ActiveWindow.Selection.SlideRange.SlideNumber).Shapes.Count
    'Select the last inserted object'
    ActiveWindow.Selection.SlideRange.Shapes(i).Select
    'Rescale it to 120%'
    With ActiveWindow.Selection.ShapeRange
        .ScaleWidth 1.2, msoFalse, msoScaleFromTopLeft
        .ScaleHeight 1.2, msoFalse, msoScaleFromTopLeft
    End With
End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

GlennaShawCommented:
Multiple methods are shown on Jon Peltier's site:
http://peltiertech.com/Excel/XL_PPT.html
0
marknlynn3Author Commented:
Thanks Antagony1960, the code you provided was very close to what I needed.  I made the following modifications:

I changed ".ScaleWidth 1.2, msoFalse" to ".ScaleWidth 1.2, True" and "ScaleHeight 1.2, msoFalse" to ".ScaleHeight 1.2, True" so that the picture's width and height would be scaled from the picture's original size rather than the picture's current size.  When I ran the code with this parameter set to scale from the picture's current size (msoFalse) the picture ended up being scaled up 44%.  The 20% increase was applied twice.  Setting the parameter to msoTrue fixed this problem.

I also added some code to set the location of the picture.

GlennaShaw, thanks for pointing me to Jon Peltier's site.  This information should prove useful in the future when I have more to to work on increasing the efficiency of our processes.
Sub PasteAndResize()
Dim i As Integer
    ActiveWindow.Selection.SlideRange(1).Shapes.PasteSpecial ppPasteEnhancedMetafile
    'Get the index of last insertion'
    i = ActivePresentation.Slides(ActiveWindow.Selection.SlideRange.SlideNumber).Shapes.Count
    'Select the last inserted object'
    ActiveWindow.Selection.SlideRange.Shapes(i).Select
    'Rescale it to 120%'
    With ActiveWindow.Selection.ShapeRange
        .ScaleWidth 1.2, msoTrue, msoScaleFromTopLeft
        .ScaleHeight 1.2, msoTrue, msoScaleFromTopLeft
        .Left = 30.24
        .Top = 340
    End With
End Sub

Open in new window

0
marknlynn3Author Commented:
Thanks both for your help.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.