Creating Excel Files


With SQR, you were always limited to writing CSV files, unless you had some sort of overpriced third party app you could call.

Well, with App Engine (and more specifically using a PeopleCode action in App Engine) you can create Excel files via the Microsoft Excel COM object, as long as the process scheduler is running on Windows.

The following is an example of doing so, from a template file called template.xls, which needs to sit on the server somewhere. The template file exists, because most of the time you don't want to be doing everything from PeopleCode. Mine usually contains images like company letterhead layout.

/* Set up the Excel COM objects and open the template file */
Local object &oWorkApp, &oWorkBook;
&oWorkApp = CreateObject("COM", "Excel.Application");
&oWorkApp.DisplayAlerts = "False";
&oWorkBook = ObjectGetProperty(&oWorkApp, "Workbooks");
&oWorkBook.Open("C:\some_path_to\template.xls");
&oWorkSheet = &oWorkApp.Worksheets("Sheet1");
&oWorkApp.ActiveWorkBook.SaveAs("C:\your_output_file.xls");

/* then go crazy with your worksheet object */
/* eg */
&oWorkSheet.Cells(1, 1).Value = "I'm adding stuff to be bolded";
&oWorkSheet.Cells(1, 1).Font.Bold = True; 

/* Save Excel file and quit */
&oWorkApp.ActiveWorkBook.Save();
&oWorkApp.ActiveWorkBook.Close();
&oWorkApp.DisplayAlerts = "True";
&oWorkApp.Quit();
You'll need to change the Open path and SaveAs path in the code above which also uses Sheet1 as the worksheet name.

That's it! Of course with COM objects, all the properties and methods available for use in VBA are available here. What I usually do is record a macro, and transfer over the code, but using PeopleSoft variable notation of course.


Revision #1
Created Wed, Jul 31, 2019 7:38 PM by PeopleSoft Wiki
Updated Wed, Jul 31, 2019 7:41 PM by PeopleSoft Wiki