Edit Excel Object in Word VBA

[xlStar]

Hello.

I have a word document containg several pictures from Excel spreadsheet. These pictures were entered into Word using the Paste Special as 'Microsoft Excel Worksheet Object; without paste links.

I need to write a macro in Word to check the validations of the amounts within that embeded object and I am struggling to open the embedded object into Excel.

Here is my code so far...

Sub EditEmbeddedWkb()
Dim wdOle As OLEFormat
Dim xlApp As Object
Dim xlWkb As Object

Dim myDoc As Document
Set myDoc = Documents("SOA 07-08.doc")
myDoc.Activate
Selection.GoTo What:=wdGoToPage, Count:="22"
Selection.GoTo What:=wdGoToObject, Which:=wdGoToNext, Count:=1, Name:="Excel.Sheet.8"


'here is an error!
Selection.ShapeRange(1).OLEFormat.DoVerb VerbIndex:=1
End Sub


I am able to go to page 22, and I needed to activate and open the picture on that page into Excel so therefore I can pick up the amount in relevant cell.

 

[MakeItSo]

Sure it's a shape and not an inline shape?
Here's some code of mine I use to "export" Visio graphics embedded in Word documents

Sub ExportVSDs()

On Error Resume Next
Dim inl As InlineShape, vis As Visio.Application, vsd As Visio.Document
Dim i As Integer
i = 0
    For Each inl In ActiveDocument.InlineShapes
        If Left(inl.OLEFormat.ClassType, 13) = "Visio.Drawing" Then
            i = i + 1
            [b]inl.OLEFormat.DoVerb verbindex:=1 '=Context menu: visio object->Open
            Set vis = GetObject(, "Visio.Application")
            Set vsd = vis.ActiveDocument
            vsd.SaveAs ActiveDocument.Path & "\Inline_" & i & ".vsd"
            vsd.Close[/b]
        End If
    Next inl
    vis.Quit
End Sub

It works fine, object is opened and dealt with in Visio, no prob.

Hope this helps,

Andy

[navy]"We had to turn off that service to comply with the CDA Bill."[/navy]
- The Bastard Operator From Hell

 

[fumei]

That is because you are actually using an instance of Visio.

Set vis = GetObject(, "Visio.Application")

xlStar, you declare the following, but never set them.

Dim xlApp As Object
Dim xlWkb As Object

faq219-2884

Gerry
My paintings and sculpture

 

[MakeItSo]

The problem is a different one:

I have just tried and

Selection.GoTo What:=wdGoToObject, Which:=wdGoToNext, Count:=1, Name:="Excel.Sheet.8"

is what comes out of the recorder, but it does not actually select the Excel table if inserted as said.

In my case, its name was "Object 5" rather than "Excel" whatever.

Not sure whether "GoTo" is the right approach to this at all, hence my proposal with using the shapes/inline shapes collection.

Cheers,
Andy

[navy]"We had to turn off that service to comply with the CDA Bill."[/navy]
- The Bastard Operator From Hell

 

[MakeItSo]

Sorry Gerry,

there is an additional problem with not setting the object(s).



[navy]"We had to turn off that service to comply with the CDA Bill."[/navy]
- The Bastard Operator From Hell