Solved

Macro for Excel Output to Powerpoint

Posted on 2012-03-21
8
317 Views
Last Modified: 2012-03-22
EE Professionals,

I have a WB that has a number of "Ranges", "Charts", and "Graphics" that I normally use a product like Snagit to copy and paste the images from Excel to Powerpoint.  Is it possible/reasonable to create a macro in Excel that takes range names, or other objects (i.e. Graphics, Charts), and output them to a Powerpoint presentation?  Each object/range name would need to be outputted to a different PPT slide.  It doesn't have to be perfect, but it would save a lot of time to be able to do that.

I've attached a very simple WB with a range and a graphic to see if we can make this happen.

Thank you in advance,

B.
Output-to-PPT-WB.xlsm
0
Comment
Question by:Bright01
  • 6
  • 2
8 Comments
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
You can output a range name or even a chart/image name to powerpoint.

Can you help me with what is different from this solution, if anything:
http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_27477527.html

Also, I have created the utility to paste ranges into Powerpoint into precise locations (re: replacing images, etc. If you are interested.

Please advise.

Dave
0
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
Ok - for proof of concept purposes, I created a range called "ExportRange" where you can put range names and chart/image names.  Run the macro and it adds to existing or creates new presentation/slides.

The code is a modification of the original solution from some time back.

Again, I built that utility we discussed where you can specify exact precision around where each chart/image/range would be replaced in powerpoint, but perhaps that's a different question for future.

See attached, your solution as requested.

Dave
Output-to-PPT-WB-r1.xlsm
0
 

Author Comment

by:Bright01
Comment Utility
Dave,

Greetings.  Very nice.  Answer & Question;

1.) The most recent question should be an extension of the first.  They are both related.  Sometimes I cannot make the jump from a Macro to the integration in a WB that has more complications.  Then later when I stumble across the need again, I author another question and often it has built off a previous macro answer.

2.) I tested the macro you just sent and it works well EXCEPT for a certain condition.  Ironically, when I added the "Charts" I had in the WB, I found that I have Charts with the same name (e.g. Chart 1).  When I try to change the chart name in the upper left name box, it reverts to the original name.  How do I change the Chart name(s) so I can identify them as unique Charts?  Also, I have "Text" that overlays some of my graphics.  So when it posts the Chart, it leaves out the text.  Now I'm working to incorporate the Text INTO the graphic; but I also tried to create a "range" that included the chart....but that didn't produce a PPT slide with the chart included....... any ideas?  IF not, I can live with the chart alone.

Thanks,

B.
0
 
LVL 41

Accepted Solution

by:
dlmille earned 500 total points
Comment Utility
How to change a chart name:  You're supposed to be able to do this, manually, but I have a heck of a time following Peltier's steps to do this:  http://peltiertech.com/Excel/ChartsHowTo/NameAChart.html

So, I use a macro to do it.  in the attached, you now have an add-in menu item that will step you through it.  Select the chart/image, then use the add-in menu to name the chart/image.


>>Now I'm working to incorporate the Text INTO the graphic; but I also tried to create a "range" that included the chart....but that didn't produce a PPT slide with the chart included....... any ideas?

You have to position the chart/text and all the stuff you want in the "image" going to Powerpoint on top of a range.  Or, said a different way, just create a range that encompasses all you want and name it.  That should get it copied over.

I created a 2nd range which is larger than the chart, but captures the shape "Hey" textbox I created on top of the chart to demonstrate that should work.

See attached.

Cheers,

Dave
Output-to-PPT-WB-r2.xlsm
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 41

Expert Comment

by:dlmille
Comment Utility
Technically, the chart "Chart1" on Sheet2's name is "Sheet2 Chart1" but it gets messy trying to type all that in and I'd have to code to support that specific name, though its possible, I think creating your own homegrown unique names probably is more supportable on both ends, as a result.

In fact, most if not all shapes have this attribute (sheet prefix, then name of shape) - at least they do when they're originally created.

With the ExcelToPowerpoint! utility I created (you've not seen as yet), you actually have to name the replacement shapes in Powerpoint with a similar add-in menu.  the way it works is it reads all the images in Powerpoint, gets their names, then finds them in Excel.  Then, deletes the old image, replaces with the new in the exact same position and dimensions as they were intended in the originally designed Powerpoint template.  One of these days we'll get around to this one, lol.

Cheers,

Dave
0
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
Hopefully, this wraps up this question.  I'm off to bed as its 4:17 am and I have a Dr's appointment and won't get back to E-E till maybe tomorrow evening.

Cheers,

Dave
0
 

Author Closing Comment

by:Bright01
Comment Utility
Nice job Dave.  After an hour, I get how this now works.  Appreciate the help and the lesson. Please let me know when your tool is available.

Thank you,

B.
0
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
It is ready enough for feedback when you're ready

Cheers

Dave
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Checkbox fires off a Statement 7 20
Excel - Page layout - Margins 7 32
File size limit in SharePoint 2010 3 14
Gantt chart 2 9
Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now