Excel – Updating Microsoft Word link item through VBA

microsoft excelmicrosoft wordvba

Is it possible to change the Item of a link in MS Word referencing to an Excel spreadsheet, pragmatically, through the use of VBA?

I have found discussions on how about to change the file source, but not the Item, shown below in the edit link menu from MS Word 2010:

Edit link tab

The exact same question has been already asked here, but without any answers, about 2 years ago. I have also looked into the LinkFormat property documentation, which is used to edit the Source of a link, but led me nowhere.

Any ideas on how to solve the issue are appreciated.

Background: I have a Excel spreadsheet feeding a word document with data. I am trying to use this document as a template, where depending on the analysis I would change the spreadsheet feeding the data to the word document. The tables in the spreadsheet (not formated as such) can vary in range. However, they have the same number of columns. Since the number of rows vary, I would have to change the Item field accordingly.

Edit

I have provided 2 sample files as a MWE of the issue. They can be found here.

Best Answer

I found a work arround to this. Instead of changing the Itens, I created a VBA script to name all the ranges linked. So instead of changing the link item in Word, I change the named range value in excel. The link item remains the same, but it points to a range that can be pragmatically modified. Here's the code I put together to put names to ranges:

Sub CreateNamedRanges()

Dim i As Integer
For i = 1 To Worksheets.Count
     sheetName = "Mysheet" & i
     varName = "Myvar" & CStr(i)
     Set Rng = Sheets(sheetName).Range("G6:I9")
     ActiveWorkbook.Names.Add Name:=varName, RefersTo:=Rng
Next i

End Sub

The downside of this approach is that I had to recreate (link) manually all the connections I did previously. To change the file Source I used the code in the question link. For completion's sake I am writing it here:

Sub changeSource()
Dim dlgSelectFile As FileDialog  'FileDialog object
Dim thisField As Field
Dim selectedFile As Variant    'must be Variant to contain filepath of selected item
Dim newFile As Variant
Dim fieldCount As Integer



'create FileDialog object as File Picker dialog box
Set dlgSelectFile = Application.FileDialog(FileDialogType:=msoFileDialogFilePicker)



With dlgSelectFile
'use Show method to display File Picker dialog box and return user's action
    If .Show = -1 Then

        'step through each string in the FileDialogSelectedItems collection
        For Each selectedFile In .SelectedItems
            newFile = selectedFile    'gets new filepath
        Next selectedFile
    Else   'user clicked cancel
    End If
End With
Set dlgSelectFile = Nothing



'update fields
fieldCount = ActiveDocument.Fields.Count
For x = 1 To fieldCount
    ActiveDocument.Fields(x).LinkFormat.SourceFullName = newFile
Next x



End Sub

Finally, to update all the links at once:

Sub AutoOpen()
    ActiveDocument.Fields.Update
End Sub
Related Question