General Question about Collating hundreds of files

[MermaidNiki]

Hi,
I have a friend who has hundreds of files in one folder. They each have the exact same header files (first row). Is there an easy script that can combine all her files together into one? For example:
Folder = Monkey
File = Monkey1, Monkey2, Monkey3, etc...
First row is the same for each file and needs to be retained at the top of the collated file.
# of rows with data vary within each file
She wants to add an extra column at the very end that has the file name of the original file, so that column Z would say either Monkey1, Monkey2, or Monkey3, etc...

She has been waiting on someone to help her with this for the better part of a year. But I imagine it is something she could manage on her own, or I could help walk her through, if one of you brilliant people wants to help. It's more endangered monkey conservation work.

Thank you!

[Special-K]

Are the files all Excel files (.xsl/.xlsm)? Generated from the same version of Excel?
Do any of the files contain macros (.xlsm)?
How many worksheets are in each file?
Does each file have the same number of worksheets?
Does each worksheet in a file have the same number of columns? if so, how many?
Does any file contain more than 25 columns (if they do then youre going to overwite column Z data with the filename as youve suggested).

[MermaidNiki]

Are the files all Excel files (.xsl/.xlsm)? Generated from the same version of Excel? yes
Do any of the files contain macros (.xlsm)?yes, all .xlsb
Does each file have the same number of columns? if so, how many? yes, 27
Does any file contain more than 25 columns (if they do then youre going to overwite column Z data with the filename as youve suggested). I just pulled "Z" as an example but was pretty close! Looks like AB would need to be the file ID column.

[Special-K]

Does each file have the same number of worksheets?
Are there any formulas in the workboosk or is it all raw data?

If you're merging the data, what's going to happen to the macros in the .xlsb files?
Do they need to be copied into the merged file? Would they still work in the merged file or need to be changed?

[Special-K]

Ok here's a start...

1. Backup your data first.
2. Create a file called Merge and insert this piece of VBA, save it as Merge.xlsm

Sub GetSheets()
Pth = "D:\!WORK\Install 2\"
j = 2
FlNm = Dir(Pth & "Monkey*.xlsb")
    Do While FlNm <> ""
    Workbooks.Open Filename:=Pth & FlNm, ReadOnly:=True
    lastrow = ActiveWorkbook.Worksheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
    For i = 2 To lastrow
    lastcell = ActiveWorkbook.Worksheets("Sheet1").Cells(i, Columns.Count).End(xlToLeft).Column
    For k = 1 To lastcell
    l = ActiveWorkbook.Worksheets("Sheet1").Cells(i, k)
    Application.Workbooks("Merge.xlsm").Worksheets("Sheet1").Cells(j, k) = l
    Next k
    Application.Workbooks("Merge.xlsm").Worksheets("Sheet1").Cells(j, 28) = FlNm
    j = j + 1
    Next i
    Workbooks(FlNm).Close
    FlNm = Dir()
  Loop
End Sub

Change the pathname (Pth) to where your Monkey folders are.

3) Insert a manual heading in row 1 for the 27 column names. Data will be copied from each workbook into the Merg.xlsm file from row 2 onwards.


This assumes the following:

All the Monkey*.xlsb files will have one worksheet called "Sheet1". If this is not the case then you'll need to change "Sheet1" in ActiveWorkBook to whatever the sheet is called in each Monkey file (This is assuming the same sheet name has been used in ALL Monkey files).

Again, am not sure what's gonna happen to your macros in the original sheets.
Also this purely copies the data so if there are any formulas you may end up with errors, but obviously I dont know what the data looks like or what formulas are being used.

Hope this helps.

[MermaidNiki]

Thank you! Let me have her try this. She says that she does not need the macros anymore, just the raw data.
Fingers crossed! I'll check back in.

[lisamd]

Hi everyone, I'm the person mermaidNiki was trying to help...I've been trying to work with the code you so kindly provided, but I keep getting an error starting at this line:

Application.Workbooks("Merge.xlsm").Worksheets("EF").Cells(j, k) = l
the error says "runtime error 9 script out of range"

I should note that I have two sets of files to merge (separately), one has 23 columns, the other 7. Though if I understood the code correctly, that should not matter since the program is counting?

I am happy to send examples of the files if that helps (I've been just using a set of 4 to try to debug this)

I'm very new to macros so all help is appreciated, thank you!

Lisa