Macro on multiple files and save in other fileformat

**soreno** · 03-09-2010, 02:35 PM

Hello!
I have this macro that I want to use on all files in a folder (*.csv). It can be up 100 files at the most and it is hard work doing manually. Is there a way to put this macro to be able to run on all files in a folder and at the best be saved as an excelfile (as I want to keep the formatting).

Original files are *csv files...

Please Login or Register  to view this content.

**JBeaucaire** · 03-09-2010, 03:31 PM

This macro will open all the CSV files in one folder, then run your macro, save, then move on.

Please Login or Register  to view this content.

If you want to save the files into a different directory, we can add that.

==========
Looking through your code posted above, here's a version I think does the same thing without all the "selecting"...should process faster.

Please Login or Register  to view this content.

If not, you can always just keep your original.

If you post up a sample CSV, I might be able to clean the formatting code a little more accurately, up to you.

**soreno** · 03-09-2010, 05:54 PM

Jerry,

Wow! more than I expected... I did change the macro so it is updated below. The files I tested has one problem, in the filename it has "xxx [ text ].csv" and that seems to get into trouble when it tries to save the file as excel. I dont know how to get around this, but that can be removed "[ tex ]" when saved if possible. I attached a zipfile with an small example file (files can be up to 1000 rows) I prefer to have the macro open the files in excisiting folder then to have "hardcoded" the path to the folders as that varies alot...
Thanks alot

Please Login or Register  to view this content.

**JBeaucaire** · 03-09-2010, 07:58 PM

I'll look at it now.

1) Do you want the Excel file saved to another folder? I can add that capability

2) Do you want anything done with the imported CSV files? When I'm "converting" like this, I typically:

a - delete the original file once the conversion has confirmed as successful
b- move the files into an "imported" folder inside the original folder so I know they're done, but still available

**JBeaucaire** · 03-09-2010, 08:13 PM

I haven't done anything to your new macro. If want to, you can compare my trimmed version above in post #2 to see how the macro recorder creates code far bulkier and inefficient to what can be done. Compare the sections and you'll see what I mean.

Meanwhile, try this:

Please Login or Register  to view this content.

**soreno** · 03-10-2010, 02:46 AM

Jerry!

Beautiful code, works as a charm thanks mucho.

With your suggestions it would be great to put the old imported csv files into another folder in case you want to have another look...

Question

Would "D:/Data/csvfiles/%users%/" works as a path to the folder with the files for processing?

I will look at my macro to see if I am able to "clean" it up....

**JBeaucaire** · 03-10-2010, 04:19 AM

This version will move the CSV files into an IMPORTED folder in the same directory.

Please Login or Register  to view this content.

I'll leave it to you to experiment with the paths and find what works for you and what doesn't.
========
If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.

(Also, use the blue "scales" icon in our posts to leave Reputation Feedback, it is appreciated. It is found across from the "time" in each of our posts.)

**john555** · 03-10-2010, 04:30 AM

I have an excel file there quantity I added with date like 09/05/2009=05 and 10/05/2009=05 as on. Each date and quantity added on each columns. I want to sum the quantity.

exaple 5/12=2 6/12=30 7/12=5 8/12=30 9/12=30 16/12=30

Is there any way to sum this quantity without date

I attached a sample file. pls give me a solution. My Email address [email protected]

**teylyn** · 03-10-2010, 04:59 AM

John555, Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.