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

/*
In this demonstration we will create yet another Excel WorkBook, this time with 4 WorkSheets
Then we will perform several functions
1) Place data in some cells
2) Autofit the columns by combining some methods
3) Rename each of the sheets
4) Save the workbook
5) Use the same OLE object and open the WorkBook again (it could be a different WorkBook)
6) Iterate over all the WorkSheets and say the WorkSheet name
7) Add another worksheet and give it a name
8) Iterate over the WorkSheets again and say their name
8) Save the WorkBook with a new name
9) Close the WorkBook
10) Quit Excel
*/
    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')

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

--Select Sheet1 and put a value in 1 cell
    if slowdown then
        do
            smsg = .infoDlg~new(d_title,'Select sheet1 and put a value in 1 cell')
            smsg~Execute('ShowTop')
        end
    xlobj~Sheets('Sheet1')~Select
    xlobj~Cells(2,'C')~Value = 'This sheet was orginally named Sheet1'
    xlobj~Range('C:C')~Columns~AutoFit

--Select Sheet2 and put values in 2 cells
    if slowdown then
        do
            smsg = .infoDlg~new(d_title,'Select sheet2 and put a value in 2 cells')
            smsg~Execute('ShowTop')
        end
    xlobj~Sheets('Sheet2')~Select
    xlobj~Cells(3,'D')~Value = 'This sheet was orginally named Sheet2'
    xlobj~Cells(4,'E')~Value = 697
    xlobj~Range('D:E')~Columns~AutoFit

--Select Sheet3 and put values in 3 cells
    if slowdown then
        do
            smsg = .infoDlg~new(d_title,'Select sheet3 and put a value in 3 cells')
            smsg~Execute('ShowTop')
        end
    xlobj~Sheets('Sheet3')~Select
    xlobj~Cells(1,'A')~Value = 'This sheet was orginally named Sheet3'
    xlobj~Cells(2,'B')~Value = 801
    xlobj~Range('A:B')~Columns~AutoFit
    xlobj~Cells(6,'D')~Value = 936
    xlobj~Range('D:D')~Columns~AutoFit

--Select Sheet4 and put values in 4 cells
    if slowdown then
        do
            smsg = .infoDlg~new(d_title,'Select sheet4 and put a value in 4 cells')
            smsg~Execute('ShowTop')
        end
    xlobj~Sheets('Sheet4')~Select
    xlobj~Cells(1,'A')~Value = 'This sheet was orginally named Sheet4'
    xlobj~Cells(2,'A')~Value = 'ABC'
    xlobj~Cells(3,'A')~Value = 'DEF'
    xlobj~Cells(4,'A')~Value = 'XYZ'
    xlobj~Range('A:A')~Columns~AutoFit

--Now lets rename each of our sheets
    if slowdown then
        do
            smsg = .infoDlg~new(d_title,'Rename each of the 4 sheets')
            smsg~Execute('ShowTop')
        end
    xlobj~Sheets('Sheet1')~Name = 'FirstSheet'
    xlobj~Sheets('Sheet2')~Name = 'SecondSheet'
    xlobj~Sheets('Sheet3')~Name = 'ThirdSheet'
    xlobj~Sheets('Sheet4')~Name = 'FourthSheet'

--Lets be nice and reselect cell A1 of the first sheet
    if slowdown then
        do
            smsg = .infoDlg~new(d_title,'Go back to FirstSheet and place cursor in A1')
            smsg~Execute('ShowTop')
        end
    xlobj~Sheets('FirstSheet')~Select --Note the use of the "new" name
    xlobj~Range('A1')~Select

/*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_015.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
    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, but don't close Excel")
            smsg~Execute('ShowTop')
        end
    xlobj~WorkBooks(1)~Close(SaveAll)

--At this point our WorkBook and been saved and closed but note that we don't quit Excel

--Lets re-open the WorkBook we just saved
    if slowdown then
        do
            smsg = .infoDlg~new(d_title,'Re-Open the workbook')
            smsg~Execute('ShowTop')
        end
    infile = '.\RxXL_Automation_015.xls'
    work_book = xlobj~WorkBooks~Open(infile)

--Iterate over all the WorkSheets and "say" some information
    if slowdown then
        do
            smsg = .infoDlg~new(d_title,'Iterate over all the worksheets and say their name')
            smsg~Execute('ShowTop')
        end
    do work_sheet over work_book~WorkSheets
        say 'WorkBook='work_book~name '- WorkSheet='work_sheet~name
    end

--Add another WorkSheet and rename it - all in one line of code
    if slowdown then
        do
            smsg = .infoDlg~new(d_title,'Add another worksheet and rename it')
            smsg~Execute('ShowTop')
        end
    xlobj~Sheets~Add~Name = 'FifthSheet'

--Iterate over the WorkSheets again
    if slowdown then
        do
            smsg = .infoDlg~new(d_title,'Iterate over the worksheets again and say their name')
            smsg~Execute('ShowTop')
        end
    say
    do work_sheet over work_book~WorkSheets
        say 'WorkBook='work_book~name '- WorkSheet='work_sheet~name
    end

--Lets be nice (again) and reselect cell A1 of the first sheet
    if slowdown then
        do
            smsg = .infoDlg~new(d_title,'Select FirstSheet and leave cursor in A1')
            smsg~Execute('ShowTop')
        end
    xlobj~Sheets('FirstSheet')~Select
    xlobj~Range('A1')~Select

--Save the modified WorkBook
    if slowdown then
        do
            smsg = .infoDlg~new(d_title,'Save the modified workbook with a new name')
            smsg~Execute('ShowTop')
        end
    outfile = '.\RxXL_Automation_015_Modified.xls'
    xlobj~DisplayAlerts = .false
    xlobj~ActiveSheet~SaveAs(outfile)
    if slowdown then
        do
            smsg = .infoDlg~new(d_title,'Close the workbook')
            smsg~Execute('ShowTop')
        end
    xlobj~WorkBooks(1)~Close(SaveAll)
    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