Solved

Inserting specific Excel 2007 named range into 2007 PowerPoint

Posted on 2014-02-06
7
965 Views
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
0
Comment
Question by:PeterFrb
[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
  • 4
  • 2
7 Comments
 
LVL 19

Expert Comment

by:regmigrant
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?
0
 
LVL 51

Accepted Solution

by:
Rgonzo1971 earned 500 total points
ID: 39841544
HI,

with VBA it would be

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

Open in new window

Regards
0
 
LVL 19

Expert Comment

by:regmigrant
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?
0
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 

Author Comment

by:PeterFrb
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
0
 

Author Closing Comment

by:PeterFrb
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.
0
 
LVL 19

Expert Comment

by:regmigrant
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
0
 
LVL 19

Expert Comment

by:regmigrant
ID: 39842447
a little harsh on the points award!
0

Featured Post

Technology Partners: 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!

Question has a verified solution.

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

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
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…

726 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