Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

3 excel files to be linked in 1 excel file

Posted on 2015-02-09
3
Medium Priority
?
93 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 2000 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

This article describes a serious pitfall that can happen when deleting shapes using VBA.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

963 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