+ Reply to Thread
Results 1 to 9 of 9

Looking for VBA code to resize charts (pasted as pics)?

  1. #1
    Registered User
    Join Date
    01-16-2009
    Location
    Hartford, CT
    MS-Off Ver
    Excel 2010
    Posts
    50

    Looking for VBA code to resize charts (pasted as pics)?

    We have a macro that moves charts from Excel sheets into a PPT deck. It copies the charts from each worksheet and then pastes them as pics in the PPT.

    I'd like to append some VBA to the end of the macro that will then go through the PPT and resize each pic (chart) so that it fits nicely within each slide.

    You'll see what I mean if you look at the attached. Currently, the pics overlap the header and footer of the slides. I need them to line up and fit in an orderly fashion.

    NOTES: On a yearly basis, our marketing dept is responsible for designing a new PPT template that we're required to use for all of our decks. The 2018 version of the template accommodated the pics properly, so sizing wasn't needed. With the new version of the template, the pics don't fit nicely.
    Also, I realize that appending the code to the end of the existing macro is probably not best practice - but I am constrained by what leadership will allow, in terms of modifications to the code.
    Thanks!
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,621

    Re: Looking for VBA code to resize charts (pasted as pics)?

    are the slides the same order every time? or are you expecting Logic to somehow determine which slides needs "rework" ?
    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  3. #3
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,621

    Re: Looking for VBA code to resize charts (pasted as pics)?

    OK so the logic i came up with is
    you only want to change slides where PICTURE 3 exists....ie the slides with 2 charts on them
    basically after finding Picture 3
    change the TOP and LEFT positions then scale down to 90%
    looks ok on my trails but adjust the numbers to your liking you can tweak them to your liking

    Please Login or Register  to view this content.

  4. #4
    Registered User
    Join Date
    01-16-2009
    Location
    Hartford, CT
    MS-Off Ver
    Excel 2010
    Posts
    50

    Re: Looking for VBA code to resize charts (pasted as pics)?

    Ah, this looks promising. I will give it a go, once I get into the office. Thank you!!

  5. #5
    Registered User
    Join Date
    01-16-2009
    Location
    Hartford, CT
    MS-Off Ver
    Excel 2010
    Posts
    50

    Re: Looking for VBA code to resize charts (pasted as pics)?

    So, I was able to try it out, here at home. This is great. How would I modify it so that, if a slide only contains one picture (chart), that picture is centered on the page? Oh, and to answer your question - the order/# of pics on each slide can vary (but never more than 2).

  6. #6
    Registered User
    Join Date
    01-16-2009
    Location
    Hartford, CT
    MS-Off Ver
    Excel 2010
    Posts
    50

    Re: Looking for VBA code to resize charts (pasted as pics)?

    I stepped through the code and noticed that, even if Picture 3 does NOT exist on a slide, the steps between the If/End If are being executed. That's not the expected behavior, right?
    Also, it looks like, for slides with only one picture to start with, the pictures are already centered properly and don't need to be adjusted with code. That being the case, how can the code be modified to "ignore pictures on this slide if the ONLY picture on the slide is named Picture 2"?

  7. #7
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,621

    Re: Looking for VBA code to resize charts (pasted as pics)?

    order of slides dont matter as you correctly identified
    the code only does something when picture 3 exists on slide

    when i briefly looked at the power point the slides with only 1 picture looked fine to me so i thought you only wanted to deal with slides with 2 pictures
    now i looked again i can see that the slide with 1 picture needs to be shifted left

    ran into some trouble if i tried to just set another shape to check so resorted to a function to help determine if shape exists
    it should is much more robust now

    Please Login or Register  to view this content.

  8. #8
    Registered User
    Join Date
    01-16-2009
    Location
    Hartford, CT
    MS-Off Ver
    Excel 2010
    Posts
    50

    Re: Looking for VBA code to resize charts (pasted as pics)?

    Outstanding. This is the best forum EVER. Thank you very much for your help

  9. #9
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,621

    Re: Looking for VBA code to resize charts (pasted as pics)?

    not a problem

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1