/* BalSheetToExcel.rex */
/*
Les Koehler
9Jan2007
Reads in the Balance Sheet .prt file and places data in Excel
*/
Arg monyy '('print
Call Time('r')                     /* Just to see how long this takes */
print?=Abbrev(print,'PR')
/* Define location of the .prt file  WILL HAVE TO BE CHANGED DEPENDENT ON SYSTEM ITS RUNNING ON */
file_path = 'F:\Documents and Settings\Les\My Documents\HOA\Financials\'
Trace or
parse value hoadate(monyy) with err? mon yy rest
if err? then do
  say mon yy rest
  exit

end
months='JanFebMarAprMayJunJulAugSepOctNovDecJan'
file_name=mon||yy'_BalanceSheet.prt'

/*file_name = 'Oct06_BalanceSheet.prt' */
--file_name = 'Nov06_BalanceSheet.prt'
infile = file_path||file_name
Parse Var file_name fname'.'.
outfile = file_path||fname'.xls'
Call syscls
Call killexcel
Signal On SYNTAX

xlobj = .oleObject~new('Excel.Application') /* Create an Excel object */
xlobj~Visible = .true                           /* Make Excel visible */
xlobj~Application~SheetsInNewWorkBook = 1   /*Create a new workbook with 1 worksheet */
xlobj~WorkBooks~Add
font_name = 'Arial Rounded MT Bold' /* Define the font attributes we'll use later */
font_size = 9      /* Leave the font_size BIG - the printing to a single page will */
                    /* take care of it fitting on the sheet */

/* 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')
xllandscape   = xlobj~GetConstant('xlLandScape')
xlpaperletter = xlobj~GetConstant('xlPaperLetter')

Parse Var fname curmonth +3 curyear +2 report
hit=Lastpos(curmonth,months)
prevmonth=Substr(months,hit-3,3)
If prevmonth='Dec' Then prevyear=Right(curyear-1,2,0)
Else prevyear=curyear
known?.=0                        /* Known names */
prev_file=prevmonth||prevyear||report'.xls'
txtstream = .stream~new(file_path||prev_file)
oldfile="'"file_path||"["prev_file"]Sheet1'!"
exists?=txtStream~query('exists')\= ''
if exists? then call get_cell_names file_path||prev_file

/* 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
assets=.false
liability=.false
assets_replace=0
assets_restore=0
liability_replace=0
liability_restore=0
note_payable=''
tot_res_funds=''
tot_res_liab=''
sum_assets_replace='sum('
sum_assets_restore='sum('
sum_liability_replace='sum('
sum_liability_restore='sum('

/* Define an array to store our header lines - need to do all formatting before insert of headers */
header_lines = .array~new

row = 5                             /* Starting point for report data */

Do i = 1 To iarray~items    /* Process each array element (rows in our text file) */
  aline = iarray[i]~strip
  If aline='' | Left(aline~word(1),1) = '_' ,
   | Left(aline~word(1),1) = '=' Then Iterate i   /* Don't need these line */

  If Pos('$',aline)>0 Then found_title = .true
  If \found_title Then header_lines[header_lines~items + 1] = aline         /* These must be header lines */
  Else Do
trace or
    row = row + 1
    If \assets Then Do                           /* Add missing title */
      assets=.true
      column = 1
      xlobj~Cells(row,column)~Value = '      ASSETS'
      If exists? Then Do
        column = 4
        xlobj~Cells(row,column)~Value = '   Net Change'
        column = 1
      End
      skip_line=.true
      row = row + 2
    End
    aline=Translate(aline,' ','$')             /* Remove dollar signs */
    col_a_words = aline~words - 1
    col_a_data  = aline~subword(1,col_a_words)
    column = 1
    If col_a_data~word(1) = 'TOTAL' Then Do         /* Make it pretty */
      col_a_data = '       'col_a_data
      skip_line = .true       /* Set a boolean to pretty up the print */
    End
    title=titles(col_a_data)
    name='_'Translate(Space(title),'______','()/& -')
    xlobj~Cells(row,column)~Value = title
    column = 2
    data=Word(aline,col_a_words+1)
    xlobj~Cells(row,column)~Value=data
    xlobj~Cells(row,column)~name = name
    If exists? & known?.name Then Do                /* Calc Net Change */
      column=4
      xlobj~Cells(row,column)~formula='=RC[-2]-'oldfile||name
    End
    talk?=0
    data=space(translate(data,'   ',',()'),0)
    out=''
    if word(title,1)\='TOTAL' then do
      If Pos('Repl',title)>1 | pos('Roof',title)>1 Then Do
        if talk? then out='Accumulating "'space(title)'" as a'
        if talk? then out=out 'Replacement'
        If \liability Then do
          if talk? then out=out 'asset. 'assets_replace '+' data '='
          assets_replace=assets_replace+data
          if sum_assets_replace='sum(' then ,
           sum_assets_replace=sum_assets_replace||name
          else sum_assets_replace=sum_assets_replace','name
          if talk? then out=out assets_replace
        end
        Else do
          if talk? then out=out 'liability. 'liability_replace '+' data '='
          liability_replace=liability_replace+data
          if sum_liability_replace='sum(' then ,
           sum_liability_replace=sum_liability_replace||name
          else sum_liability_replace=sum_liability_replace','name
          if talk? then out=out liability_replace
        end
      End
      If Pos('Rest',title)>1 | pos('EQ Note',title)>1 Then Do
        if talk? then out='Accumulating "'space(title)'" as a'
        if talk? then out=out 'Restoration'
        If \liability Then do
          if talk? then out=out 'asset. 'assets_restore '+' data '='
          assets_restore=assets_restore+data
          if sum_assets_restore='sum(' then ,
           sum_assets_restore=sum_assets_restore||name
          else sum_assets_restore=sum_assets_restore','name
          if talk? then out=out assets_restore
        end
        Else do
          if talk? then out=out 'liability. 'liability_restore '+' data '='
          liability_restore=liability_restore+data
          if sum_liability_restore='sum(' then ,
           sum_liability_restore=sum_liability_restore||name
          else sum_liability_restore=sum_liability_restore','name
          if talk? then out=out liability_restore
        end
      End
      if talk? & out\='' then say out
      if pos('Note Payable',title)>0 then note_payable=name
    end
    if space(col_a_data)='TOTAL RESERVE FUNDS' then tot_res_funds=name
    if space(col_a_data)='TOTAL RESERVE LIABILITIES' then ,
     tot_res_liab=name
    If Space(col_a_data)='TOTAL LIABILITIES & EQUITY' Then Leave i

    if Space(col_a_data)='TOTAL ASSETS' Then liability=.true
    If skip_line Then Do
--      row = row + 1
      skip_line = .false
      Select
        When Space(col_a_data)='TOTAL ASSETS' Then Do
          row = row + 2
          col_a_data='    LIABILITIES & EQUITY'  /* Add missing title */
      column = 1
      xlobj~Cells(row,column)~Value = col_a_data
        End
--        Otherwise col_a_data=' '
          otherwise nop
      End
  --    column = 1
  --    xlobj~Cells(row,column)~Value = col_a_data
      row = row + 1
    End
  End
End
row=row+3
column=1
xlobj~Cells(row,column)~Value = '---- Cross Check ----'
row=row+1
titles='Assets Liabilities Error'
do column=1 to words(titles)
  xlobj~Cells(row,column+1)~Value = word(titles,column)
end
sum='=sum(r[-2]c:r[-1]c)'
absolute='=sum(abs(r[-2]c),abs(r[-1]c))'
diff='=rc[-2]-rc[-1]'
sum_assets_restore='='sum_assets_restore')'
sum_assets_replace='='sum_assets_replace')'
sum_liability_replace='=abs('sum_liability_replace'))'
sum_liability_restore='=abs('sum_liability_restore'))'
/*
say 'sum_assets_restore='sum_assets_restore
say 'sum_assets_replace='sum_assets_replace
say 'sum_liability_replace='sum_liability_replace
say 'sum_liability_restore='sum_liability_restore
line.1='Restoration' assets_restore liability_restore diff
line.2='Replacement' assets_replace liability_replace diff
*/
line.1='Restoration' sum_assets_restore sum_liability_restore diff
line.2='Replacement' sum_assets_replace sum_liability_replace diff
line.3='Total'       sum            sum               absolute
line.4=''
--line.5='RESERVES_vs._NOTE' assets_restore+assets_replace,
line.5='RESERVES_vs._NOTE' '=sum('tot_res_funds',abs('tot_res_liab'))' ,
  '='note_payable diff
line.0=5
row=row+1
do l=1 to line.0
  if line.l\='' then do
    do column=1 to 4
      if substr(word(line.l,column),1,1)='=' then ,
       xlobj~Cells(row+l,column)~formula= word(line.l,column)
      else xlobj~Cells(row+l,column)~Value =,
       translate(word(line.l,column),' ','_')
    end
  end
end

lastcell = xlobj~ActiveCell~SpecialCells(xllastcell)~Address    /* Determine last cell used */
Parse Var lastcell '$'max_column'$'max_row  /* Parse that for column & 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:'lastcell)~Select */
xlobj~columns('B:D')~Select
myselection = xlobj~Selection
mySelection~HorizontalAlignment = xlright
/*xlobj~Columns('B:B')~Select */
/*xlobj~Selection~NumberFormat = ' */
myselection~NumberFormat = '#,##0.00'

/* 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
    box?=1
    if c=1 then do
      next_column_letter=col_letters~word(2)
      xlobj~Range(next_column_letter||r)~Select
      mySelection_next = xlobj~Selection
      if mySelection_next~Value = .nil then box?=0
    end
    if mySelection~Value \= .nil & box? 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]
    xlobj~Cells(i,1)~Font~Name      = font_name /* Set the font name and size for the header line */
    xlobj~Cells(i,1)~Font~Size      = font_size
    xlobj~Range('A'i':'max_column||i)~Select
    mySelection                     = xlobj~Selection
    mySelection~HorizontalAlignment = xlCenter  /* Center and merge the header line */
    mySelection~Merge
end

xlobj~DisplayAlerts = .false        /* Don't alert us if the document already exists */
xlobj~ActiveSheet~SaveAs(outfile)   /* Save the document */

/* Set up information to print */
xlobj~ActiveSheet~PageSetup~PrintArea         = '$A$1:'lastcell
xlobj~ActiveSheet~PageSetup~LeftMargin        = xlobj~InchesToPoints(.7)
xlobj~ActiveSheet~PageSetup~RightMargin       = xlobj~InchesToPoints(.5)
xlobj~ActiveSheet~PageSetup~TopMargin         = xlobj~InchesToPoints(.5)
xlobj~ActiveSheet~PageSetup~BottomMargin      = xlobj~InchesToPoints(.5)
xlobj~ActiveSheet~PageSetup~HeaderMargin      = xlobj~InchesToPoints(0)
xlobj~ActiveSheet~PageSetup~FooterMargin      = xlobj~InchesToPoints(0)
/*
xlobj~ActiveSheet~PageSetup~Orientation       = xlLandscape
xlobj~ActiveSheet~PageSetup~Orientation       = xlPortrait
*/
xlobj~ActiveSheet~PageSetup~PaperSize         = xlPaperLetter
xlobj~ActiveSheet~PageSetup~Zoom              = .false
xlobj~ActiveSheet~PageSetup~FitToPagesWide    = 1
xlobj~ActiveSheet~PageSetup~FitToPagesTall    = 1
if print? then do
  xlobj~ActiveWindow~SelectedSheets~PrintOut  /* Comment out this line to NOT print */
end
xlobj~Range('A1')~Select                    /* Be nice and place the cursor at the top of the document */
xlobj~WorkBooks(1)~Close(SaveAll)           /* Close the document */
xlobj~Quit                                  /* Quit Excel */
say 'Your Completed Excel Document Was Saved As:' outfile
say 'Elasped Time To Convert To Excel Was:' time('e') 'Seconds'
exit
get_cell_names:
  procedure expose known?.
  parse arg file
-- Create the Excel object
    xlobj = .OleObject~New('Excel.Application')

/*
    Turn the visible attribute off - there's nothing to see in this demostration
*/
    xlobj~Visible = .false

--Open the existing workbook
    infile = file
    xlobj~WorkBooks~Open(file)

--We need to determine our lastcell (we'll need the constant to do that)
    xlLastCell = xlobj~GetConstant('xlLastCell')
    lastcell = xlobj~ActiveCell~SpecialCells(xlLastCell)~Address
    parse var lastcell '$'max_column'$'max_row

    do r=5 to max_row
      parse value examine_cell(r) with flag name
      if flag='VALUE' then do
        if name='---- Cross Check ----' then leave r

        name='_'translate(space(name),'_______',':()/& -')
        known?.name=1
      end
    end

--Quit Excel
    xlobj~Quit
return

Examine_Cell: procedure expose xlobj max_row
  arg row
  cell_value = xlobj~Cells(row,'A')~Value
  cell_formula = xlobj~Cells(row,'A')~Formula
  select
    when cell_formula = '' then do
      c = 'EMPTY'
      cv = ''
    end
    when cell_value \= cell_formula then do
      c = 'FORMULA'
      cv = cell_formula
    end
    otherwise do
      c = 'VALUE'
      cv = cell_value
    end
  end
  if c\='VALUE'& row<max_row then return 'EMPTY'
  if cv='ASSETS' then return 'EMPTY'
  if cv='LIABILITIES & EQUITY' then return 'EMPTY'
/*
  cell_value = xlobj~Cells(row,'B')~Value
  cell_formula = xlobj~Cells(row,'B')~Formula
  select
    when cell_formula = '' then do
      c = 'EMPTY'
      cv = ''
    end
    when cell_value \= cell_formula then do
      c = 'FORMULA'
      cv = cell_formula
    end
    otherwise do
      c = 'VALUE'
      cv = cv cell_value
    end
  end
*/
/*
    say 'Cell' col||row"'s Contents Are "cv c
*/
return c cv


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
::requires "KillExcel.rex"
::requires "titles.rex"
::requires "hoadate.rex"