Auto format a csv file?
 

Hi,
I have reports I do that are output as csv type. I then must do quite a bit
of formatting and then copy the data into a template.An example of what I
do: Open csv in excel, in col a replace all 2's & 3's with text (same word)
and replace 5's with a different word. col A contains ONLY one of these 3
numbers. then I format 4 cols as $, 2 as numbers. I then sort descending by
col A, ascending by Col B. Their are a few more things but all have to do
with formatting and/or sorting.Can something like this be automated with a
macro or something?
Thanks
Frank

Auto format a csv file?
 

That is what VBA is all about. The best way for a novice to start is to turn
on the auto macro record. Then perform your normal formating. Then turn off
the macro record. You probably will have to customise the macro after it is
recorded. You can post the macro on this website under excel programming if
you need help with the macro.


Here is how you would start.
1) Start new workbook and read Csv file
1) On Excel Toolbar Select Tools - Macro - Record New Macro
2) Perform your normal formating
3) Turn off recording Select Tools - Macro - Stop Recording.
4) To run Macro Seclect Tools - Macro - Macros - Macro1 - Run
5) To Edit Macro Seclect Tools - Macro - Macros - Macro1 - Edit

if you want to post macro, edit macro and then copy and p[aste macro on this
website.

You can also have the macro do the reading of the csv file by starting macro
before you read the csv file. Or have two macros, one for reading and one
for formatting.

"Frank" wrote:

Hi,
I have reports I do that are output as csv type. I then must do quite a bit
of formatting and then copy the data into a template.An example of what I
do: Open csv in excel, in col a replace all 2's & 3's with text (same word)
and replace 5's with a different word. col A contains ONLY one of these 3
numbers. then I format 4 cols as $, 2 as numbers. I then sort descending by
col A, ascending by Col B. Their are a few more things but all have to do
with formatting and/or sorting.Can something like this be automated with a
macro or something?
Thanks
Frank

Auto format a csv file?
 

Frank

Fire up the macro recorder and start doing the stuff you need to do including
opening the csv file.

Most times some alterations to the code is necessary, like changing hard-coded
cell refs to offsets and similar but if you use the Relative Reference button
there won't be too many to change.

See Tushar Mehta's "Beyond the macro recorder" site for tips.

http://www.tushar-mehta.com/excel/vb...rder/index.htm


Gord Dibben MS Excel MVP

On Sun, 27 May 2007 16:11:55 -0400, "Frank"
wrote:

Hi,
I have reports I do that are output as csv type. I then must do quite a bit
of formatting and then copy the data into a template.An example of what I
do: Open csv in excel, in col a replace all 2's & 3's with text (same word)
and replace 5's with a different word. col A contains ONLY one of these 3
numbers. then I format 4 cols as $, 2 as numbers. I then sort descending by
col A, ascending by Col B. Their are a few more things but all have to do
with formatting and/or sorting.Can something like this be automated with a
macro or something?
Thanks
Frank