Joining spreadsheets from different directories into one

area52

New Member
Joined
Nov 23, 2005
Messages
3
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!!!
 

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

Rijnsent

Well-known Member
Joined
Oct 17, 2005
Messages
1,298
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Nov 23, 2005
Messages
3
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

Well-known Member
Joined
Oct 17, 2005
Messages
1,298
Office Version
  1. 365
Platform
  1. Windows
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


Code:
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

New Member
Joined
Nov 23, 2005
Messages
3
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
 

Watch MrExcel Video

Forum statistics

Threads
1,118,811
Messages
5,574,452
Members
412,595
Latest member
slim313
Top