Opening Files That Contain Specific Name And Format Certain Columns

[Howardc]

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

[patel]

attach please a source sample file and the final

[Howardc]

Hi Patel

See attached sample file

[Kenneth Hobson]

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.

[Howardc]

Hi Kenneth

Thanks for the information. Will format in the master file.

Regards

Howard

[Excel Fox]

I haven't come across a situation where opening a CSV file and saving it changes the format to non-CSV :)

[Kenneth Hobson]

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.

[Excel Fox]

Saved as a CSV file and reopened, the value of A2 is now 1.56, not 1.555.

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.

[Kenneth Hobson]

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.