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.

    Yours,
    Chandoo
  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

    Hui...

  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

    Messages:
    19
    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 (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

    Messages:
    19
    Hai

    Can anyone help me in this please.
  3. charlesdh

    charlesdh New Member

    Messages:
    29
    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 likes this.
  4. charlesdh

    charlesdh New Member

    Messages:
    29
    Junarkar,

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

    Charles
    Junarkar likes this.
  5. Junarkar

    Junarkar New Member

    Messages:
    19
    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

Share This Page