S
Sean via OfficeKB.com
Hello. I am try to write a sub to automate the process of changing all of
the Excel links in a Word doc by having the user point and click the new
file. There are over 200 links in each doc and they are set to update
manually. These links represent both in-line text and pictures (not .jpg
but Excel tables pasted as linked pictures). If I step through the code I
now have (below), the first link will change correctly, however, the
remaining links do not change and it appears that the sub is caught in a
loop. Any help is greatly appreciated. Thanks!
--------
Public Sub LinkToExcel()
' Early Binding required
Dim xlApp As Excel.Application
Dim xlWkb As Excel.Workbook
Dim strWorkbook As String
Dim strDefaultDir As String
Dim fldField As Field
strDefaultDir = "C:\"
With Dialogs(wdDialogFileOpen)
.Name = "*.xls"
ChangeFileOpenDirectory strDefaultDir
If .Display = -1 Then
strWorkbook = CurDir & Application.PathSeparator & Replace
(.Name, Chr(34), "")
Set xlApp = CreateObject("Excel.Application")
Set xlWkb = xlApp.Workbooks.Open(strWorkbook)
For Each fldField In ActiveDocument.Fields
If fldField.Type = wdFieldLink Then
With fldField.LinkFormat
.SourceFullName = strWorkbook
.AutoUpdate = False
End With
End If
Next fldField
xlWkb.Close False
xlApp.Quit
Set xlWkb = Nothing
Set xlApp = Nothing
End If
End With
End Sub
the Excel links in a Word doc by having the user point and click the new
file. There are over 200 links in each doc and they are set to update
manually. These links represent both in-line text and pictures (not .jpg
but Excel tables pasted as linked pictures). If I step through the code I
now have (below), the first link will change correctly, however, the
remaining links do not change and it appears that the sub is caught in a
loop. Any help is greatly appreciated. Thanks!
--------
Public Sub LinkToExcel()
' Early Binding required
Dim xlApp As Excel.Application
Dim xlWkb As Excel.Workbook
Dim strWorkbook As String
Dim strDefaultDir As String
Dim fldField As Field
strDefaultDir = "C:\"
With Dialogs(wdDialogFileOpen)
.Name = "*.xls"
ChangeFileOpenDirectory strDefaultDir
If .Display = -1 Then
strWorkbook = CurDir & Application.PathSeparator & Replace
(.Name, Chr(34), "")
Set xlApp = CreateObject("Excel.Application")
Set xlWkb = xlApp.Workbooks.Open(strWorkbook)
For Each fldField In ActiveDocument.Fields
If fldField.Type = wdFieldLink Then
With fldField.LinkFormat
.SourceFullName = strWorkbook
.AutoUpdate = False
End With
End If
Next fldField
xlWkb.Close False
xlApp.Quit
Set xlWkb = Nothing
Set xlApp = Nothing
End If
End With
End Sub