Updating link paths with Word VBA

Question

I have the following links to Excel in a word document:

The issue is that when changes are made the document version on both the excel sheet referenced and the word document change.

As such I will need to update the link path.

I would like to do this with a VBA macro and here is what I have so far:

Sub Replace_Link()
Dim iShp As InlineShape
Dim exWb As Excel.Workbook
For Each iShp In ActiveDocument.InlineShapes
With ActiveDocument
        Set exWb = objExcel.Workbooks.Open(.Path & "\" & Replace$(.Name, ".docm", ".xlsm"))
    End With
  With iShp
     If Not .OLEFormat Is Nothing Then
      If Split(.OLEFormat.ClassType)(0) = "Excel" Then
        .LinkFormat.SourcePath = exWb
  End If
End If
  End With
Next
End Sub

I am getting a 'User defined type not defined' for the 'Dim exWb As Excel.Workbook'. I'm also pretty sure that I'll need more code to include the 'Worksheet' type links that cannot be updated with the 'inlineShape' command.

Is this something anyone can help with? TIA

Answer 1

Have you added a reference in your VBA project to the "Microsoft Excel 14.0 Object Library"? (NB: The 14.0 may be different depending on which version of Excel you have installed)

In your VBA window, from the Tools menu, select References to bring up the References - Project dialog. Then scroll down the list and look for the Microsoft Excel library and tick the box (it should then appear at the top of your list of references).