none
How to identify any link broken OLE objects by word macro RRS feed

  • Question

  • Hi,

    Is there any method or macro to find any link broken OLE objects (Embedded Excel, Word...etc.) and mark them by using MS Word comments.

    Any inputs would be much appreciated.

    Thanks!


    .

    Monday, August 15, 2016 12:31 PM

Answers

  • The code you require is rather more complicated than was posted above. The following should address the question of whether all external links (other than hyperlinks) in the main story range point to a valid file:

    Sub Demo()
    Dim iShp As InlineShape, Shp As Shape
    Dim Fld As Field, Cmnt As Comment, bAdd As Boolean
    With ActiveDocument
      For Each iShp In .InlineShapes
        With iShp
          If Not .LinkFormat Is Nothing Then
            If Dir(.LinkFormat.SourceFullName) = "" Then
              .Range.Comments.Add .Range, "Bad link"
            End If
          End If
        End With
      Next
      For Each Shp In .Shapes
        With Shp
          If Not .LinkFormat Is Nothing Then
            If Dir(.LinkFormat.SourceFullName) = "" Then
              .Anchor.Comments.Add .Anchor, "Bad link"
            End If
          End If
        End With
      Next
      For Each Fld In .Fields
        With Fld
          If Not .LinkFormat Is Nothing Then
            If Dir(.LinkFormat.SourceFullName) = "" Then
              bAdd = False
              For Each Cmnt In .Result.Comments
                If Cmnt.Range.Text = "Bad link" Then
                  bAdd = True
                  Exit For
                End If
              Next
              If bAdd = True Then .Result.Comments.Add .Result, "Bad link"
            End If
          End If
        End With
      Next
    End With
    End Sub

    That said, even the presence of a valid file does not prove that the link's target (e.g. a bookmark or range) within the file is also valid. An INCLUDETEXT field or a LINK field, for example, might point to a deleted bookmark in a Word document or to a deleted range name in an Excel workbook. Yet more code would be required to address such possibilities.


    Cheers
    Paul Edstein
    [MS MVP - Word]

    • Marked as answer by Sam1085 Tuesday, August 16, 2016 12:37 PM
    Tuesday, August 16, 2016 12:28 PM
  • You cannot find deleted bookmarks; at best you can find references to them. For example, the following revision to the code in my previous post will do that:

    Sub Demo()
    Dim iShp As InlineShape, Shp As Shape
    Dim Fld As Field, Cmnt As Comment, bAdd As Boolean
    With ActiveDocument
      For Each iShp In .InlineShapes
        With iShp
          If Not .LinkFormat Is Nothing Then
            If Dir(.LinkFormat.SourceFullName) = "" Then
              .Range.Comments.Add .Range, "Bad link"
            End If
          End If
        End With
      Next
      For Each Shp In .Shapes
        With Shp
          If Not .LinkFormat Is Nothing Then
            If Dir(.LinkFormat.SourceFullName) = "" Then
              .Anchor.Comments.Add .Anchor, "Bad link"
            End If
          End If
        End With
      Next
      For Each Fld In .Fields
        With Fld
          Select Case .Type
            Case wdFieldRef, wdFieldPageRef, wdFieldFootnoteRef, wdFieldNoteRef
              If ActiveDocument.Bookmarks.Exists(Split(Trim(.Code.Text), " ")(1)) = False Then
                .Result.Comments.Add .Result, "Bad link"
              End If
            Case wdFieldHyperlink
              With .Result.Hyperlinks(1)
                If (.Address = "") Then
                  If ActiveDocument.Bookmarks.Exists(.SubAddress) = False Then
                    .Range.Comments.Add .Range, "Bad link"
                  End If
                End If
              End With
            Case Else
              If Not .LinkFormat Is Nothing Then
                If Dir(.LinkFormat.SourceFullName) = "" Then
                  bAdd = False
                  For Each Cmnt In .Result.Comments
                    If Cmnt.Range.Text = "Bad link" Then
                      bAdd = True
                      Exit For
                    End If
                  Next
                  If bAdd = True Then .Result.Comments.Add .Result, "Bad link"
                End If
              Else
            End If
          End Select
        End With
      Next
    End With
    End Sub

    Another thing to bear in mind is that embedded objects, although not inserted as links, may contain their own references to external files. You might, for example, have an embedded (not linked) Excel worksheet that has cells with formulae referencing external files. Again, without considerably more code, it would not be possible to test all of those possibilities.


    Cheers
    Paul Edstein
    [MS MVP - Word]

    • Marked as answer by Sam1085 Wednesday, August 17, 2016 9:16 AM
    Tuesday, August 16, 2016 10:08 PM

All replies

  • >>Is there any method or macro to find any link broken OLE objects (Embedded Excel, Word...etc.) and mark them by using MS Word comments.

    I think you could use Field.Update Method (Word) to check if the link is invalid after updating it.

     

    Sub tt()

    For Each linkField In ActiveDocument.Fields

    If linkField.Update = 0 Then

    linkField.Select

    ActiveDocument.Comments.Add _

    Range:=Selection.Range, Text:="Invalid"

    End If

    Next

    End Sub

    Tuesday, August 16, 2016 8:30 AM
    Moderator
  • Hi Celeste,

    Thank you for your reply. Yes, Correct. Actually I need to check if the link is invalid after updating it (F9). Please see below example.

    I've linked two excel files as embedded files. Also enabled link to file check box while linking the file. Then I've removed 2nd excel file. Then receive the following error massage when update the fields in the entire document.

    I tried the above code. But I didn't get any changes. Can you please check this again.

    Thanks!


    .

    Tuesday, August 16, 2016 10:32 AM
  • Hi,

    What is your office version?

    I am using Office2016. And I am working fine with the code above.

    After updating all the field(Ctrl+A then F9), Word shows the alert and the fields are updated.

    Then run the macro, comment could successfully be added.


     

    Since you are manually updating and doesnt get any change, I suggest you update the fields at runtime, please try the following code.

    Sub tt()
    'suppress the alert windows
    Application.DisplayAlerts = False
    For Each linkField In ActiveDocument.Fields
    linkField.Update 'update all the field
    'if failed to update then add a comment
    If linkField.Update = 0 Then
    linkField.Select
    ActiveDocument.Comments.Add _
    Range:=Selection.Range, Text:="Invalid"
    End If
    Next
    End Sub



    Tuesday, August 16, 2016 11:31 AM
    Moderator
  • Hi Celeste,

    Thanks for the quick response. I'm using MS Office 2010. I think that's the reason of the above codes not works for me. How can compatible the above code with 2010 version.


    .

    Tuesday, August 16, 2016 11:55 AM
  • The code you require is rather more complicated than was posted above. The following should address the question of whether all external links (other than hyperlinks) in the main story range point to a valid file:

    Sub Demo()
    Dim iShp As InlineShape, Shp As Shape
    Dim Fld As Field, Cmnt As Comment, bAdd As Boolean
    With ActiveDocument
      For Each iShp In .InlineShapes
        With iShp
          If Not .LinkFormat Is Nothing Then
            If Dir(.LinkFormat.SourceFullName) = "" Then
              .Range.Comments.Add .Range, "Bad link"
            End If
          End If
        End With
      Next
      For Each Shp In .Shapes
        With Shp
          If Not .LinkFormat Is Nothing Then
            If Dir(.LinkFormat.SourceFullName) = "" Then
              .Anchor.Comments.Add .Anchor, "Bad link"
            End If
          End If
        End With
      Next
      For Each Fld In .Fields
        With Fld
          If Not .LinkFormat Is Nothing Then
            If Dir(.LinkFormat.SourceFullName) = "" Then
              bAdd = False
              For Each Cmnt In .Result.Comments
                If Cmnt.Range.Text = "Bad link" Then
                  bAdd = True
                  Exit For
                End If
              Next
              If bAdd = True Then .Result.Comments.Add .Result, "Bad link"
            End If
          End If
        End With
      Next
    End With
    End Sub

    That said, even the presence of a valid file does not prove that the link's target (e.g. a bookmark or range) within the file is also valid. An INCLUDETEXT field or a LINK field, for example, might point to a deleted bookmark in a Word document or to a deleted range name in an Excel workbook. Yet more code would be required to address such possibilities.


    Cheers
    Paul Edstein
    [MS MVP - Word]

    • Marked as answer by Sam1085 Tuesday, August 16, 2016 12:37 PM
    Tuesday, August 16, 2016 12:28 PM
  • Thanks Paul,

    This is works for me as I needed.

    Additionally can find deleted bookmarks in the same document?


    .

    Tuesday, August 16, 2016 12:58 PM
  • You cannot find deleted bookmarks; at best you can find references to them. For example, the following revision to the code in my previous post will do that:

    Sub Demo()
    Dim iShp As InlineShape, Shp As Shape
    Dim Fld As Field, Cmnt As Comment, bAdd As Boolean
    With ActiveDocument
      For Each iShp In .InlineShapes
        With iShp
          If Not .LinkFormat Is Nothing Then
            If Dir(.LinkFormat.SourceFullName) = "" Then
              .Range.Comments.Add .Range, "Bad link"
            End If
          End If
        End With
      Next
      For Each Shp In .Shapes
        With Shp
          If Not .LinkFormat Is Nothing Then
            If Dir(.LinkFormat.SourceFullName) = "" Then
              .Anchor.Comments.Add .Anchor, "Bad link"
            End If
          End If
        End With
      Next
      For Each Fld In .Fields
        With Fld
          Select Case .Type
            Case wdFieldRef, wdFieldPageRef, wdFieldFootnoteRef, wdFieldNoteRef
              If ActiveDocument.Bookmarks.Exists(Split(Trim(.Code.Text), " ")(1)) = False Then
                .Result.Comments.Add .Result, "Bad link"
              End If
            Case wdFieldHyperlink
              With .Result.Hyperlinks(1)
                If (.Address = "") Then
                  If ActiveDocument.Bookmarks.Exists(.SubAddress) = False Then
                    .Range.Comments.Add .Range, "Bad link"
                  End If
                End If
              End With
            Case Else
              If Not .LinkFormat Is Nothing Then
                If Dir(.LinkFormat.SourceFullName) = "" Then
                  bAdd = False
                  For Each Cmnt In .Result.Comments
                    If Cmnt.Range.Text = "Bad link" Then
                      bAdd = True
                      Exit For
                    End If
                  Next
                  If bAdd = True Then .Result.Comments.Add .Result, "Bad link"
                End If
              Else
            End If
          End Select
        End With
      Next
    End With
    End Sub

    Another thing to bear in mind is that embedded objects, although not inserted as links, may contain their own references to external files. You might, for example, have an embedded (not linked) Excel worksheet that has cells with formulae referencing external files. Again, without considerably more code, it would not be possible to test all of those possibilities.


    Cheers
    Paul Edstein
    [MS MVP - Word]

    • Marked as answer by Sam1085 Wednesday, August 17, 2016 9:16 AM
    Tuesday, August 16, 2016 10:08 PM
  • Thanks Paul,

    That's what actually I needed.


    .

    Wednesday, August 17, 2016 9:17 AM