Solved

3 excel files to be linked in 1 excel file

Posted on 2015-02-09
3
83 Views
Last Modified: 2015-02-09
Hello Experts,

My problem is:

I have 3 excel file which needs to be connected to each other in 1 excel file

each of this 3 files has a sigle sheet that i need to link in a sheets of 1 excel file

example

file 1 link to sheet 1
file 2 link to sheet 2
file 3 link to sheet 3

so that everytime i update one of the files in that 3 excel file it will automatically update also the linked excel file.

I hope that you can help me

Thanks.

Czher
0
Comment
Question by:Czher
  • 2
3 Comments
 
LVL 18

Expert Comment

by:Simon
ID: 40598025
How many cells do you need to link?

Can't you just set the formulae on the linked files to reference the master file? Every time the linked files open they would look up the latest values from the master file. This should work unless you actually need to copy values across to the other files (e.g. if they won't have the connectivity to be able to update links when next opened).
0
 

Author Comment

by:Czher
ID: 40598029
simonadept,

I need the whole sheet in those 3 files (1 sheet per file) to be connected in the master file each sheet in 3 files linked to 3 sheets in the master file,  

Czher
0
 
LVL 18

Accepted Solution

by:
Simon earned 500 total points
ID: 40598069
OK, are you looking for a scripted process to replace the content of the sheet in each of the 3 files with the corresponding sheet from the master file?

This code assumes you want to use the same sheet names in the slave files as in the master file e.g. Sheet1=>Sheet1
It clears the cells in the destination (slave) files and copies the used range from the sheet of the same name from the master file.

Sub OverwriteFromSameSheetnameInThisFile()
Dim mf As Workbook
Dim wb As Workbook
Dim rng As Range
Dim Destinations(3) As String
Dim destPath As String
Dim sheetName As String
Dim x As Integer

'Populate the array with the filepath and sheet name for each destination workbook, separated by a comma
'This currently assumes that the sheetname will match a sheetname in the master file
Destinations(1) = "C:\LocalDev\EE\Slave1.xlsx,Sheet1"
Destinations(2) = "C:\LocalDev\EE\Slave2.xlsx,Sheet1"
Destinations(3) = "C:\LocalDev\EE\Slave3.xlsx,Sheet1"

Set mf = ThisWorkbook
For x = 1 To UBound(Destinations)
destPath = Split(Destinations(x), ",")(0)
sheetName = Split(Destinations(x), ",")(1)
Set rng = Sheets(sheetName).UsedRange
Set wb = Workbooks.Open(destPath)
With wb.Sheets(sheetName)
    .Cells.Clear
    .Range(rng.Address).Value = rng.Value
End With
wb.Close savechanges:=True
Next x
MsgBox "Finished"
End Sub

Open in new window

0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

26 Experts available now in Live!

Get 1:1 Help Now