One can insert an object in word by either linking or embedding. We have already seen How to Read and Edit Embedded objects using VBA, The following code will throw light on accessing a linked object from Word (Excel sheet) and editing the same.
Sub Edit_Linked_Excel_Objects()
Dim oXL As Excel.Application ' Excel App Object
Dim oWB As Excel.Workbook ' Workbook Object
Dim sWB As String ' Linked String
Dim oIShape As InlineShape ' Inline Shape Object
On Error GoTo Err_Report
Set oXL = New Excel.Application
For Each oIShape In ActiveDocument.InlineShapes
If InStr(1, oIShape.OLEFormat.ProgID, "Excel") Then
' Check if the Object is Linked
If oIShape.Type = wdInlineShapeLinkedOLEObject Then
' Get the Source Name of Linked Workbook
sWB = oIShape.LinkFormat.SourceFullName
If Len(Dir(sWB)) <> 0 Then
Set oWB = oXL.Workbooks.Open(sWB, , False)
oWB.Sheets(1).Range("A1").Value = "ID"
oWB.Save
oWB.Close False
oIShape.LinkFormat.Update
Else
MsgBox "Linked file not found"
End If
End If
End If
Next oIShape
Finally:
oXL.Quit
If Not oXL Is Nothing Then Set oXL = Nothing
If Not oWB Is Nothing Then Set oWB = Nothing
If Not oIShape Is Nothing Then Set oIShape = Nothing
Exit Sub
Err_Report:
MsgBox Err.Description & " - " & Err.Number
Err.Clear
GoTo Finally
End Sub
Posts
