Hi
In my file I have external reference to multiple files.
Is there a way to update the external links;
* Without opening source file &
* Without going to Edit Links option.
I've tried below VBA code but it sometimes won't work. I don't know why. I've copied it from another forum, so really doesn't know how it works.
In my file I have external reference to multiple files.
Is there a way to update the external links;
* Without opening source file &
* Without going to Edit Links option.
I've tried below VBA code but it sometimes won't work. I don't know why. I've copied it from another forum, so really doesn't know how it works.
Code:
Private Sub Worksheet_Calculate()
Dim rWatchRange As Range
Dim sBook As String
Dim sFilePath As String
On Error Resume Next
Application.EnableEvents = False
Set rWatchRange = Range("B2:H2")
If Intersect(ActiveCell, rWatchRange) Is Nothing Then
Set rWatchRange = Nothing
Exit Sub
End If
sBook = ActiveCell
sFilePath = Range("FilePath")
Application.DisplayAlerts = False
ActiveCell.Offset(10, 0).Formula = _
"='" & sFilePath & "[" & sBook & ".xls]Sheet1'!R1C1"
Application.EnableEvents = True
Application.DisplayAlerts = True
On Error GoTo 0
End Sub
Last edited by a moderator: