Link workbooks or files

In Excel, you can write VBA code to link different Excel workbooks or files so that any change made to a source workbook or file will automatically update the data in the destination workbook or file. Now let's say you have a source workbook called source.xlsx and a destination workbook called destination.xlsx. You want to have automatic change of data in cell A1 in the Sheet1 of the destination workbook after the change to the data in cell A1 in the Sheet1 of the source workbook. To accomplish this task you will follow the steps below.

-First, open the source workbook. Then press Alt+F11 to open the Visual Basic Editor.
-Double-click the Sheet1. On the Object drop box, select Worksheet. And on the Procedure drop box, select Change.
-Paste the following code to the Worksheet change procedure:

Dim des As String
Dim WatchCell As Range
Dim Intersecttion As Range
des = "destination.xlsx"
Set desfile = Workbooks.Open(des)
desfile.Activate
Set WatchCell = Range("A1")
Set Intersection = Intersect(Target, WatchCell)
If Intersection Is Nothing Then 'do nothing
Else
desfile.Sheets("Sheet1").Range("A1").Formula = "='[source.xlsx]Sheet1'!$A$1"
End If
desfile.Save
desfile.Close

To test the code, now go to the Sheet1 of the source workbook and enter a value in the cell A1. Then open the destination workbook file to see the change.

Posted by: Lavy | post date: 12-21-2013 | Subject: MS Excel




This website intents to provide free and high quality tutorials, examples, exercises and solutions, questions and answers of programming and scripting languages:
C, C++, C#, Java, VB.NET, Python, VBA,PHP & Mysql, SQL, JSP, ASP.NET,HTML, CSS, JQuery, JavaScript and other applications such as MS Excel, MS Access, and MS Word. However, we don't guarantee all things of the web are accurate. If you find any error, please report it then we will take actions to correct it as soon as possible.