Merge or Copy multiple Workbooks into one

[denko]

Is it possible to read multiple (I’ve got more then 100) Excel files and than copy them into one?
To be more clear I have Excel files with some sensible data.
I'm not sure if I need to open one of them copy the content into Main file close first file, open second one copy into main and so on. May be there is a more efficient way of doing it?
Any thoughts.

Thanks.

 

[FoxProProgrammer]

Do you want to do this in Access? I'd recommend writing a VB program in Excel that opens your workbooks and copies the data to a master. You can use the following code to copy the worksheets to a master.

Worksheets("Sourcesheet&quot.Copy after : = Worksheets("Master&quot

You can also copy the data onto the same worksheet if you want, but it would take different syntax. The syntax above creates a new worksheet for each source sheet and places them in the same workbook (Excel file)

dz

 

[denko]

Yes I need to copy all of those workbooks that each cosists of one speadsheet into one speadsheet of the Master (Main) workbook.
If you have code for that it would be very helpful.
Also because of 120 workbooks memory is my biggest concern(Excel is freezing the whole mashine)
Thanks a lot

 

[FoxProProgrammer]

Are the field names in each of the 120 workbooks the same? It seems like they would all have to be the same to merge them into a single worksheet in the master workbook. If the field names aren't the same, it seems like you would have to store the source workbooks into their own worksheet in the new workbook. I'll send you some code after I make sure we're on the same page. Geez, the semantics are giving me a headache...workbook, worksheet, page. <g> The former is a little more complicated because you have to copy a range and start the next paste at the end of the last paste. If you were just going to copy the workbooks to their own worksheet in the master, you wouldn't have to keep track of the range because you would just be copying the entire range of one workbook to it's own sheet.

 

[denko]

Yes field names are the same in all workbooks. I envision a procedure that would loop through one directory open one file at the time copy data from one sheet(All my workbooks have only one worksheet) paste it into the main workbook. Close temp 1, Open temp2 book copy it and so on ......
May be there is more efficient way to do it but that seems to be an idea that I could work on.
If you can help with code to loop through Directory, Open file and than closing it, I'd very appreciated, since this is a very rush project.
Thanks.