Joining spreadsheets from different directories into one

[area52]

Dear Sirs,

I'm trying to join several spreadsheets.

They have the same name (ex: "0001.xls, "0002.xls"...) but each one have different sheet names (ex: "Only_A"; "Only_B"; "A&B") and saved at different directories (Ex: C\Reports\Only_A ; C\Reports\Only_B; C:\Reports\A&B).

Is there any VBA code that researches for one by one in their subfolders and join them in a unique spreadshet with all its sheets ("Only_A"+"Only_B"+"A&B").

Any help will be very appreciated!!!

Thanks a lot!!!

 

[Rijnsent]

Like this?

Hi area52,

to check that I understand you correctly:
you'd like to create new files (say in a new directory C:\Reports\Totals\) with the names of the original files (0001.xls, etc.), with 3 sheets in them, taken from the 3 seperate files?
Are the file names in a certain logical way (say all numbers from 0001 to 0132)?

If so, it's very possible , just post these details and I'll try to help you moving on,

Koen

 

[area52]

Dear Rijnsent,

Exactly!

These files are arranged like this:

C:\report\Only_A\0001.xls - sheet "Only_A"
C:\report\Only_B\0001.xls - sheet "Only_B"
C:\report\A&B\0001.xls - sheet "A&B"...

and so on for 0002.xls, 0003.xls....

I just want to join all the 3 "0001.xls" workbooks in a just one with the 3 sheets at "C:\report\Consolid\0001.xls - sheets "Only_A","Only_B" and "A&B"

and so on for 0002.xls, 0003.xls...

Any sugestion will be welcome!!

Thanks in advance!

 

[Rijnsent]

Step 1

Hi there,

sorry for not replying for some time, end of the year is very busy . I grabbed some code together, this should get you started. I hope you can work the rest out, if not, just post and I'll try to help you out further.

Greetz,

Koen

Sub unify()

Dim basedir As String
Dim FileName As String
Dim n As Integer
Dim NrOfFiles As Integer

NrOfFiles = 20
basedir = "C:\temp\"

For n = 1 To NrOfFiles
    FileName = n
    If Len(FileName) = 1 Then
        FileName = "000" & FileName
    ElseIf Len(FileName) = 2 Then
        FileName = "00" & FileName
    ElseIf Len(FileName) = 3 Then
        FileName = "0" & FileName
    Else
    End If
    
    ChDir basedir & "Totals\"

    Set NewBook = Workbooks.Add
    With NewBook
        .SaveAs FileName:="allsales" & FileName & ".xls"
    End With
    
    ChDir basedir & "Only_A\"
    Workbooks.Open FileName:=basedir & "Only_A\" & FileName & ".xls"

'Now copy the worksheet and paste it in the allsales file, and do that as well for B and A&B.
    
Next n

End Sub

 

[area52]

Thank You!!!

Rijnsent,

No prob! I´m also stuck at another crazy 'excel challenges', typical during the year end...

I haven´t tested your code yet but, as soon as I do it, I´ll feedback you.

Thank you very much for your reply and help!!!

Marcus