# Concatenate Excel files



## Squashman (Apr 4, 2003)

Is there anyway to concatenate a bunch of excels file together. I have a bunch of excel files with the same type of data in each column and I would like all of the data to be in one excel file. I could certainly cut and paste if it was just a few files, but is is around 50 files and some of the files have over 10,000 rows. Would be real easy if they were CSV files or text files but not sure how to do it with excel files or if there is even a way to do it. I suppose I could try and export every one of the files as a CSV file and then concatenate them together but that would still be alot of work.


----------



## Ziggy1 (Jun 18, 2002)

Well if some of the files have 10 000 rows, and you have 50 excel files, then it is a good bet that you will exceed Excel's 65 536 row limit.

With that in mind, you should consider importing the data into a Access Database table.


----------



## Jimmy the Hand (Jul 28, 2006)

Here's a workbook with a macro that might suit your needs. 
Before using it, put all the needed excel files in one folder.
Click on start, browse to the folder containing the excel files, and *select them all* before clicking on Open. Edit: Ctrl and Shift works, just like in any Explorer window.

The macro will process the files in a loop.
It takes always the first sheet of the currently processed file and copies the CurrentRegion (starting with cell "A1") into a sheet created for this purpose. If the rows to copy would exceed the limit (65536 rows), a new sheet is opened.


----------



## OBP (Mar 8, 2005)

If as Ziggy suggested Jimmy's Fix creates a worksheet with too many rows then I have a similar setup to import the Excel sheets in to an Access database, Unlike Jimm's it currently only imports them one at a time, which is not too much work when all you have to do is browse to the workbook and click a button.


----------



## bomb #21 (Jul 1, 2005)

:up: OBP, you should market that. 

Jimmy, Ron de Bruin has much stuff to do with "copy range from closed workbooks" (something to do with "ActiveX Data Objects" --  ) ; might interest you since you seem hungry for knowledge.  Find it at http://www.rondebruin.nl/ado.htm


----------



## Jimmy the Hand (Jul 28, 2006)

Bomb, 

Thx for the link. It's certainly interesting, but I must admit it's a bit beyond me  I might make the effort one day and try to grasp the essence. Until then.. I will have to open everything... Poor me


----------



## Squashman (Apr 4, 2003)

Jimmy the Hand said:


> Here's a workbook with a macro that might suit your needs.
> Before using it, put all the needed excel files in one folder.
> Click on start, browse to the folder containing the excel files, and *select them all* before clicking on Open. Edit: Ctrl and Shift works, just like in any Explorer window.
> 
> ...


That is awesome. Just what I need. Doesn't matter that I will have to make them into 3 files because some of them are so large.

One request though. First row has the column headings. Anyway to remove that with your macro.


----------



## Anne Troy (Feb 14, 1999)

See if this helps:
http://vbaexpress.com/kb/getarticle.php?kb_id=773


----------



## Jimmy the Hand (Jul 28, 2006)

Squashman said:


> First row has the column headings. Anyway to remove that with your macro.


Sure, no problem. Just one more line in the proper place. The new line in question is
*Sht.Rows(DestRow).Delete shift:=xlUp*
and it resides at the end of *ProcessFile* subroutine. See the updated attachment.

Please note that this line actually deletes the _first row of the data copied from each and any processed sheets_. It doesn't check if it's really a column headings or something else. Also, in the end, there will be no column heading rows at all in the newly created and filled sheets, so you will have to provide one, manually. (Which could, of course, be handled by macro, but if this is a one-time task, it's not more effort to copy/paste a row than write a code to do it.)

BTW, the link Anne has provided looks promising. Certainly, it's more thorough and scientific work than mine.
Also, converting these sheets into an Access database makes sense.


----------



## Squashman (Apr 4, 2003)

No need to put them into an access database. One I have them merged into a few files I run them thru another utility that makes them all into a standard File Layout and moves them to our mainframe.


----------



## Squashman (Apr 4, 2003)

Anne Troy said:


> See if this helps:
> http://vbaexpress.com/kb/getarticle.php?kb_id=773


So if I use this, it will wrap to the next sheet once it has hit the row limit?


----------



## Zack Barresse (Jul 25, 2004)

Yes, that is correct. About halfway down the routine you see this line...


```
If RowCount + uRange.Rows.Count > 65536 Then 'if the used range wont fit on the sheet
```
This is great, but I would change the hard-coded 65536 into...


```
tWS.Rows.Count
```
This will work for all versions of Excel.

HTH


----------



## Squashman (Apr 4, 2003)

Jimmy the Hand said:


> Sure, no problem. Just one more line in the proper place. The new line in question is
> *Sht.Rows(DestRow).Delete shift:=xlUp*
> and it resides at the end of *ProcessFile* subroutine. See the updated attachment.
> 
> ...


Used this for the second time again and just wanted to say Thank You again. It saves me a ton of time.


----------



## TerryNet (Mar 23, 2005)

Reopened per request.


----------



## Squashman (Apr 4, 2003)

Hi Jimmy,

If I change this one line of code will this then work on Office 2007 to get me the additional rows.


```
If DestRow + RowCount > 65536 Then
```


----------



## Jimmy the Hand (Jul 28, 2006)

Yes. Change it to 

```
If DestRow + RowCount > Sht.Rows.Count Then
```
and it will be version independent. I guess. As I have no xl2007, can't test it. But I'm reasonably sure.

Jimmy


----------



## Squashman (Apr 4, 2003)

I didn't try your fix yet. I just changed the number to 1000000.

I ran it and it bombed I think about on the 2nd file. Each file is about 50,000 rows.

When I clicked on debug it highlighted this line.

```
Workbooks(NewWorkbook).Sheets(1).Range("A1").CurrentRegion.Copy Destination:=Sht.Cells(DestRow, 1)
```
Will let you know that these excel files have Chinese characters. Don't know if that makes a difference or not.

But the error seemed kind of odd. Here is a screen shot of the error.


----------



## Squashman (Apr 4, 2003)

Ok. We are making progress. I made your change and it doesn't error out but it is still creating multiple sheets. Office 2007 can handle a million rows so I need this all onto one sheet. Looks like it is creating a sheet for each file it is appending. Each file is 50,000 so each sheet has 50,000 rows.


----------



## Ziggy1 (Jun 18, 2002)

Can you actually manually add more than 65536 rows? I had a problem with that before so make sure you can actually paste more


----------



## Jimmy the Hand (Jul 28, 2006)

I rewrote the code, and in the meantime added some experience of the last few years.
Not sure what the problem was, but I know that using Activesheet and ActiveWorkbook is not a good practice, so I eliminated them. Also changed the algorithm that selects the range to be copied. From A1 to the last used cell in column A, entire rows are copied.
Hope it works. It _should _work.

```
Sub Append()
    Dim i As Long, FileNameArray
    Dim wbSource As Workbook, wsSource As Worksheet, wsDest As Worksheet
    Dim rngSource As Range, rngDest As Range
    
    FileNameArray = GetFileNames
    If Not IsArray(FileNameArray) Then Exit Sub
    Set wsDest = NewSheet
    For i = 1 To UBound(FileNameArray)
        Set wbSource = Workbooks.Open(Filename:=FileNameArray(i))
        Set wsSource = wbSource.Worksheets(1)
        Set rngSource = Range(wsSource.Range("A1"), wsSource.Range("A" & wsSource.Rows.Count).End(xlUp))
        Set rngDest = wsDest.Range("A" & wsDest.Rows.Count).End(xlUp).Offset(1)
        If rngDest.Row + rngSource.Rows.Count > wsDest.Rows.Count Then
            MsgBox ("This sheet is full. New sheet will be added.")
            Set wsDest = NewSheet
            Set rngDest = wsDest.Range("A1")
        End If
        rngSource.Copy Destination:=rngDest
        wbSource.Close SaveChanges:=False
    Next
End Sub

Function NewSheet() As Worksheet
    Set NewSheet = ThisWorkbook.Sheets.Add
End Function
Function GetFileNames()
    GetFileNames = Application.GetOpenFilename(, , , , True)
End Function
```
Jimmy


----------



## Squashman (Apr 4, 2003)

So I went in and edited the macro. Removed all your old code and pasted in your new code.

Now when click the Start button it gives me this error.


----------



## Squashman (Apr 4, 2003)

I figured out that I had to reassign the start button to the new macro. Newbie mistake.

I was able to select the files. It then told me the sheet was full and needed to start another sheet. Then it gave me an error. The debug highlighted this line.
Set rngDest = Sht.Range("A1")


----------



## Squashman (Apr 4, 2003)

Ziggy1 said:


> Can you actually manually add more than 65536 rows? I had a problem with that before so make sure you can actually paste more


Can't seem to do that either. Attaching a screen shot of that error. Not sure why it says that. All these file have the same exact amount of columns and are all in the same order.


----------



## Squashman (Apr 4, 2003)

Think I am making progress.
I saved the new macro as an XLSM (Office 2007 macro enabled workbook). I then ran it. It pulled in all my files but only the first column of each file. I have a dozen columns in each file.

The first row was blank. Then it output a header from column A twice and then I got some data.


Blank
Catalog_ID
Catalog_ID
L0017668
L0017669
L0017670
etc......
Then the very last row is again the header from the first column.
Catalog_ID


----------



## Jimmy the Hand (Jul 28, 2006)

My mistake, sorry. The buggy line is a leftover of the old version.
Replace

```
Set rngDest = Sht.Range("A1")
```
by

```
Set rngDest = wsDest.Range("A1")
```
I've edited post #20. Changed the above line, and also replaced each occurrence of WB with wbSource.

BTW, can you copy one single row from a xl2003 worksheet to a xl2007 worksheet?


----------



## Squashman (Apr 4, 2003)

Thanks for getting me started Jimmy. Here is the final code we used. We changed it up a little bit. The first issue we had was it was only pulling in Column A only. Then we noticed that if Column A was blank it would not pull in the record at all.

```
Sub Append()
    Dim i As Long, FileNameArray
    Dim wbSource As Workbook, wsSource As Worksheet, wsDest As Worksheet
    Dim rngSource As Range, rngDest As Range
    Dim iDestRecordCt As Long
    
    FileNameArray = GetFileNames
    If Not IsArray(FileNameArray) Then Exit Sub
    Set wsDest = NewSheet
    For i = 1 To UBound(FileNameArray)
        Set wbSource = Workbooks.Open(Filename:=FileNameArray(i))
        Set wsSource = wbSource.Worksheets(1)
        Set rngSource = Range(wsSource.Range("A2"), wsSource.Range("M" & wsSource.Rows.Count).End(xlUp))
        iDestRecordCt = wsDest.UsedRange.Row - 1 + wsDest.UsedRange.Rows.Count
        If wsDest.Range("A1") <> "" Or wsDest.Range("B1") <> "" Then
          Set rngDest = wsDest.Range("A" & iDestRecordCt + 1)
        Else
          Set rngDest = wsDest.Range("A" & iDestRecordCt + 1).End(xlUp)
        End If
        If rngDest.Row + rngSource.Rows.Count > wsDest.Rows.Count Then
            MsgBox ("This sheet is full. New sheet will be added.")
            Set wsDest = NewSheet
            Set rngDest = wsDest.Range("A1")
        End If
        rngSource.Copy Destination:=rngDest
        wbSource.Close SaveChanges:=False
    Next
    MsgBox ("Done. All files loaded.")
End Sub

Function NewSheet() As Worksheet
    Set NewSheet = ThisWorkbook.Sheets.Add
End Function
Function GetFileNames()
    GetFileNames = Application.GetOpenFilename(, , , , True)
End Function
```


----------



## Zack Barresse (Jul 25, 2004)

Might I make a little suggestion? Right before this line...

```
Set wbSource = Workbooks.Open(Filename:=FileNameArray(i))
```
...I would do a test to see if the workbook is open or not. If it is, and you then open it programmatically, it can be a pain and will not always work well in your favor.

Also, I would change your NewSheet function to pass the workbook in question rather than use ThisWorkbook. Now you don't actually set your ThisWorkbook anywhere in your code (as a workbook object variable), but if you did this may be beneficial...

```
Function NewSheet(wkbTarget As Workbook) As Worksheet
    Set NewSheet = wkbTarget.Sheets.Add
End Function
```
...which would alter the line when called...

```
Set wsDest = NewSheet(YOUR WORKBOOK)
```
Food for though.


----------



## Squashman (Apr 4, 2003)

Well for me that is like an 8 course meal. Which will probably take me the next 8 months to digest.


----------



## draceplace (Jun 8, 2001)

I read this post last week and you guys seemed to be headed in the right direction (best I could tell). I had a different solution you might want to consider.
1. vbs to save all the excel files in a directory as .csv
2. vbs to read new cvs files and write them into one csv skipping the header after input count is greater than 1.

This is a little more straight forward and easier to maintain. If you just importing the data to a mainframe the 'excel' format isn't buying you anything...

Let me know if you interest in the vbs.


----------



## Squashman (Apr 4, 2003)

This one is not going on the mainframe. After I have them all merged I exported them as a Unicode file because they have chinese characters. I just needed them merged first so that i could do a little manipulation before I exported it as a unicode file.


----------



## teachmehow2macro (Oct 30, 2010)

Hi guys,

Pardon me if I am hijacking(and if its unacceptable, please move me to the proper thread, Thanks!) but the reason I am asking here is because append.xls Jimmy added would help me greatly on a daily basis as well with a few minor changes. 

How and what changes do I need to make to the code so when it opens the workbooks it unhides any hidden column and rows and copies only used range between A2:AE65536 off of every workbook and pastes special, values only, back onto the main workbook

Thanks in advance guys.


----------

