Automating Workbook Link Updates with VBA in Excel

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:

  1. Create two Excel files: Source.xlsm & Target.xlsm.
  2. Open both files.
  3. In the Source workbook, utilize the RAND function within the range A1:D10.

  1. Copy this range using the ‘Paste Special’ command in the Target Workbook, selecting ‘Paste Link’.

  1. In the Target workbook, access the VB editor.
    • Click on the ‘Developer’ tab.
    • Within the ‘Code’ group, choose ‘Visual Basic’.

  1. Inside the ‘ThisWorkbook’ module, insert the following code:
Private Sub Workbook_Open()
Call Update_Links
End Sub

  1. Click on ‘Insert’ and select ‘Module’.

  1. 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.
Compare items
  • Total (0)
Shopping cart