Jan 31, 2013

PeopleSoft : AE Peoplecode to read data from excel

Local array of string &a;
&sFilePath = "C:\temp\template.xls";
&oWorkApp = CreateObject("COM", "Excel.Application");
ObjectSetProperty(&oWorkApp, "Visible", True);
&oWorkBook = ObjectGetProperty(&oWorkApp, "Workbooks");
&oWorkBook.Open(&sFilePath);
&oWorkSheet = &oWorkApp.Worksheets("Sheet1");
&rowcount = &oWorkSheet.Usedrange.rows.count;
&cellcount = &oWorkSheet.Usedrange.cells.count;
&columncount = &oWorkSheet.Usedrange.columns.count;
MessageBox(0, "", 0, 0, "RowCount:" | &rowcount);
MessageBox(0, "", 0, 0, "CellCount:" | &cellcount);
MessageBox(0, "", 0, 0, "ColumnsCount:" | &columncount);
For &i = 1 To &rowcount
   rem  &a = CreateArray("");
   For &j = 1 To &columncount
      &data = &oWorkSheet.Cells(&i, &j).Value;
      rem &a.Push(&data);
      MessageBox(0, "", 0, 0, "data in" | &i | " row " | &j | " column :" | &data);
   End-For;
End-For;
&oWorkApp.ActiveWorkBook.Close();
&oWorkApp.DisplayAlerts = "True";
&oWorkApp.Quit();

4 comments:

  1. AnonymousJuly 24, 2013

    Hello,
    Thank you for the code, I have a problem tough... because the file I open ( an .csv file) as an Excel file, does not close correctly.

    Now when I want to delete the file , it says it has been used for another program.

    Please help me

    Thanks

    Eva

    ReplyDelete
  2. I also have the same issue. Does anyone know alternative function for Quit. &oWorkApp.Quit();

    Thanks

    ReplyDelete
  3. Hi Chitanya ,

    Thank you so much for your blog for importing Excel using peoplecode in AE .
    I have same requirment . when i was trying ,I am getting following error .

    Invalid parameter CreateObject: Excel.Application Invalid class string

    I am kind of stuck , needed you input . Please let me know if i have missed any thing .
    If possible please call me at my cell

    Regards
    Pavan Sharma
    Cell +91 9538138074

    ReplyDelete
    Replies
    1. Pavan,
      Did you check if you have Microsoft Office installed on the App Server you are running the process. If MS Office is not installed you have to get it installed before you can run this.

      Delete