Solved

3 excel files to be linked in 1 excel file

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering 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

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;…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
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…

623 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