Solved

3 excel files to be linked in 1 excel file

Posted on 2015-02-09
3
86 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

756 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