Wednesday, August 25, 2010

How to edit Linked Objects using Word VBA

How to open and edit Linked Excel files from Word using VBA

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


1 comment:

  1. Anonymous7:40 AM

    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.
    I 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

    ReplyDelete

StumbleUpon
Share on Facebook
Related Posts Plugin for WordPress, Blogger...
Download Windows Live Toolbar and personalize your Web experience! Add custom buttons to get the information you care about most.