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
I need some assistance on a VBA macro and have been unable to find a solution through a search, though I believe this has likely been addressed previously.
ReplyDeleteI have a client that files SEC documents in a rigid Word format that does not support embedding Excel spreadsheets nor does it accommodate pasting multiple cell values from Excel – pasting multiple cell values does not allow each value to reside in a separate table field.
I need a VBA macro that will bring unformatted linked values from Excel into Word. Each value from Excel needs to reside in its own cell in a Word table. To get the desired result manually, I must copy and paste each cell (paste special, link, unformatted) resulting in a field in Word such as { LINK Excel.Sheet.12”Book1” “Sheet1!R73C17” \a \a}. I need to construct a macro that will bring large ranges from Excel into Word, placing each value in a Word table.
Any guidance on where to find code that accomplishes this is appreciated.
Thanks,
Mark