1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

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


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

Discussion in 'VBA Macros' started by Junarkar, Nov 5, 2018.

  1. Junarkar

    Junarkar New Member


    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 (vb):

    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: Nov 5, 2018
  2. Junarkar

    Junarkar New Member


    Can anyone help me in this please.
  3. charlesdh

    charlesdh Member

    Are you wanting to Update the Links when opening the workbook? Or use code to update?

    I will be out the rest of the day. But will take a look at it tomorrow.
    Junarkar likes this.
  4. charlesdh

    charlesdh Member


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

    Junarkar likes this.
  5. Junarkar

    Junarkar New Member

    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.


Share This Page