/* RxXL_Automation_017.rex */
    parse source src
    d_title = substr(src,src~lastpos('\')+1,src~length-src~lastpos('\'))

/*
In this demonstration we will create yet another workbook and insert random numbers.
We will then add some column heading and a title, to which we will format in various different ways
Create 2 different charts
Rename each of the charts
Rename Sheet1
Rearrange the order of the sheets
This demonstration also includes several of the VBA macros that were translated
*/
    slowdown = .true

--Clear the screen
    call SysCls

-- Create the Excel object
    if slowdown then
        do
            smsg = .infoDlg~new(d_title,'Create The Excel Object')
            smsg~Execute('ShowTop')
        end
    xlobj = .OleObject~New('Excel.Application')

--In this demonstration I have moved all GetConstant calls to a single location
    xlLastCell = xlobj~GetConstant(xlLastCell)
    xlCenter = xlobj~GetConstant(xlCenter)
    xlLineMarkers = xlobj~GetConstant(xlLineMarkers)
    xlBottom = xlobj~GetConstant(xlBottom)
    xlBarClustered = xlobj~GetConstant(xlBarClustered)

/*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
    if slowdown then
        do
            smsg = .infoDlg~new(d_title,'Create a workbook with 1 worksheet')
            smsg~Execute('ShowTop')
        end
    xlobj~Application~SheetsInNewWorkBook = 1
    xlobj~WorkBooks~Add

--Place our column headings in Row 1
    if slowdown then
        do
            smsg = .infoDlg~new(d_title,'Add the column headings')
            smsg~Execute('ShowTop')
        end
    headings = 'Year January February March April May June',
               'July August September October November December'
    row = 2
    do aa = 1 to headings~words
        xlobj~cells(row,aa)~Value = headings~word(aa)
    end

--Add some years to column A
    if slowdown then
        do
            smsg = .infoDlg~new(d_title,'Add the row headings')
            smsg~Execute('ShowTop')
        end
    years = '2000 2001 2002 2003 2004'
    column = 1
    do aa = 1 to years~words
        xlobj~cells(aa+2,column)~Value = years~word(aa)
    end

--Add some random data at each column/row intersection
    if slowdown then
        do
            smsg = .infoDlg~new(d_title,'Add some random data at each column/row intersection')
            smsg~Execute('ShowTop')
        end
    start_column = 2
    end_column = 13
    start_row = 3
    end_row = 7

    do row = start_row to end_row
        do column = start_column to end_column
            xlobj~cells(row,column)~Value = random(30000,50000)
        end
    end

--Autofit the column widths
    if slowdown then
        do
            smsg = .infoDlg~new(d_title,'Autofit the column widths')
            smsg~Execute('ShowTop')
        end
/*
Here is the VBA macro that was recorded to perform the desired functions
Compare it to the ooRexx code that follows

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 5/13/2005 by Lee Peedin
'

'
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    Selection.Columns.AutoFit
End Sub
*/

/* Since the macro was recorded WITHIN Excel, all methods are automatically applied to the
   existing object.  In our case we will have to prefix our method calls with our object
   name (xlobj)
   Another thing to note: in the macro cell A1 was already selected, but we can't be sure
   of that in our program so we will manually select it first.
*/
    xlobj~Range('A1')~Select
    xlobj~Range(xlobj~Selection,xlobj~ActiveCell~SpecialCells(xlLastCell))~Select
    xlobj~Selection~Columns~AutoFit

--Center all the data in our selected range
    if slowdown then
        do
            smsg = .infoDlg~new(d_title,'Center all the data in our selected range')
            smsg~Execute('ShowTop')
        end
/*
Here is the VBA macro that was recorded to perform the desired functions
Compare it to the ooRexx code that follows

Sub Macro2()
'
' Macro2 Macro
' Macro recorded 5/13/2005 by Lee Peedin
'

'
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
End Sub
*/

/* We already have our range selected so we need not select it again
   Note that the ooRexx code is much less than the VBA code - this is because the VBA code
   assigns all possible Attributes (even if it assign their default).
   In our case the only attribute we're interested in is the HorizontalAlignment
   Trial & Error is really the only way you can determine what can be elimated
*/
    xlobj~Selection~HorizontalAlignment = xlCenter

--Select cell A1 and enter a title
    if slowdown then
        do
            smsg = .infoDlg~new(d_title,'Select cell A1 and enter a title')
            smsg~Execute('ShowTop')
        end

/*
Here is the VBA macro that was recorded to perform the desired functions
Compare it to the ooRexx code that follows

Sub Macro3()
'
' Macro3 Macro
' Macro recorded 5/13/2005 by Lee Peedin
'

'
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "XYZ Company Monthly Sales"
End Sub
*/

--Following is the literal translation of the macro
--but we've already learned a much simplier way of assigning a value to a cell
/*
    xlobj~Range('A1')~Select
    xlobj~ActivCell~FormulaR1C1 = 'XYZ Company Monthly Sales'
*/
--We'll do this instead
    xlobj~cells(1,1)~Value = 'XYZ Company Monthly Sales'


--Now lets center and merge our title
    if slowdown then
        do
            smsg = .infoDlg~new(d_title,'Now lets center and merge our title')
            smsg~Execute('ShowTop')
        end
/*
Here is the VBA macro that was recorded to perform the desired functions
Compare it to the ooRexx code that follows

Sub Macro4()
'
' Macro4 Macro
' Macro recorded 5/13/2005 by Lee Peedin
'

'
    Range("A1:M1").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Selection.Merge
End Sub
*/

--Use the address of the lastcell to get our last column (the macro used a literal)
    lastcell = xlobj~ActiveCell~SpecialCells(xlLastCell)~Address
    parse var lastcell '$'max_column'$'max_row
    xlobj~Range('A1:'max_column||1)~Select
    xlobj~Selection~HorizontalAlignment = xlCenter
    xlobj~Selection~Merge

--Now lets set some attributes of the title line
    if slowdown then
        do
            smsg = .infoDlg~new(d_title,'Now lets set some attributes of the title line')
            smsg~Execute('ShowTop')
        end

/*
Here is the VBA macro that was recorded to perform the following
Change the font name to Arial Black
Change the font size to 24
Change the font color to red
Change the background color to yellow
Compare it to the ooRexx code that follows

Sub Macro2()
'
' Macro2 Macro
' Macro recorded 5/13/2005 by Lee Peedin
'

'
    With Selection.Font
        .Name = "Arial Black"
        .Size = 10
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
    End With
    With Selection.Font
        .Name = "Arial Black"
        .Size = 24
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
    End With
    Selection.Font.ColorIndex = 3
    With Selection.Interior
        .ColorIndex = 6
        .Pattern = xlSolid
    End With
End Sub
*/
--Remember we ALREADY have our title line selected so no need to select it again
    xlobj~Selection~Font~Name = 'Arial Black'
    xlobj~Selection~Font~Size = 24
    xlobj~Selection~Font~ColorIndex = 3
    xlobj~Selection~Interior~ColorIndex = 6

--Add some attributes to the column headings
    if slowdown then
        do
            smsg = .infoDlg~new(d_title,'Add some attributes to the column headings')
            smsg~Execute('ShowTop')
        end

--Remember that whatever our "Selection" is, it will remain that until something else is selected
--Let's use the same logic as above and select the column headers row
    xlobj~Range('A2:'max_column||2)~Select
    xlobj~Selection~Font~ColorIndex = 3

--Add some attributes to the row headings
    if slowdown then
        do
            smsg = .infoDlg~new(d_title,'Add some attributes to the row headings')
            smsg~Execute('ShowTop')
        end

--Let's use the same logic as above and select the row headers column
    xlobj~Range('A3:A'max_row)~Select
    xlobj~Selection~Font~ColorIndex = 7

--Now that we're finished "beautifying" our workbook, lets create some charts

--We will create a Line chart as a SEPERATE worksheet
--Provide a name for each range
--Add a title to it

--Select the area to be charted - Remember, we know that our data starts in cell B3
    if slowdown then
        do
            smsg = .infoDlg~new(d_title,'Select the area to be charted')
            smsg~Execute('ShowTop')
        end
    xlobj~Range('B3:'max_column||max_row)~select
    xlobj~Charts~Add

--You may notice another chart appear then disappear before the Line chart is shown
--That will happen if the default chart type for your Excel installation is something other
--than Line
    if slowdown then
        do
            smsg = .infoDlg~new(d_title,'Define the chart type we want')
            smsg~Execute('ShowTop')
        end
    xlobj~ActiveChart~ChartType = xlLineMarkers

--Assign some cell values as series names
    if slowdown then
        do
            smsg = .infoDlg~new(d_title,'Assign some cell values as series names')
            smsg~Execute('ShowTop')
        end
    rows_used = max_row - 3 + 1
    do series = 1 to rows_used
        xlobj~ActiveChart~SeriesCollection(series)~Name = '=Sheet1!R'series+2'C1'
    end

--Add a title
    if slowdown then
        do
            smsg = .infoDlg~new(d_title,'Add a title')
            smsg~Execute('ShowTop')
        end
    xlobj~ActiveChart~HasTitle = .True
    xlobj~ActiveChart~ChartTitle~Characters~Text = 'XYZ Company 2000-2004 Monthly Sales Chart 1'

--Rename the Chart
    if slowdown then
        do
            smsg = .infoDlg~new(d_title,'Rename the Chart')
            smsg~Execute('ShowTop')
        end
    xlobj~Sheets('Chart1')~Name = 'Line_Chart'

--Go Back to Sheet1
    if slowdown then
        do
            smsg = .infoDlg~new(d_title,'Go Back to Sheet1')
            smsg~Execute('ShowTop')
        end
    xlobj~Sheets('Sheet1')~Select

--Select our data range again
    if slowdown then
        do
            smsg = .infoDlg~new(d_title,'Select our data range again')
            smsg~Execute('ShowTop')
        end
    xlobj~Range('B3:'max_column||max_row)~select

--Add a bar chart and perform the same tasks to it
    if slowdown then
        do
            smsg = .infoDlg~new(d_title,'Add a bar chart and perform the same tasks to it')
            smsg~Execute('ShowTop')
        end
    xlobj~Charts~Add
    xlobj~ActiveChart~ChartType = xlBarClustered
    do series = 1 to rows_used
        xlobj~ActiveChart~SeriesCollection(series)~Name = '=Sheet1!R'series+2'C1'
    end
    xlobj~ActiveChart~HasTitle = .True
    xlobj~ActiveChart~ChartTitle~Characters~Text = 'XYZ Company 2000-2004 Monthly Sales Chart 2'
    xlobj~Sheets('Chart2')~Name = 'Bar_Chart'

--Go Back to Sheet1
    if slowdown then
        do
            smsg = .infoDlg~new(d_title,'Go Back to Sheet1')
            smsg~Execute('ShowTop')
        end
    xlobj~Sheets('Sheet1')~Select

--Rename Sheet1
    if slowdown then
        do
            smsg = .infoDlg~new(d_title,'Rename Sheet1')
            smsg~Execute('ShowTop')
        end
    xlobj~Sheets('Sheet1')~Name = 'Sales_Data'

--Move Sales_Data back to being the first sheet
    if slowdown then
        do
            smsg = .infoDlg~new(d_title,'Move Sales_Data back to being the first sheet')
            smsg~Execute('ShowTop')
        end
    xlobj~Sheets('Sales_Data')~Select
    xlobj~Sheets('Sales_Data')~Move(xlobj~Sheets(1))

--Place the cursor in cell A1
    if slowdown then
        do
            smsg = .infoDlg~new(d_title,'Place the cursor in cell A1')
            smsg~Execute('ShowTop')
        end
    xlobj~Range('A1')~Select

--Give the file a name
    outfile = '.\RxXL_Automation_017.xls'

--Turn off the alerts
    xlobj~DisplayAlerts = .false

--Save the file
    if slowdown then
        do
            smsg = .infoDlg~new(d_title,'Save the workbook')
            smsg~Execute('ShowTop')
        end
    xlobj~ActiveSheet~SaveAs(outfile)

--Close the Workbook
    if slowdown then
        do
            smsg = .infoDlg~new(d_title,'Close the workbook')
            smsg~Execute('ShowTop')
        end
    xlobj~WorkBooks(1)~Close(SaveAll)

--Quit Excel
    if slowdown then
        do
            smsg = .infoDlg~new(d_title,'Quit Excel')
            smsg~Execute('ShowTop')
        end
    xlobj~Quit
exit

::requires 'oodwin32.cls'
::class infoDlg subclass userdialog public
::method Init
    expose width height d_title d_text
    use arg d_title, d_text
    self~Init:super()
    width = 350
    height = 45
    rc=self~CreateCenter(width,height,d_title,,,'Arial',10)

::method DefineDialog
    expose width height d_title d_text
    forward class(super) continue
    self~AddButton( 1,width-60 ,height-20,50,15,'OK','Ok','DEFAULT')
    self~AddText(1,5,350,15,d_text)

::method Ok
    self~OK:super
return self~finished