• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Auto update external links

Junarkar

Member
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.

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:
hi,
Are you wanting to Update the Links when opening the workbook? Or use code to update?

Charles
I will be out the rest of the day. But will take a look at it tomorrow.
 
Junarkar,

If possible post a workbook. Also in your code I do not see a "Link" Ie :
"Some file path",UpdateLinks:=True".

Charles
 
Junarkar,

If possible post a workbook. Also in your code I do not see a "Link" Ie :
"Some file path",UpdateLinks:=True".

Charles
Hi Mr.Charls

The codes are now looks like working fine. I have used the links in the cell. i.e., i entered "=" and then clicked on the required column on the source file.
I don't know how this code works since I got it from another website. Will post a sample workbook. May be you can find some flaws.

Thanks
 
Back
Top