Consolidating Data from Multiple Files

[AwooOOoo]

Hi,
I'm trying to consolidate from multiple files into a different file, but I'm having some trouble. Here is a simplified example of what I want;

File1.xlsx
Col1 Col2 Col3
111a 111b 111c
222a 222b 222c

File2.xlsx
Col1 Col2 Col3
666a 666b 666c
777a 777b 777c
888a 888b 888c

Summary file
Col1 Col2 Col3
111a 111b 111c
222a 222b 222c
666a 666b 666c
777a 777b 777c
888a 888b 888c

Seems easy enough and I've read on linked files and consolidation, but from what I can see this works on a fixed row/col count. I want to be able to add data (i.e. more rows) to the children files and by only opening the parent (summary) file have the new data present.

I've seen some different examples that are somewhat similar with VB script and I was hoping someone could offer some code that met these needs?

In the real example I don't have any file name consistency (i.e. numeric succession), but could list them on the summary file as there won't be that many and it will be rare another is added. I'd imagine the following;

[insert horrible pseudo code]
Read number of files from Sheet Files:A1
Read names of each file from A2:Ax
row offset = 0
for each file {
read how many rows contain data
read how many cols contain data
write data to row offset on Sheet 'Summary'
row offset = row offset + rows
}

I've read some implementations require that all files (parent and children) have to be open for this to work, but I'd like to avoid this if possible. Multiple people are going to be reading the summary file so if it is possible without having them install any add-ins this would be desirable.

If someone can help i would be greatly appreciated which would save me learning to write VB syntax for the rest of the night.

Kind Regards, Paul.

 

[pbornemeier]

Welcome to the Board!

1a. Do each of the input files contain a header row? (probably yes)
1b. If so, do you want to copy that row as well? (probably not)
2a. Are all of the input files in a single directory?
2b. If so, are they the only files in that directory?
3a. Do you want the summary file to pull data from each of the input files each time it is opened?
3b. Depending on the # if input files this could take some time.
as an alternative, the summary file could check a date/time stamp to see if any of the input files have been changed since the summary file was laste updated.
4. What should happen if an input file is being edited when the summary file is opened?
5. If someone has the summary file open and someone else opens a read-only copy of it, what should happen?

 

[AwooOOoo]

Hi,
Thanks for the post.

1a) Yes
1b) No (but doesn't matter)
2a) Yes
2b) Yes (all .xlsx apart from the summary file which i expect will need to be.xlsm ) I assume based on this answer we can skip the reading of file names from sheet in the summary file, but can just read them from the directory.

3a) Yes (from reading a Workbook_Open() trigger looks like what I need)
3b) I understand your input. I expect they will be only 10 or so files with perhaps 50 or so rows each so I don't think this should be a big issue. If it is sufficiently more complex to detect via a time stamp, I'm happy to run with direct reads to get it going.
4) Good question, i hadn't thought of that. What are my options? I assume i can just read in the 'last saved copy', perhaps with the option for a pop-up with a warning that 'File X is under edit'.
5) The summary file will be read-only for all if this helps. My expectation would be that each person that opened the summary (even simultaneously) would still have all the child files read, but there won't be changing the master at all themselves.

Excellent questions, I appreciate the help.

Regards, Paul.

 

[AwooOOoo]

I think I may be most of the way there, with a post I just found.

I'm using the "Merging a Range from All Workbooks in a Folder" script from the following http://msdn.microsoft.com/en-us/library/cc837974.aspx link. I'm doing the customizations mentioned at the bottom so that it will find the last row with the RDB_Last routine at the top of the page.

I still haven't quite got it working right, but it looks promising. If you think there are any short comings with this approach i'd appreciate and advice.

Regards, Paul.

 

[pbornemeier]

Here is a thread that discusses a similar problem.

 

[AwooOOoo]

Thank you very much for your help Phil =)