Linked Excel Tables not open for Automation...

[Jeff Rush]

Hello All.

I have a macro which converts embedded excel tables to word
tables.

It works fine.

The problem is when I run the same routine for linked excel
tables, I get an error telling me that 'Class does not
support Automation or does not support expected interface'

The snippet of the macro for the linked object is:

*****************************
If .Type = wdInlineShapeLinkedOLEObject Then
If Left(.OLEFormat.ProgID, 11) = "Excel.Sheet" Then
.OLEFormat.DoVerb wdOLEVerbPrimary
.OLEFormat.Object.Parent.ActiveWindow.visiblerange.Copy
ActiveDocument.Range(.Range.End, .Range.End).Select:
Selection.Paste:
.Delete: DoEvents
End If
End If
**********************

Any ideas on how to make that work? I need to either expose
it for automation or convert the linked tables to embedded
tables first. Once we are at this stage, we no longer need
the information to be contained in a separate document and
we actually need it all to be in word as opposed to excel.

That is why we convert it in the first place. As I said, it
works fine for converting embedded tables but fails on
either of the following two lines:

.OLEFormat.Object.Parent.ActiveWindow.visiblerange.Copy
ActiveDocument.Range(.Range.End, .Range.End).Select:
Selection.Paste:

It will open the table for editing fine and it would delete
it fine.

Any help would be greatly appreciated!

Thanks again.

Jeff(RemoveMe)@EdgeMultimediaGroup.com

 

[Cindy M -WordMVP-]

Hi Jeff,

You unfortunately don't specify which line of code is causing
the problem, but you might try using the verb to actually
OPEN the worksheet in the parent application. Or automate
Excel, open the actual file and work with that.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun
8 2004)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow
question or reply in the newsgroup and not by e-mail