CODEWITHSUNDEEP
vbaexcelms-word
X-Guy
X-Guy

Reputation: 1

programatically update-linked-named-range-of-excel-object-in-ms-word-2010

I have thoroughly read the above answer you posted in the link below.

Programmatically Update Linked Named Range of excel object in MS Word (2007)

I am facing issue while updating a shared drive path using the same above steps. My excel file is in a shared drive folder and I have tried putting in OLE objects manually, which I succeeded. While using a similar logic :

ActiveDocument.Bookmarks("R1").Range.InlineShapes.AddOLEObject filename:=filename _
  & "!Range1", LinkToFile:=True"

it gives me the below error:

Word is unable to create a link to the object you specified. Please insert the object directly into your file without creating a link

I am not able to figure out why this error comes up. Any help in this regard will be appreciated.

Note: I am updating a new range as well as the file location and file name. I have verified range in excel file is valid.

Thanks

Upvotes: 0

Views: 583

Answers (2)

macropod
macropod

Reputation: 13505

For wrapped objects, with early binding:

Dim wdApp As New Word.Application, wdDoc As Word.Document
Dim wdShp As Word.Shape, wdRng As Word.Range, i As Long, Fmt As Long, StrID As String, StrNm As String
Dim vRel As Long, vPos As Single, hRel As Long, hPos As Single, Hght As Single, Wdth As Single
Const strPath As String = "New Path"
With wdApp
  .Visible = True
  Set wdDoc = .Documents.Open(Filename:="C:\Users\" & Environ("Username") & "\Documents\Target Document.docx", _
    AddToRecentFiles:=False, Visible:=True)
  With wdDoc
    For i = .Shapes.Count To 1 Step -1
      With .Shapes(i)
        If Not .LinkFormat Is Nothing Then
          Set wdRng = .Anchor: StrID = .OLEFormat.progID: StrNm = "\" & .LinkFormat.SourceName
          Fmt = .WrapFormat.Type: Hght = .Height: Wdth = .Width
          vRel = .RelativeVerticalPosition:   vPos = .Top
          hRel = .RelativeHorizontalPosition: hPos = .Left
          .Delete
        With wdRng
          .Fields.Add Range:=.Duplicate, Type:=wdFieldEmpty, PreserveFormatting:=False, _
          Text:="LINK " & StrID & " " & Chr(34) & Replace(strPath & StrNm, "\", "\\") & Chr(34) & " " & _
            "6 - EW_RA!R2C17" & " \p"
          .End = .End + 1
          Set wdShp = .Fields(1).InlineShape.ConvertToShape
        End With
        With wdShp
          .WrapFormat.Type = Fmt: .Height = Hght: .Width = Wdth
          .RelativeVerticalPosition = vRel:   .Top = vPos
          .RelativeHorizontalPosition = hRel: .Left = hPos
          End With
        End If
      End With
    Next
    .Close True
  End With
  .Quit
End With

Upvotes: 0

macropod
macropod

Reputation: 13505

Since the link already exists, you shouldn't be using .AddOLEObject. You should instead be editing the filepath. For example:

Dim iShp As InlineShape
Const strPath As String = "New Path"
For Each iShp In ActiveDocument.InlineShapes
  With iShp
    If Not .LinkFormat Is Nothing Then
      With .LinkFormat
        .SourceFullName = Replace(.SourceFullName, .SourcePath, strPath)
      End With
      With .Field
        .Code.Text = Replace(.Code.Text, "5 - EW_RA!R2C17", "6 - EW_RA!R2C17")
        .Update
      End With
    End If
  End With
Next

Upvotes: 2

Related Questions