Solved

Macro for Excel Output to Powerpoint

Posted on 2012-03-21
8
328 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 2
8 Comments
 
LVL 42

Expert Comment

by:dlmille
ID: 37748396
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 42

Expert Comment

by:dlmille
ID: 37748510
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
ID: 37751787
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
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 
LVL 42

Accepted Solution

by:
dlmille earned 500 total points
ID: 37751877
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
 
LVL 42

Expert Comment

by:dlmille
ID: 37751908
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 42

Expert Comment

by:dlmille
ID: 37751919
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
ID: 37754391
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 42

Expert Comment

by:dlmille
ID: 37754399
It is ready enough for feedback when you're ready

Cheers

Dave
0

Featured Post

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

710 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