Instantiating Microsoft Excel COM Objects

Following is a sample code ,which can be used in application engine or Peoplecode to instantiate COM Objects and can read and write data from excel file.


/*Variable Declaration*/

Local object &oWorkApp, &oWorkBook, &oWorkApp1, &oWorkBook1, &oWorkApp2, &oWorkBook2, &oWorkSheet, &oWorkSheet1, &oWorkSheet2;
Local integer &Idx1, &IdxSuccess, &IdxError, &TotalRow, &TotalRowSuccess, &TotalRowError;
Local File &fileLog;
Local string  &InputFile;
Local string &RetPath;


 
try

   &InputFile=&FilePath;/**ABHISHEK**/
   &oWorkApp = CreateObject("COM", "Excel.Application");
   ObjectSetProperty(&oWorkApp, "Visible", True);
   &oWorkBook = ObjectGetProperty(&oWorkApp, "Workbooks");
   &oWorkBook.Open(&InputFile);
   &oWorkSheet = &oWorkApp.Worksheets("Sheet1");
  
 
      /***Output File***/
    &OutputFile= &strRptOutputDir | &strDirSep | "JAPAN_LEAVING_REPORT.xls";
    &oWorkApp1 = CreateObject("COM", "Excel.Application");
   ObjectSetProperty(&oWorkApp1, "Visible", True);
   &oWorkBook1 = ObjectGetProperty(&oWorkApp1, "Workbooks");
   &oWorkApp1.DisplayAlerts = "False";
   &oWorkBook1 = &oWorkApp1.Workbooks.Add();
   &oWorkSheet1 = &oWorkApp1.Worksheets("Sheet1");

  
  
   &Idx1 = 1;
   &IdxSuccess = 0;
   &ThisRec = "Y";
  
For &i=  1 to 3
For &j= 1 TO 71
  &oWorkSheet1.cells( &i,&j).Value = &oWorkSheet.cells( &i,&j).Value;

end-for;
end-for; 
 
   &status = &oWorkApp.ActiveWorkBook.Close( False);
   &oWorkApp.DisplayAlerts = "True";
   &oWorkApp.Quit();
  
   &status1 = &oWorkApp1.ActiveWorkBook.SaveAs(&OutputFile);
   &status1 = &oWorkApp1.ActiveWorkBook.Close( False);
   &oWorkApp1.DisplayAlerts = "True";
   &oWorkApp1.Quit();
 


catch Exception &ex
  
   &status = &oWorkApp.ActiveWorkBook.Close( False);
   &oWorkApp.DisplayAlerts = "True";
   &oWorkApp.Quit();
  
   &status1 = &oWorkApp1.ActiveWorkBook.SaveAs(&OutputFile);
   &status1 = &oWorkApp1.ActiveWorkBook.Close( False);
   &oWorkApp1.DisplayAlerts = "True";
   &oWorkApp1.Quit();
  

end-try;