Inserting specific Excel 2007 named range into 2007 PowerPoint

Posted on 2014-02-06
Last Modified: 2014-02-07
The question really applies to inserting a named range into any existing Office application: Word, PowerPoint, Visio, etc.  For the moment, I'm using PowerPoint.

I have a fairly complex process that copies and pastes dozens of Excel ranges into PowerPoint; and it does not always work out well.  For every fifteenth or twentieth range or so, the system locks, and the code stops in its tracks to say there's an error.  My only choice, really, is to bail out entirely and start the process over, in hopes that I'll get lucky.  Sometimes I do.  

Thinking about this problem, I had the idea that if I were to Insert a named range directly into PowerPoint, rather than copy and paste the range there, the link to PowerPoint would be more solid.  Is it even possible to do this directly?  I tried creating a sample Excel table and give it the named range "TestRange"; and then using the Insert Object routine, choosing Microsoft Excel Worksheet; and then, having chosen the file, try to add a named range, i.e., "C:\MyFolder\TestExcel.xlsx!TestRange".  That particular attempt, and everything else I've tried, has failed; and I find it surprising that access to named ranges would not be more forthcoming.  Having two documents open -- i.e., PowerPoint and Excel; Word and Excel; Visio and Excel -- and using code to copy and paste back and forth between them requires dozens of seamless transfers of data between Excel to clipboard and clipboard to Powerpoint.  I believe the code is sometimes guilty of attempting to paste into PowerPoint prematurely, if a larger dataset takes the clipboard longer to accrue.  The named range is an obvious alternative, providing robust bookmarks to datapoints that other applications leverage as a matter of course.  I believe that leveraging this information would cause my application to run to completion more often, with few to zero errors.  I can dream, can't I? :-)  ~Peter Ferber
Question by:PeterFrb
  • 4
  • 2
LVL 19

Expert Comment

ID: 39841535
I tried this:-

Insert  excel worksheet object into powerpoint slide. Then goto excel and select the whole named range, copy, goto powerpoint and 'paste link'

In power point I now have
{=Excel.Sheet.12|Book1!'!Sheet1!a'} when I select one of the cells ('a' being my named range) and changes to the excel sheet are updated immediately.

is this not what you are experiencing?
LVL 48

Accepted Solution

Rgonzo1971 earned 500 total points
ID: 39841544

with VBA it would be

ActivePresentation.Slides(1).Shapes.AddOLEObject FileName:="c:\Book1.xlsx!Sheet1!table", link:=True

Open in new window

LVL 19

Expert Comment

ID: 39841560
That's interesting - in the manual link there is an extra ! between the filename and the 'sheet' which is outside the quote pair.

eg: =Excel.Sheet.12|'C:\Users\GMartin1\Desktop\Book1.xlsx'!'!Sheet1!a'

Should that be in the VBA too?
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


Author Comment

ID: 39842367
These are great responses, and I'm sure I'll get this worked out!  

For  regmigrant, how do you insert a worksheet object into a power point slide.  The worksheet in its entirety is obviously so much bigger in a slide, it could never hope to contain the whole thing.  Secondly, where do you see "{=Excel.Sheet.12|Book1!'!Sheet1!a'}"?  I was looking for it.

Anyway, I'm excited to test the code presented here.  I'll keep you apprised shortly on my progress.  Thanks!  ~Peter

Author Closing Comment

ID: 39842387
You nailed it!  I placed your code into a module, ran it, and had it work the first time.  Woo hoo!  Good job to all on this.
LVL 19

Expert Comment

ID: 39842432
I chose a slide layout of 'title and content' then in the content part went to 'insert, object, object type "Microsoft excel worksheet'. that gives you an empty excel sheet in your slide, you can treat it as standard excel worksheet, including pasting into it from another workbook. It also has scroll bars so you can move the focus around as in excel.

When you paste a link from another sheet and subsequently select one of the pasted cells you will see the formula = execl.sheet.12 - I am guessing 12 is the 'excel worksheet object' from the types list. The rest (following the |) seems to be the identifier for the excel workbook and  pointers to the cells (or the named range in my example).

I hope it works out for you but I have no idea what sort of file sizes you are going to see
LVL 19

Expert Comment

ID: 39842447
a little harsh on the points award!

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Setting the Scene Animations in PowerPoint are a great tool to convey messages when used carefuly with the content of your slides. There are plenty of animation effects and options, including a Repeat feature for individual animation effects. …
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

758 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

20 Experts available now in Live!

Get 1:1 Help Now