/* for_les_prt.rex */ /* Lee Peedin 11/18/2006 Reads in the sample .prt file and places data in Excel */ -- Clear the screen call time('r') -- Just to see how long this takes call SysCls signal on Syntax -- Create an Excel object xlobj = .oleObject~new('Excel.Application') -- Make Excel visible xlobj~Visible = .true --Create a new workbook with 1 worksheet xlobj~Application~SheetsInNewWorkBook = 1 xlobj~WorkBooks~Add -- Define the font attributes we'll use later font_name = 'Arial' font_size = 10 -- Need this to convert column numbers to column letters col_letters = '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' -- Retrieve some constant values we'll need later (they're stored within Excel) xlLastCell = xlobj~GetConstant('xlLastCell') xlRight = xlobj~GetConstant('xlRight') xlEdgeLeft = xlobj~GetConstant('xlEdgeLeft') xlEdgeTop = xlobj~GetConstant('xlEdgeTop') xlEdgeBottom = xlobj~GetConstant('xlEdgeBottom') xlEdgeRight = xlobj~GetConstant('xlEdgeRight') xlContinuous = xlobj~GetConstant('xlContinuous') xlCenter = xlobj~GetConstant('xlCenter') -- Define location of the .prt file WILL HAVE TO BE CHANGED DEPENDENT ON SYSTEM ITS RUNNING ON file_path = 'c:\Documents and Settings\Les\My Documents\HOA\Financials\' file_name = 'G5401087.prt' infile = file_path||file_name parse var file_name fname'.'. outfile = file_path||fname'.xls' -- Fastest method in ooRexx to read in an entire file and convert it to an array istream = .stream~new(infile) iarray = istream~charin(,istream~chars)~makearray istream~close -- Define some boolean values (you'll see their use as the code progresses) found_title = .false skip_line = .false -- Define an array to store our header lines - need to do all formatting before insert of headers header_lines = .array~new -- Starting point for report data row = 4 -- Process each array element (rows in our text file) do i = 1 to iarray~items aline = iarray[i]~strip -- Such lines as these don't need to be included if aline~word(1) = '-------' | aline~word(1) = '=======' then iterate i -- Find the first line with that is not a header if aline~words = 13 & aline~word(aline~words) = 'Total' then found_title = .true -- If we haven't found the header line yet, save the row in our array for later use if \found_title then -- These must be header lines header_lines[header_lines~items + 1] = aline else do row = row + 1 -- Based on the word count, determine where to put our data select when aline~words < 13 then -- A "description row" do column = 1 xlobj~Cells(row,column)~Value = aline end when aline~words = 13 then -- A row with title data do column = 2 do w = 1 to aline~words xlobj~Cells(row,column)~Value = aline~word(w) column = column + 1 end end otherwise -- Must be data do -- All words before our data numbers need to be in column 1 col_a_words = aline~words - 13 col_a_data = aline~subword(1,col_a_words) column = 1 -- Indent the Total rows if col_a_data~word(1) = 'Total' then do col_a_data = ' 'col_a_data -- Set a boolean value to "pretty up" the document skip_line = .true end if col_a_data~word(1)='CURRENT' then do col_a_data='NET INCOME/(LOSS)' end xlobj~Cells(row,column)~Value = col_a_data column = 2 do w = col_a_words + 1 to aline~words if left(reverse(word(aline,w)),1)='-'then do temp=word(aline,w) temp='-'left(temp,length(temp)-1) xlobj~Cells(row,column)~Value=temp end else do xlobj~Cells(row,column)~Value = aline~word(w) end column = column + 1 end end end if skip_line then do row = row + 1 skip_line = .false end end end /* At this point our data is loaded We know we started at row 5 column 1, but we don't know where we ended, so find the last cell used. and select that range */ lastcell = xlobj~ActiveCell~SpecialCells(xlLastCell)~Address parse var lastcell '$'max_column'$'max_row -- Set the font name and size for the used range xlobj~Range('A5:'lastcell)~Select mySelection = xlobj~Selection mySelection~Font~Name = font_name mySelection~Font~Size = font_size -- Autofit the column widths to match our data xlobj~Columns('A:'max_column)~select xlobj~Selection~Columns~AutoFit -- Right justify all data xlobj~Range('A5:'max_column||5)~Select mySelection = xlobj~Selection mySelection~HorizontalAlignment = xlRight -- Draw boxes around only the cells that are not .nil max_column_number = col_letters~wordpos(max_column) do r = 5 to max_row do c = 1 to max_column_number column_letter = col_letters~word(c) xlobj~Range(column_letter||r)~Select mySelection = xlobj~Selection if mySelection~Value \= .nil then do mySelection~Borders(xlEdgeLeft)~LineStyle = xlContinuous mySelection~Borders(xlEdgeTop)~LineStyle = xlContinuous mySelection~Borders(xlEdgeBottom)~LineStyle = xlContinuous mySelection~Borders(xlEdgeRight)~LineStyle = xlContinuous end end end -- Now that we are done formatting, go back and insert our header line data do i = 1 to header_lines~items xlobj~Cells(i,1)~Value = header_lines[i] -- Set the font name and size for the header line xlobj~Cells(i,1)~Font~Name = font_name xlobj~Cells(i,1)~Font~Size = font_size xlobj~Range('A'i':'max_column||i)~Select mySelection = xlobj~Selection -- Center and merge the header line mySelection~HorizontalAlignment = xlCenter mySelection~Merge end -- Be nice and place the cursor at the top of the document xlobj~Range('A1')~Select -- Don't alert us if the document already exists xlobj~DisplayAlerts = .false -- Save the document xlobj~ActiveSheet~SaveAs(outfile) -- Close the document xlobj~WorkBooks(1)~Close(SaveAll) -- Quit Excel xlobj~Quit say 'Your Completed Excel Document Was Saved As:' outfile et = time('e') say 'Elasped Time To Convert To Excel Was:' et 'Seconds' exit Syntax: say 'SYNTAX Error On Line' SIGL 'ERRORTEXT'(rc) -- If we have an error, we want to be sure to quit Excel, don't it will be an orphaned process xlobj~Quit exit