Command to combine many Excel files into one

There is no standard Excel funcionality that accomplishes this. There are Excel VBA / macro options that are available, however. Are you interested into breaking into code to solve this problem, or is that not something that interests you?

Hi BullmanTech,

Yes, a VBA/macro option seems the way I would like it then.

I assume I would paste the VBA / Macro into Excel's VB editor....

My files are all Excel 2007 with xlsx extensions.

Thanks!

Thanks BullmanTech!  I will be trying this out later today.

Rowby

Hi

On Excel Menu > Insert I can't find "Module".   Can I use Alt F10 and paste it into the VB Editor?  Or must this be done only via the Insert Menu.



See attached. screen capture.
insert-module.jpg

Apologies - I was talking about the Insert menu within the VB Editor. Find the VB Editor in Excel 2007 by:

1) Using the Developer tab > Visual Basic (Developer tab made visible by Office Button > Excel Options > Popular > Show Developer tab in the Ribbon).
2) Pressing Alt + F11

Got it.  Will try when I get to a computer.
what will the name of the new "Master" folder be.  And will I find it in the same folder as the other files?

There will not be a master 'folder,' but there will be a master file, one file containing all of the worksheets from each of the workbooks within the folder (full folder path listed in Cell A3).

Hi

Ran the command and saw a bunch of activity.  Ran smoothly.  Ended with "Process completed successfully".


But cannot find a "master" file in the folder.  All of the files are  the same size (KB) as before.

Is there a new excel filename I should be looking for?  I assume it should be in the same folder as the other files?

FYI here is the folder info I put in Cell A3

C:\convert\9-13 files to combine and print\

BTW I have the macro file in a separate folder:  C:\convert\9-13 files to combine and print\special folderfor combining macro



Thanks!

Rowby

AH, I see now.  They were all added as separate tabs in the macro workbook.

I thought it would be appended into one long spreadsheet.

But this might work for what I am doing.  

However, just in case,  is there a way to make all of the spreadsheets one new long spreadsheet?

Rowby

Let me know if this definitely doesn't work for you and we can go down that path only we have to.

Stay tuned.  Am trying it in a few min!  :)

Well, I can't seem to get Microsoft Publisher to import individual tabs.  It's only accepting one tab at a time.  I guess I could live with it.

However if there is a way to combine  it so where one excel file ends, the next one is appended to the bottom of that one.   Making one long file.

Again if it's more trouble that it's worth I suppose I could import one table / tab at a time.

Rowby

Hi

For my current situation your solution will be okay.

However for the future,  is there a way to  to combine so where one excel file ends, the next one is appended to the bottom of that one.   Making one long file.

Hi  

Update.

As I attempt to merge the files into Microsoft publisher I am running into problems with having to do  the individual tabs.  

So even if it requires me to take extra steps, I will need a way to combine all of the excel sheets into one long 'spreadsheet" or similar datasourc.e


For example I have Access, if that's a way to combine the excel sheets and then export them, for example, to a csv file for import as one big file into Publisher.

Rowby

I won't be by a computer until Monday, so I won't be able to get you any code to accomplish that, but I am quite confident there is a suitable solution somewhere on Experts Exchange, so if you search for "combine multiple Excel worksheets," you should be able to find what you need.

Otherwise, I can help next week.

Hi BullmanTech...  I've been googling it this morning and have found some solutions.

Stay tuned I'll share the results when you get back.

And will award you the points for helping me troubleshoot this!

Rowby

Did you find a solution?

Yes.

This one worked for me.  See if you have any modifications for the future, but otherwise it works fine.

BTW this works after I have used your code to combine them all into one workboook.

Rowby

Sub ConsolidateSheets()
Dim TargetSh As Worksheet
Dim DestCell As Range
Dim LastRow As Long
Dim sh As Worksheet

With Application
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
End With

On Error Resume Next
Set TargetSh = Worksheets("Consolidated")
On Error GoTo 0
If TargetSh Is Nothing Then
    Set TargetSh = Worksheets.Add(before:=Sheets(1))
    TargetSh.Name = "Consolidated"
Else
    TargetSh.Cells.Clear
End If
Set DestCell = TargetSh.Range("A1")
Sheets(2).Range("A1:AZ1").Copy DestCell 'copy header
Set DestCell = DestCell.Offset(1)

For Each sh In ThisWorkbook.Sheets
    If sh.Name <> "Consolidated" Then
        LastRow = sh.Cells.SpecialCells(xlCellTypeLastCell).Row
        sh.Range("A2", sh.Range("AZ1" & LastRow)).Copy Destination:=DestCell
        Set DestCell = DestCell.Offset(LastRow - 1)
    End If
Next

With Application
    .ScreenUpdating = True
    .Calculation = xlCalculationAutomatic
End With
End Sub

Select allOpen in new window

Glad to hear it worked!

Thanks  BullmanTech!