Using VBA can help us with a lot of things in Excel. It does not necessarily mean that the help means that the code that is being executed will create new sheets or input new values in our cells.
Sometimes, the code that we write can serve us to update certain things. Such is the case with updating the links in our Workbooks.
We will show how exactly VBA can be used for this purpose in the example below.
Enabling and Disabling Link Updates
The first thing that needs to be communicated is that we can define links updating in Excel itself at the Workbook level.
To check the options available, we need to go to File tab >> Options >> Trust Center >> Trust Center Settings:
Once we click on it, we will see the Security settings for Workbook links option, under which we will choose Disable automatic update of Workbook Links:
As seen, we have two more options: Enable automatic update for all Workbook Links (not recommended because these links can contain potentially harmful and dangerous files) and Prompt user on automatic update for Workbook Links. When you choose the second option, upon entering the Workbook, you will see the following message:
But, since we have chosen the third option available, we will not get these messages in our Workbook.
Make Excel Update Links Using VBA
For our exercise to work, we need to add a link to our file. We will open another Workbook (in our case we will name it Book2) and insert values into cells A1 and A2. Next thing, we will link the values of these cells to our original Workbook:
If both of the Workbooks are open, changes in Book2 will automatically be reflected in our original Workbook. However, let’s say we closed the original Workbook and made the changes in Book2 to values 250 and 200.
We will open the original Workbook again, but our cells will not be updated. For this to work, we can create a simple code in VBA.
To open the VBA module, we will click on ALT + F11, then right-click on the left window that appears, and then go to Insert >> Module:
When the new window appears in which we should write our code, we can start with it.
The code will be very simple, and it is:
|
1 2 3 |
Sub UpdatingLinks() ActiveWorkbook.UpdateLink, Type:=xlExcelLinks End Sub |
As seen, the UpdateLink method is located in the Workbook object. This method has two optional arguments: Name and Type (xlLinkTypeExcelLinks if we want to return Excel files and xlLinkTypeOLELink if we want to return OLE sources).
For our example, we could skip the type altogether and would end up with the same results. When we execute our code by pressing the F5 on our keyboard (while in the module) we will see that we have our numbers updated:
And that is everything we need to do to achieve our results.