none
Do not update links unless button clicked RRS feed

  • Question

  • Hello,

    Have numerous files that link to another "master" file for specific tax rates.

    Sometimes the tax rates are updated.

    Those updates are necessary for newer files going forward, but *bad* for older files with previous tax rates in place.

    To complicate matters, there are a few necessary links that should be updated always/normally.

    Solution as I see it is to have "update links" disabled in vba --- *for that one file*, call it "TaxRates" --- only to be updated when a button is clicked ie "Update Tax Rates".

    Any ideas anyone?

    Thanks in advance.

     - Mik

    Friday, April 22, 2016 12:07 PM

Answers

  • Re:  update links

    Excel workbooks have had an UpdateLinks property since at least the Excel 10 version.
    I have never used it, so you get to test it.
    '---
    Constant                          Value
    xlUpdateLinksAlways          3
    xlUpdateLinksNever            2
    xlUpdateLinksUserSetting   1
    xlUpdateState                     1
    '---

    You can try this code attached to your button...

    Sub linkTest()
     Dim lngSetting As Long
     lngSetting = ThisWorkbook.UpdateLinks
     If lngSetting > 2 Then
       ThisWorkbook.UpdateLinks = xlUpdateLinksNever
       'add code here to update button caption
     Else
       ThisWorkbook.UpdateLinks = xlUpdateLinksAlways
       'add code here to update button caption
     End If
    End Sub

    '---

    Jim Cone
    Portland, Oregon USA
    https://goo.gl/IUQUN2 (Dropbox)
    (free & commercial excel add-ins & workbooks)

    • Proposed as answer by David_JunFeng Monday, May 2, 2016 2:08 PM
    • Marked as answer by David_JunFeng Tuesday, May 3, 2016 3:20 AM
    • Edited by James Cone Monday, October 31, 2016 1:08 PM
    Friday, April 22, 2016 2:51 PM
  • >>>Looks like this updates *all* links.

    Need a selective update if possible.<<<

    According to your description, you could use ActiveCell.Formula property to return a value that represents the object's formula, then you could use string function to retrieve updating file location.
    '='D:\[Data.xlsx]Sheet1'!A1
    filePath = ActiveCell.Formula
    'you logic code to retrieve file path from formula
    'get file path "D:\Data.xlsx"
    filePath = "D:\Data.xlsx"
    ActiveWorkbook.UpdateLink Name:=filePath, Type:=xlExcelLinks


    Tuesday, April 26, 2016 9:10 AM

All replies

  • Re:  update links

    Excel workbooks have had an UpdateLinks property since at least the Excel 10 version.
    I have never used it, so you get to test it.
    '---
    Constant                          Value
    xlUpdateLinksAlways          3
    xlUpdateLinksNever            2
    xlUpdateLinksUserSetting   1
    xlUpdateState                     1
    '---

    You can try this code attached to your button...

    Sub linkTest()
     Dim lngSetting As Long
     lngSetting = ThisWorkbook.UpdateLinks
     If lngSetting > 2 Then
       ThisWorkbook.UpdateLinks = xlUpdateLinksNever
       'add code here to update button caption
     Else
       ThisWorkbook.UpdateLinks = xlUpdateLinksAlways
       'add code here to update button caption
     End If
    End Sub

    '---

    Jim Cone
    Portland, Oregon USA
    https://goo.gl/IUQUN2 (Dropbox)
    (free & commercial excel add-ins & workbooks)

    • Proposed as answer by David_JunFeng Monday, May 2, 2016 2:08 PM
    • Marked as answer by David_JunFeng Tuesday, May 3, 2016 3:20 AM
    • Edited by James Cone Monday, October 31, 2016 1:08 PM
    Friday, April 22, 2016 2:51 PM
  • Thanks Jim.

    Looks like this updates *all* links.

    Need a selective update if possible.

    Friday, April 22, 2016 4:22 PM
  • >>>Looks like this updates *all* links.

    Need a selective update if possible.<<<

    According to your description, you could use ActiveCell.Formula property to return a value that represents the object's formula, then you could use string function to retrieve updating file location.
    '='D:\[Data.xlsx]Sheet1'!A1
    filePath = ActiveCell.Formula
    'you logic code to retrieve file path from formula
    'get file path "D:\Data.xlsx"
    filePath = "D:\Data.xlsx"
    ActiveWorkbook.UpdateLink Name:=filePath, Type:=xlExcelLinks


    Tuesday, April 26, 2016 9:10 AM