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 49

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?
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.


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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Multiple Open Excel Spreadsheets 12 41
Dropbox in Windows Server 2008 4 31
Excel IF formula 3 21
VBA Fill Blanks with text from another cell 6 21
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

778 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