Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Repetitive Spreadsheet Formatting Automation
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 -- excel addic ----------------------------------------------------------------------- excel addict's Profile: http://www.excelforum.com/member.php...fo&userid=3009 View this thread: http://www.excelforum.com/showthread.php?threadid=49779 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Repetitive Spreadsheet Formatting Automation
see inline for suggestions. this should get you started
"excel addict" wrote in message news:excel.addict.213cca_1136345701.7132@excelforu m-nospam.com... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Repetitive Spreadsheet Formatting Automation
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! Heres 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Repetitive Numbers | Excel Discussion (Misc queries) | |||
Repetitive keystroke | Excel Discussion (Misc queries) | |||
repetitive | Excel Discussion (Misc queries) | |||
Repetitive IF THEN Statement | Excel Programming | |||
VBScript Automation of Excel Spreadsheet Sorting & Filtering | Excel Programming |