How to make Excel update links using VBA

by | Nov 13, 2018 | Excel Macros, Excel Tips & Tricks

How to make Excel update links using VBA

Excel doesn't automatically update any external links in your workbooks and this can become a hassle if it's something happening frequently. In this article, we're going to show you how to make Excel update links automatically using VBA.

 

How to make Excel update links

Links can be updated using a special method named UpdateLink under the Workbook object. The UpdateLink method takes two optional arguments:

  • Name: The name of the Microsoft Excel workbook or DDE/OLE link to be updated, as returned from the LinkSources
  • Type: One of the parameters for XlLinkType is specifying the type of link. Use xlLinkTypeExcelLinks for Excel files and xlLinkTypeOLELinks for OLE sources.

To update all links in the active workbook, use ActiveWorkbook.LinkSources for the Name and xlExcelLinks for Type arguments.

ActiveWorkbook.UpdateLink Name:=ActiveWorkbook.LinkSources, Type:=xlExcelLinks

Since the default value of the Type parameter is xlExcelLinks, you can omit that argument as well.

You can use codes in two ways:

  • Module
  • Immediate Window

In the Module method, you need to add the module into the workbook or the add-in file. Copy and paste the code into the module to run it. The main advantage of the module method is that it allows saving the code in the file, so that it can be used again later. Furthermore, the subroutines in modules can be used by icons in the menu ribbons or keyboard shortcuts. Remember to save your file in either XLSM or XLAM format to save your VBA code.

The Immediate Window method, on the other hand, is essentially a quick and dirty method where you can simply copy and paste the code into the Immediate Window and press the Enter key to run it. Unfortunately, any code you use in the Immediate Window will not be saved. Also note that icons and keyboard shortcuts will not be available.

 

Update links in a worksheet

Module method:

Sub UpdateLinks()

 

       ActiveWorkbook.UpdateLink Name:=ActiveWorkbook.LinkSources, Type:=xlExcelLinks

 

End Sub

 

Immediate Window method:

ActiveWorkbook.UpdateLink Name:=ActiveWorkbook.LinkSources, Type:=xlExcelLinks