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 50

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?
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.


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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Getting rid of #VALUE! 7 23
Expanding and filling year gaps in multiple listings 6 28
Excel Macro 9 22
Decoding a line of VBA DataSort 11 10
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 article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

828 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