Combine xls Files

mgpco

New Member
Joined
May 17, 2003
Messages
4
Hi there ,

My question is do u know any sort of program that can combine 100 xls Files (all has only one sheet ) to only one huge file in just one sheet !

and yes all the sheets having same no of columns and rows of data !!


Regards
H.B :)
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
mgpco said:
Hi there ,

My question is do u know any sort of program that can combine 100 xls Files (all has only one sheet ) to only one huge file in just one sheet !

and yes all the sheets having same no of columns and rows of data !!


Regards
H.B :)

You could have used your earlier posting itself.

The following macro will do the job of copying all your data from indvidual workbook and paste into a single sheet.

No error trap is provided.

Copy your all individual workbooks into a single directory "C:\Temp\"
Open a new workbook. Right click the leftmost excel ison and click viewcode then paste this code and run.

Keep backup of your orignal data.
<Pre>
Sub MergeSheets()
Dim SrcBook As Workbook
Dim fso As Object, f As Object, ff As Object, f1 As Object

Application.ScreenUpdating = False
Set fso = CreateObject("Scripting.FileSystemObject")
Set f = fso.Getfolder("C:\Temp\")
Set ff = f.Files

For Each f1 In ff
Set SrcBook = Workbooks.Open(f1)
Range("A1:IV" & Range("A65536").End(xlUp).Row).Copy
ThisWorkbook.Worksheets(1).Activate
Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial
Application.CutCopyMode = False
SrcBook.Close
Next
End Sub</Pre>

GNaga
 
Upvote 0
mgpco said:
It really works Thank U !
How U got it ? where didi u learn ?

Thanks. I learnt many things from this board itself.

GNaga
 
Upvote 0
Gnaga,
Wow, you are excellent :wink:
Can you help to explain what the meaning of the code each line.
Thanks
 
Upvote 0
Hello Naga,

I have a problem stated below. How can we fix this.


I have a Excel Sheet(Say OG.xls) which has some data already in it with some 5000 rows with headings in the first row and Upto "AN" Columns.
This No of rows(5000) doesnt change for a whole year.
Now i have 5 XL files(Say A,B,C,D,E) and the data from these files has to be appended to this OG file just starting from 5001st row every time.
All these 5 files has different no of coloumns but identical to that of OG File.
I have to pull data from these files and place them in OG File.
From File A : Column A,B,C,D,E,F,G&H goes to Coloumn F,G,T,U,V,W,X&Y respectivley Of OG.xls file likewise for the other files too.
The second file data has to be appended right below the next row where the File A ends.(Say after filling the data from File A now the OG.xls has 5110 rows,then File B data has to be filled from 5111 st row of OG.xls. The Columns of File B has to match up with that of OG.xls)
The same follows for the other files too.
Each time the same operation is repeated by filling the data from 5001st row of OG.xls

Please help me in this!!!
Please let me know for any clarifications.

Thanks in advance!!!
 
Last edited:
Upvote 0
thank you for your post, but all of my users are working with windows...
i'll try to find the windows versions if there is any

Cheers...
 
Upvote 0
Hi there. This stacks the information. What would it take to place all of the headers in a row and run the information horizontally. Each of the headers is a different title. Thanks. Padre310 here.

You could have used your earlier posting itself.

The following macro will do the job of copying all your data from indvidual workbook and paste into a single sheet.

No error trap is provided.

Copy your all individual workbooks into a single directory "C:\Temp\"
Open a new workbook. Right click the leftmost excel ison and click viewcode then paste this code and run.

Keep backup of your orignal data.



Sub MergeSheets()
Dim SrcBook As Workbook
Dim fso As Object, f As Object, ff As Object, f1 As Object

Application.ScreenUpdating = False
Set fso = CreateObject("Scripting.FileSystemObject")
Set f = fso.Getfolder("C:\Temp\")
Set ff = f.Files

For Each f1 In ff
Set SrcBook = Workbooks.Open(f1)
Range("A1:IV" & Range("A65536").End(xlUp).Row).Copy
ThisWorkbook.Worksheets(1).Activate
Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial
Application.CutCopyMode = False
SrcBook.Close
Next
End Sub
</PRE>

GNaga
 
Upvote 0
You can also try a program called bulk file merger which does not require any macro programming. I used this program to merge over 300 xls files. The program also merges csv files. Neat little app.
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,915
Members
448,532
Latest member
9Kimo3

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
Back
Top