Automating Workbook Link Updates with VBA in Excel. In this guide, discover how to effortlessly update external workbook links at specific intervals using VBA in Excel code.
Query: “How can I automatically refresh external workbook links every 10 seconds?”
Automating Workbook Link Updates with VBA in Excel
To achieve a 10-second interval update for an Excel file, follow these steps:
- Create two Excel files: Source.xlsm & Target.xlsm.
- Open both files.
- In the Source workbook, utilize the RAND function within the range A1:D10.
- Copy this range using the ‘Paste Special’ command in the Target Workbook, selecting ‘Paste Link’.
- In the Target workbook, access the VB editor.
- Click on the ‘Developer’ tab.
- Within the ‘Code’ group, choose ‘Visual Basic’.
- Inside the ‘ThisWorkbook’ module, insert the following code:
Private Sub Workbook_Open()
Call Update_Links
End Sub
- Click on ‘Insert’ and select ‘Module’.
- This will create a new module. Input the following code into the Module:
Sub Update_Links()
On Error Resume Next
ActiveWorkbook.UpdateLink Name:=ActiveWorkbook.LinkSources
Application.OnTime DateAdd("s", 10, Now), "Update_Links"
End Sub
The code provided continuously runs at 10-second intervals, updating the links as intended.
