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

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

Rijnsent

Well-known Member
Joined
Oct 17, 2005
Messages
1,299
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,299
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,122,263
Messages
5,595,166
Members
413,973
Latest member
leon1974mk

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top