Creating MS EXCEL Using CreateObject in PeopleSoft

Application Engine is great for batch processing, but it is not a reporting tool. Recently, our users requires every single AE to have a formatted output report, so my teammate came up with the solution that writes .txt file using writeline function. Its very simple, you just need to code it manually. Getting bored of the text file that comes out on my report, I look for some other ways to create a more decent output. Of course, XML Publisher would have been nice but I dont want to go thru that hassle creating a report definition, data source, etc. I browse thru PeopleBooks and found this PeopleCode function CreateObject! It has an example using Excel.Application, a few trial and error, and voala! I created an Excel Report! I only tested it on the NT Server though.

/*How to read data from one cell and writes to another*/
&oWorkApp = CreateObject("COM", "Excel.Application");
ObjectSetProperty(&oWorkApp, "Visible", True);
&oWorkBook = ObjectGetProperty(&oWorkApp, "Workbooks");
&oWorkBook.Open("C:\temp\TEST1.xls");
&oWorkSheet = &oWorkApp.Worksheets("Sheet1");
&oData = &oWorkSheet.Range("A1").Value;
&oWorkSheet.Range("A2").Value = &oData;
&oWorkApp.ActiveWorkbook.SaveAs("C:\temp\TestXLS.xls");
/*How to read data from one cell and writes to different sheet*/
&oWorkApp = CreateObject("COM", "Excel.Application");
ObjectSetProperty(&oWorkApp, "Visible", True);
&oWorkBook = ObjectGetProperty(&oWorkApp, "Workbooks");
&oWorkBook.Open("C:\temp\TEST1.xls");
&oWorkSheet = &oWorkApp.Worksheets("Sheet1");
&oData = &oWorkSheet.Range("A1").Value;
&oWorkSheet2 = &oWorkApp.Worksheets(2);
&oWorkSheet2.Range("A1").Value = &oData;
&oWorkApp.ActiveWorkbook.SaveAs("C:\temp\TestXLS.xls");

/*Add data to cells of the first worksheet in the new workbook*/
&oWorkApp = CreateObject("COM", "Excel.Application");
ObjectSetProperty(&oWorkApp, "Visible", True);
&oWorkBook = ObjectGetProperty(&oWorkApp, "Workbooks");
&oWorkBook.Open("C:\temp\TEST1.xls");
&oWorkSheet = &oWorkApp.Worksheets("Sheet1");
&oWorkSheet.Range("A1").Value = "Last Name";
&oWorkSheet.Range("B1").Value = "First Name";
&oWorkSheet.Range("A1:B1").Font.Bold = True;
&oWorkSheet.Range("A2").Value = "Doe";
&oWorkSheet.Range("B2").Value = "John";
&oWorkApp.ActiveWorkbook.SaveAs("C:\temp\TestXLS.xls");

/*Transfer the data to Excel from Rowset*/
&oWorkApp = CreateObject("COM", "Excel.Application");
ObjectSetProperty(&oWorkApp, "Visible", True);
&oWorkBook = ObjectGetProperty(&oWorkApp, "Workbooks");
&oWorkBook.Open("C:\temp\TEST1.xls");
&oWorkBook = &oWorkApp.Workbooks.Add();
&rs_Awards = CreateRowset(Record.PERTBL);
&rs_Awards.Fill("WHERE FILL.YEAR = '2008' AND FILL.STATUS = 'C'");
&oWorkSheet = &oWorkApp.Worksheets("Sheet1");
For &ie = 1 To &rs_Awards.activerowcount
&oWorkSheet.Cells(&ie, 1).Value = &rs_Awards.getrow(&ie).PERTBL.ID.Value;
&oWorkSheet.Cells(&ie, 2).Value = &rs_Awards.getrow(&ie).PERTBL.COMP.Value;
End-For;
&oWorkApp.ActiveWorkbook.SaveAs("C:\temp\TestXLS.xls");

/*Save an xls file as a CSV*/
&oWorkApp = CreateObject("COM", "Excel.Application");
ObjectSetProperty(&oWorkApp, "Visible", True);
&oWorkBook = ObjectGetProperty(&oWorkApp, "Workbooks");
&oWorkBook.Open("C:\temp\TEST1.xls");
&oWorkApp.ActiveWorkbook.SaveAs("C:\temp\TestXLS.csv", 6);