[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now


Inserting specific Excel 2007 named range into 2007 PowerPoint

Posted on 2014-02-06
Medium Priority
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
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
  • 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 53

Accepted Solution

Rgonzo1971 earned 2000 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?
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


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

What’s Wrong with Your Cloud Strategy ?

Even as many CIOs are embracing a cloud-first strategy, the reality is that moving to the cloud is a lengthy process and the end-state is likely to be a blend of multiple clouds—public and private. Learn why multicloud solutions matter in this webinar by Nimble Storage.

Question has a verified solution.

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

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

650 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