My instrument generates hundreds of data file (xls files). I want to copy part (numeric part) of each file into a new xls file so that each column of the new file contains the numbers of each data file. How can I do that with macro? thanks.
My instrument generates hundreds of data file (xls files). I want to copy part (numeric part) of each file into a new xls file so that each column of the new file contains the numbers of each data file. How can I do that with macro? thanks.
Assuming that there is only one sheet in each file perhaps a macro like this?
You need to change the directory "C:\Temp" and the copy range to suit your needs.Please Login or Register to view this content.
Alf
For an incredibly easy option try http://www.rondebruin.nl/merge.htm
This is a packaged macro which will do it all for you.
Thank you. I tried your code and it works. In each file, I just need one column of data. Can I have each file in one column of the new file? what if I want to do conditional merge? such as, only merge those files with B2=1. Thank you.
Last edited by vlady; 01-07-2013 at 07:40 PM.
Modified the code and assumed you wish to copy column B.
So macro checks value of B2 and if this is 1 column B will be copied to target file. The i value is used as a counter to place next result in the next column.
Just a word of warning, the counter i assumes that the columns in target files are empty. It starts importing to column A, then to B and C and so on untill all the files in folder are processed. If you do a rerun it start again by importing to column A, then B then C ......Please Login or Register to view this content.
Alf
Last edited by Alf; 01-07-2013 at 06:41 PM. Reason: additional info
Alf, thank you. It works very well. You are very helpful. What order are the files emerged based on? based on name, or date?
Moderator's Note: Welcome to the forum Margosa, just a reminder not to quote whole post, it's just clutter. Thank you.
Last edited by vlady; 01-07-2013 at 07:42 PM.
Not really sure but the "default" setting for a folder is showing files in an alphabetical order (name based) so I guess this is how the files emerge. Alternative you could check the order of the files written to the target file.What order are the files emerged based on? based on name, or date?
If this takes care of your problem could you please mark thread "Solved" and if you like to give my answer a rating click on small star bottom left.
Alf
To mark your thread solved do the following:
- Go to the first post
- Click edit
- Click Advance
- Just below the word "Title:" you will see a dropdown with the word No prefix.
- Change to Solve
- Click Save
Last edited by Alf; 01-08-2013 at 02:29 AM.
Hi
Thanks for rep and comments!
Alf
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks