Repetitive Spreadsheet Formatting Automation

see inline for suggestions. this should get you started

"excel addict" <[email protected]>
wrote in message
news:[email protected]...
>
> 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
>
> I tried to do a macro, but my knowledge of macros is very extensive and
> I found that I could only execute the macro on a specific Book that I
> had created the macro on.
>
> I REAAAAAAALLY hope that you guys can help!
>
> Thanks in Advance!
>
>
> --
> excel addict
> ------------------------------------------------------------------------
> excel addict's Profile:
> http://www.excelforum.com/member.php...o&userid=30093
> View this thread: http://www.excelforum.com/showthread...hreadid=497790
>

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.

"excel addict" wrote:

>
> 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
> 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
> 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
> 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
> 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 to
> 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 and
> I found that I could only execute the macro on a specific Book that I
> had created the macro on.
>
> I REAAAAAAALLY hope that you guys can help!
>
> Thanks in Advance!
>
>
> --
> excel addict
> ------------------------------------------------------------------------
> excel addict's Profile: http://www.excelforum.com/member.php...o&userid=30093
> View this thread: http://www.excelforum.com/showthread...hreadid=497790
>
>