Skip to main content

Auto Refreshing/Updating External workbook links after x intervals

Hi Everyone,
In this article, I have explained that how you can force Excel to update External workbook links after a fixed interval or instantly. This involves a VBA code, so make sure to Enable Macros while opening workbook to get this done. Actually, I have used a Workbook_Open Event to initialise the Update_Links Subroutine and an Ontime Event that will recall the Update_Links Subroutine after an interval. So here we go:-

Steps:-
1. Open your Target workbook which contains External links to other workbooks.
2. Press Alt + F11. Double Click ThisWorkbook Module. Copy and Paste the following code there:-

       
Private Sub Workbook_Open()
 Call Update_Links
End Sub



3. Now insert a New Module and Paste following code there:-

       
Sub Update_Links()
 On Error Resume Next
 With ActiveWorkbook
        .UpdateLink .LinkSources(1), 1
 End With
 Application.OnTime DateAdd("s", 1, Now), "Update_Links"
End Sub

Note:- Change the Red Bold Portion accordingly to change the interval.

4. Save the workbook as Macro Enabled one. Reopen it while enabling Macros. You will see that Links are being updated. I am assuming that there are No Broken Links in the Target Workbook. This Technique may be used in the Live screens where data is requires to be reflected immediately on the screen.

Explanation:-
I have used .UpdateLink Method of ActiveWorkbook Object that again uses .LinkSources Method to return all Links in the Target Workbook to Update. Links can be of two types:-
1. Excel Links
2. OLE Links

For more information have a look at the following links:-

Workbook.UpdateLink Method
https://msdn.microsoft.com/en-us/library/office/ff195741.aspx

Workbook.LinkSources Method
https://msdn.microsoft.com/en-us/library/office/ff821922.aspx


You can see its implementation in the link below, where a Main Display file is linked to a Shared Excel workbook. When Users update the Shared workbook. The Shared Workbook gets saved automatically. And Using above explained technique, the Main Display File reflectes the changes in the Shared Workbook.

http://www.excelforum.com/excel-programming-vba-macros/1064815-auto-refresh-the-excel-every-1-second-2.html

Regards,
Vikas Gautam


Labels:

Comments

Post a Comment

Popular posts from this blog

Highlighting Duplicates across multiple sheets

Hi One and All, This time I have come up with some conditional formatting stuff. The aim is to highlight the duplicates across multiple sheets and with in the sheet as well. Assumptions:- 1. I am assuming that sheet names goes on like sheet1, sheet2, sheet3.... 2. The Target Column No. is same in all the sheets. I mean, as in the attached example, its Column A which is being targeted in both sheets.
2 comments
Read more

Automation:- Sending Invitation to Meeting Using Excel VBA

Hello Everyone, In one of the previous post, I wrote about automating Sending Emails using Excel VBA. This time I have come up with a pretty similar code. The code below sends Outlook Meeting Invitations to recipients on one click. here is the Code:- Sub Send_Invite_Auto() Dim olApp As Outlook.Application Dim olApt As AppointmentItem Set olApp = New Outlook.Application 'Creating Outlook Session Set olApt = olApp.CreateItem(olAppointmentItem) 'Creating an Appointment With olApt .Subject = "Enter the subject here." 'Subject .Start = DateAdd("d", 5, Now) 'Enter Date + Time here. .Recipients.Add ("example@gmail.com") 'Recipient Name, Alias, or any other Attribute. .MeetingStatus = olMeeting 'olAppointmentItem with Meeting status olMeeting 'becom
10 comments
Read more

Making Password Protected PDFs using Excel Vba and PDFtk Tool

Hi Everyone, This time, I have come up a VBA Code to generate Password protected PDFs using Excel. Actually, Excel Vba has .ExportAsFixedFormat Method to generate PDFs but this hasn't any Parameter which takes password to protect the PDFs. So I have used PDFtk Tool which provide Command Line Interface to make PDFs protected using Password. Actually, You can do various things using PDFtk Tool command line varying from creating, merging, Protecting and many other. So Download the PDFtk Tool from the following link:- Download PDFtk Tookit Here are the Steps:- 1. Install the PDFtk Toolkit. 2. Use the following code to Print or Export the Activesheet with a password.
8 comments
Read more