Repetitive Spreadsheet Formatting Automation

[excel addict]

Hey there!

For my job have to do the following and I was wondering whether yo
guys could help me maybe automate this process instead of me having t
do it manually!

Here’s the “routine”

o I Receive, by email a zip file which contains sales data for ou
store, these files are named by the date (eg. 31122005) and come i
.rpt and .dat format.
o I extract the file eg. 04012006.rpt and extract to a folder where al
my previous records are kept
o I open the .rpt file in excel and then have to use the Text Impor
Wizard to sort the data in to columns
o Once this is done I then have to change the column width of all, ba
one, of the columns to 68 pixels wide and another single column to 11
pixels
o One of the columns is an EAN (or APN) number and I thus have t
format this column to display all 13 digits of the EAN and not
decimal number
o Three columns of data have to be have to be formatted to separat
thousands with a comma and also display the value with two cent digits
o Three rows of text have to be deleted
o Another three rows of text (always the same) has to be added
o One cell of data has to be moved from one side of the spreadsheet t
another
o I then have to change the page breaks
o Save the file as an .xls
o Print

I tried to do a macro, but my knowledge of macros is very extensive an
I found that I could only execute the macro on a specific Book that
had created the macro on.

I REAAAAAAALLY hope that you guys can help!

Thanks in Advance

 

[voodooJoe]

see inline for suggestions. this should get you started

"excel addict" <[email protected]>
wrote in message

Hey there!

For my job have to do the following and I was wondering whether you
guys could help me maybe automate this process instead of me having to
do it manually!

Here’s the “routine”

o I Receive, by email a zip file which contains sales data for our
store, these files are named by the date (eg. 31122005) and come in
rpt and .dat format.
o I extract the file eg. 04012006.rpt and extract to a folder where all
my previous records are kept
o I open the .rpt file in excel and then have to use the Text Import
Wizard to sort the data in to columns

if you are inexpeerienced, do up to this point manally. this is a lot to
get some one to write in a news group.

try sewing these snips together into a macro:

o Once this is done I then have to change the column width of all, bar
one, of the columns to 68 pixels wide and another single column to 110
pixels

with activesheet
.usedrange.columnwidth = 68
.columns (###).columnwidth = 110
end with

o One of the columns is an EAN (or APN) number and I thus have to
format this column to display all 13 digits of the EAN and not a
decimal number

.COLUMNS("b:b").NumberFormat = "000000000000000"

o Three columns of data have to be have to be formatted to separate
thousands with a comma and also display the value with two cent digits

.Columns(XX).NumberFormat = "#,##0.00"

o Three rows of text have to be deleted
.Rows("1:3").EntireRow.Delete


o Another three rows of text (always the same) has to be added

.cells(1,1).value = "text"
.cells(2,1).value = "text"

etc ...

o One cell of data has to be moved from one side of the spreadsheet to
another

.cells(3,1).cut .cells(1,1)'this is row#, column#

o I then have to change the page breaks

you'll have to figure out that one

o Save the file as an .xls

SAVE AS METHOD

o Print

PRINTOUT METHOD

 

[cush]

To make your macro available to all your workbooks, save it to your
PERSONAL.xls file, which is a "hidden" file. If you are unfamiliar with this
google for Chip Pearson Personal.xls

Chip is a real guru with an excellent website which explains in precise
terms what steps are necessary to achieve many tasks.