How To Automatic Update External Workbook Links After X Intervals Using VBA in Excel

In this article, you will learn how to automatic update external workbook links after x interval time using VBA code.

Q): How can I automatically refresh external workbook links after every 10 seconds?

To update excel file every 10 seconds; we need to follow the below steps:

  • We need to create two excel file 1)Source.xlsm & 2) Target.xlsm
  • Now we need to open both the files.
  • In Source workbook we have used RAND function in range A1:D10

 

 

  • Then copy this range using Paste Special command & choose Paste Link in Target Workbook refer below screenshot

 
img2img3

 

  • In Target workbook; launch VB editor
  • Click on Developer tab
  • From Code group select Visual Basic

 
img4

 

In thisworkbook module enter the following code

 

Private Sub Workbook_Open()

Call Update_Links

End Sub

 
img5

 

  • Click on Insert then Module

 
img6

 

This will create new module.

Enter the following code in the Module

Sub Update_Links()

On Error Resume Next

ActiveWorkbook.UpdateLink Name:=ActiveWorkbook.LinkSources

Application.OnTime DateAdd("s", 10, Now), "Update_Links"

End Sub

 
img7

 

The above code will keep on run after every 10 seconds & update the links.

Users are saying about us...

  1. I'm trying to use this code but I am getting a #VALUE error. I'm not sure where to look because my formula / link was working before, but it only updated the link when I opened the other workbook. I didn't change anything in the codes: where do I look to fix it? Thanks for your time!

Leave a Reply

Your email address will not be published. Required fields are marked *

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Google PlusVisit Us On Youtube