I have several files which I export as CSV files.
I would like to have a macro to open all CSV files where the name contains has the word JNL in the file name for eg BR1 JNL june 2013 in folder C:\Journal Templates and to format the values in Col B & C to 2 decimal places (not using thousand seperator)
Where Col B & C both how zero values, then Row in CSV file to be deleted
Your assistance in this regard is most appreciated
attach please a source sample file and the final
Hi Patel
See attached sample file
That is not a good idea. The files would then be Excel files and not CSV files.
It would be better to format as needed in some other Master file.
Hi Kenneth
Thanks for the information. Will format in the master file.
Regards
Howard
I haven't come across a situation where opening a CSV file and saving it changes the format to non-CSV :)
When the CSV file is saved with NumberFormat, the values are not saved to the CSV file. The Text property is saved. This means that you get what you see, for the most part. Formatting a cell in Excel does not change the Value property. Sometimes, that may be what you want I guess.
If that is what you want, we can do that.
e.g.
A2 in one file is 1.555. Formatted to two places is 1.56. Saved as a CSV file and reopened, the value of A2 is now 1.56, not 1.555.
Of course deleting the rows with 0 values in both columns is easily done.
We probably could be saying the same thing, but I am not sure. If you look at the attachment, I've formatted the numbers to 2 decimal places, however, the values still remain as it were.Quote:
Saved as a CSV file and reopened, the value of A2 is now 1.56, not 1.555.
I only saw 1 or 2 decimal places in the 2nd column. That file and a new one was doing something that my other tests yesterday did not. After I set the formats, saved, closed, and opened, it rounded as I explained yesterday AND removed the NumberFormats. I would have expected that yesterday but it did not happen with your file. Tested on xl2010 xppro.
Howard, please test what was posted and play with it. If you can live with how it works, we can do what you originally asked.