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