/* RxXL_Automation_012.rex */ /* The purpose of this application is to demonstrate the use of ooRexx ActiveX/OLE to automate the creation of an Excel Workbook AND add some formulas to obtain Totals, Averages, etc. */ --Clear the screen call SysCls --Create a "word" list of columns - you'll see where we need this later column_list='A B C D E F G H I J K L M N O P Q R S T U V W X Y Z', 'AA AB AC AD AE AF AG AH AI AJ AK AL AM AN AO AP AQ AR AS AT AU AV AW AX AY AZ', 'BA BB BC BD BE BF BG BH BI BJ BK BL BM BN BO BP BQ BR BS BT BU BV BW BX BY BZ', 'CA CB CC CD CE CF CG CH CI CJ CK CL CM CN CO CP CQ CR CS CT CU CV CW CX CY CZ', 'DA DB DC DD DE DF DG DH DI DJ DK DL DM DN DO DP DQ DR DS DT DU DV DW DX DY DZ', 'EA EB EC ED EE EF EG EH EI EJ EK EL EM EN EO EP EQ ER ES ET EU EV EW EX EY EZ', 'FA FB FC FD FE FF FG FH FI FJ FK FL FM FN FO FP FQ FR FS FT FU FV FW FX FY FZ', 'GA GB GC GD GE GF GG GH GI GJ GK GL GM GN GO GP GQ GR GS GT GU GV GW GX GY GZ', 'HA HB HC HD HE HF HG HH HI HJ HK HL HM HN HO HP HQ HR HS HT HU HV HW HX HY HZ', 'IA IB IC ID IE IF IG IH II IJ IK IL IM IN IO IP IQ IR IS IT IU IV' -- Create the Excel object xlobj = .OleObject~New('Excel.Application') /* Turn the visible attribute on so that we can see what is happening In a production application, this can be set to false */ xlobj~Visible = .true --Create a new workbook with 1 worksheet xlobj~Application~SheetsInNewWorkBook = 1 xlobj~WorkBooks~Add /* Define a variable that indicates how many rows we will be filling You may change this value to prove that formulas described below will stay 'relative' */ rows2load = 35 /* Define a variable that indicates how many columns we will be filling You may change this value to prove that formulas described below will stay 'relative', but don't use less than 4 since well will be doing something to the first 4 columns */ columns2load = 10 do row = 1 to rows2load do column = 1 to columns2load xlobj~Cells(row,column)~Value = random(1,5000) end end /* We know our data starts in Column A - Row 1 Lets assume that we have no idea where our data ends To determine where our data ends we will need to use an Excel constant */ xlLastCell = xlobj~GetConstant('xlLastCell') --Now that we have the constant value, lets use it to determine our last used cell lastcell = xlobj~ActiveCell~SpecialCells(xlLastCell)~Address /* The value in lastcell will be something like $c$r (where c indicates the column and r indicates the row). So we will need to use the parse statement to breakout our max column and max row */ parse var lastcell '$'max_column'$'max_row /* First a word about formulas One of the greatest assets of a spreadsheet is the 'relative' formula Example: Suspose in column B we have data in rows 2-4 If in row 5 we put a formula that adds up rows 2-4 and then insert a row between 3 & 4 (or between 2 & 3) our formula will stay relative and include the inserted row in our total Below you will see formulas that start with '=SUM' - to maintain their relativity, we must indicate the row & column as a 'relative' variable. If a formula was to state "=SUM(R[-1]C:R[-3]C)", we would interpret this as follows: create a total beginning at the current row minus (-) 1 and backing up from the current row minus (-) 3, but stay in the same column. If a formula was to state "=SUM(RC[-1]:RC[-12])", we would interpret this as follows: create a total beginning at the current column minus (-) 1 and backing up from the current column minus (-) 12, but stay on the same row. The variables "R" & "C" do not have to be set in our program, they will be interpreted by ExCel. */ -- Place formulas in cells -- Here our Column will remain constant, but the rows will vary row = max_row + 1 /* our formula row is 1 more than total rows */ used_rows = max_row - rows2load start_row_pos = max_row - used_rows /* Place a COUNTA (counts all non-null cells) formula in column A */ col = "A" xlobj~Cells(row,col)~Formula = "=COUNTA(R[-1]C:R[-"start_row_pos"]C)" /* Place a TOTAL formula in column B */ col = "B" xlobj~Cells(row,col)~Formula = "=SUM(R[-1]C:R[-"start_row_pos"]C)" /* Place an AVERAGE formula in column C */ col = "C" xlobj~Cells(row,col)~Formula = "=AVERAGE(R[-1]C:R[-"start_row_pos"]C)" /* Place a STANDARD DEVIATION formula in column D */ col = "D" xlobj~Cells(row,col)~Formula = "=STDEVA(R[-1]C:R[-"start_row_pos"]C)" /* Now that we have some column formulas, lets prove that the same logic works for row formulas - Remember our Row will remain constant, but the columns will vary */ column_number = column_list~wordpos(max_column) used_columns = column_number - columns2load start_column_position = column_number - used_columns col = word(column_list,column_number + 1) /* Place a TOTAL formula in row 1 */ row = 1 xlobj~Cells(row,col)~Formula = "=SUM(RC[-1]:RC[-"start_column_position"])" /* Place an AVERAGE formula in row 2 */ row = 2 xlobj~Cells(row,col)~Formula = "=AVERAGE(RC[-1]:RC[-"start_column_position"])" /* Now that we have finished creating our workbook, lets give it a name and save it we'll save it in the default Excel folder (which will probably be My Documents' NOT the folder that this application is running in - you can provide any path you'd like */ outfile = '.\RxXL_Automation_012.xls' /* We may want to run this several times and Excel will prompt us about replacing an existing file. Since this conflicts with "automation" we'll need to turn off the Excel warnings */ xlobj~DisplayAlerts = .false --Save the file xlobj~ActiveSheet~SaveAs(outfile) --Close the Workbook xlobj~WorkBooks(1)~Close(SaveAll) --Quit Excel xlobj~Quit