Copy Columns from Workbook to MasterWorkbook

[timdecker]

Scenario:

I have 300+ workbooks. I need to copy columns C:LastActive (populated) column from the workbooks to a master workbook.

Example:

WB A ----> Copy from column C:LastPopulated to Master WB starting with the first blank column.

So essentially, I have my master WB run the macro select the file and it will copy over. Rinse and repeat.

Where do I start?

Thanks.

[mikeTRON]

How many columns and rows are in each? is there only one tab per workbook?

This shouldn't be very difficult to loop through, with basic code, but optimizing this would be to use ADO to grab it all, because it is REALLY fast and doesn't require you to open the workbooks at all.

[mikeTRON]

So I am fairly new with VBA but the following was something I was working on recently for another project, I just modified it to make it work for your needs.

ADO Extract - Batch Process.xlsb

There are two macros here. One lists all files in the folder in range C5. So type the address of your folders there then hit LIST FILES. This will populate the file names to be used for the batch processing. I prefer to keep things separated so when things break I can figure out how and why faster.

So once you have the files listed, Type YES in column F next to any files you want to extract data from. This is typically all of them for me, as I typically create a new folder to drop everything in to make sure this is as easy as possible.

Once you have selected yes for them all, hit Process Everything Flagged as Yes. This will loop through all of the files, pulling the data into the Processing tab, then pasting/appending it to right of the data in the DATA tab, like you wanted.

I created a few sample files to grab sample data from and it processed 100 files in 21 seconds so it should do 300 in about a minute.

NOTE: you MUST enable macros, you must have the Microsoft ActiveX Data Objects 2.9 Library checked as an available reference.
To do so use the following: http://msdn.microsoft.com/en-us/libr.../gg264402.aspx

[timdecker]

So I am fairly new with VBA but the following was something I was working on recently for another project, I just modified it to make it work for your needs.

Attachment 290904

There are two macros here. One lists all files in the folder in range C5. So type the address of your folders there then hit LIST FILES. This will populate the file names to be used for the batch processing. I prefer to keep things separated so when things break I can figure out how and why faster.

So once you have the files listed, Type YES in column F next to any files you want to extract data from. This is typically all of them for me, as I typically create a new folder to drop everything in to make sure this is as easy as possible.

Once you have selected yes for them all, hit Process Everything Flagged as Yes. This will loop through all of the files, pulling the data into the Processing tab, then pasting/appending it to right of the data in the DATA tab, like you wanted.

I created a few sample files to grab sample data from and it processed 100 files in 21 seconds so it should do 300 in about a minute.

NOTE: you MUST enable macros, you must have the Microsoft ActiveX Data Objects 2.9 Library checked as an available reference.
To do so use the following: http://msdn.microsoft.com/en-us/libr.../gg264402.aspx

Thank you so much for your help, I believe this is just what I need. However, I do not have "Microsoft ActiveX Data Objects 2.9 Library". Do you (or anyone else) know where I can download this version?

[timdecker]

If I run it as is without "Microsoft ActiveX Data Objects 2.9 Library" I get the following error:

Noname.jpg

Is this because I am missing Microsoft ActiveX Data Objects 2.9 Library, or something else?

[timdecker]

UPDATE: I am having errors because I am trying to process pre-2010 files (XLS). This code works great if you it is an XLSX file. Do you have any tips to for this to work in Copy/Paste of XLS files (Multiple) to XLSX (Master WORKBOOK)?

Thanks

[mikeTRON]

Did you activate the reference library as stated above?

[mikeTRON]

Yeah, I suppose that would have been good to know, I dont think the ADO works with 97-03 files.

[xlbiznes]

try this solution.

[timdecker]

try this solution.

Thank you so much. It worked fairly well; however, it only processed the first file and stopped after that. Any idea on how to get it to process the rest of the files?

Thanks again!

PS. I left the condition blank. In fact... I am a little confused as to what the condition is used for?

[xlbiznes]

can you post a screen shot of the my settings tab.

[timdecker]

can you post a screen shot of the my settings tab.

Noname.jpg

For some reason it keeps bringing in Col A and only processing the first file....

[xlbiznes]

i guess this was the last updated file can you download the file from the below link:


http://www.excelforum.com/excel-prog...ml#post3533686

[timdecker]

i guess this was the last updated file can you download the file from the below link:


http://www.excelforum.com/excel-prog...ml#post3533686

That ended up working out pretty well. Thank you for that.

Is there any way to make it so it starts the copy at Col B and each new file is pasted across horizontally instead of vertically down the worksheet?

Also, I need it to paste values. It pasted the values for the first workbook but not the second.

EDIT: Got it to paste values and format, now I just need it to paste horizontally instead of vertically into the worksheet.

[xlbiznes]

try replacing this line of code to transpose the data

from :

Please Login or Register  to view this content.

to:

Please Login or Register  to view this content.

to copy the range that you have mentioned

change this line of code from :

Please Login or Register  to view this content.

to:

Please Login or Register  to view this content.

[timdecker]

try replacing this line of code to transpose the data

from :

Please Login or Register  to view this content.

to:

Please Login or Register  to view this content.

to copy the range that you have mentioned

change this line of code from :

Please Login or Register  to view this content.

to:

Please Login or Register  to view this content.

That still didn't work.

Here is what my files look like, but much much much longer.

Noname.jpg

[xlbiznes]

can you post 2 of your data file (sample with dummy values if information is sensitive).

also post a third file with the output required.

[timdecker]

can you post 2 of your data file (sample with dummy values if information is sensitive).

also post a third file with the output required.

Ok here are 2 sample files and what I would like the final file to look like:

Collection.zip

Couldn't add atachment via this forum so I used a 3rd party, please let me know if you prefer a different host.

THANK YOU SO MUCH!!!!

[xlbiznes]

try this file and let me know if it works. tested it on the 2 files and it works as desired.

[timdecker]

try this file and let me know if it works. tested it on the 2 files and it works as desired.

Wow. THANK YOU SO MUCH! That works just how I wanted it. You have done more than enough and have truly gone out of your way to help me.

But... =) When you were looking at the files, you saw columns that contained nothing but 0. Any idea on how best to clean that up?

You don't have to help any more if you don't want as you have truly exceeded my expectations.

THANK YOU!

[xlbiznes]

What we can try to do is the delete all the columns that are starting with 0 on row 5 . Will this logic hold good ?

I am not gonna sleep till i get this thread solved.

[timdecker]

What we can try to do is the delete all the columns that are starting with 0 on row 5 . Will this logic hold good ?

I am not gonna sleep till i get this thread solved.

That sounds good to me. Logic is good to go.

[xlbiznes]

done, let me know if its fine.

Out of curiosity , i would like to know how many files would be merged in a real life scenario ?

[timdecker]

done, let me know if its fine.

Out of curiosity , i would like to know how many files would be merged in a real life scenario ?

That was perfect! Thank you, again, so very much.

On my site there are about 350+ files. With all the other sites combined it is something like 1200+.

If we ran out of room I would just create a 2nd master file. I have to add some more code to it to get it across to our oracle system, but that is a cake walk compared to what you brought to the table.

[xlbiznes]

you are welcome, had fun getting this done.

All the best.