Solved

3 excel files to be linked in 1 excel file

Posted on 2015-02-09
3
87 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
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 will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

738 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