HI all,
i've been working on this for a while now and cant seem to figure out why this is happening.
I'm currently using the below code to update the source file for multiple links with a MS WORD document.
all links are to a single excel workbook, however the pathnames change an i need a quick and easy way to change the link source.
The problem i am encountering is that although the below code works - somewhat, it doesn't change all the links. The fieldscount only counts the number of fields until it hits a Chart object, and then stops, not counting any other fields in the document, and leaves the source unchanged. The fields is does identify are changed without fault.
Same is for ChartsCount.
VBA.PNG
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
Dim ChartCount 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
MsgBox "Field Count is" & fieldCount
For x = 1 To fieldCount
ActiveDocument.Fields(x).LinkFormat.SourceFullName = newFile
Next x
'update charts
ChartCount = ActiveDocument.InlineShapes.Count
MsgBox "Chart Count is" & ChartCount
For x = 1 To ChartCount
ActiveDocument.InlineShapes(x).LinkFormat.SourceFullName = newFile
Next x
End Sub
Thanks heaps for your help
Bookmarks