Forum Discussion

CS's avatar
CS
New Contributor
7 years ago

How can I write data to different worksheets?

I'm able to write data to an Excel file, but I don't know how to write data to different worksheets within the same workbook/file.

Can you please help me find a solution? Thanks!

 

 

function WriteExcel(Record)

{

   var Excel,row, column, x;

   x=Record+1;

   Excel = Sys.OleObject("Excel.Application");

   Delay (3000);

  

     Excel.Workbooks.Open("C:\\Users\\...DATA TABLE IDs.xls");

    

    Excel.Cells.Item(x,1).Value=Record;

     Excel.Cells.Item(x,2).Value=ProjectSuite.Variables.VAR1

     Excel.Cells.Item(x,3).Value=ProjectSuite.Variables.VAR2;

     Excel.Cells.Item(x,4).Value=ProjectSuite.Variables.VAR3;

     Excel.Cells.Item(x,5).Value=ProjectSuite.Variables.VAR4;

   Excel.Cells.Item(x,6).Value=ProjectSuite.Variables.VAR5;

     Excel.Cells.Item(x,7).Value=ProjectSuite.Variables.VAR6;

     Excel.cells.item(x,8).Value=ProjectSuite.Variables.VAR7;

     Excel.Cells.Item(x,11).Value=ProjectSuite.Variables.VAR8;

     Excel.Cells.Item(x,15).Value=aqDateTime.Today();

     Excel.Cells.Item(x,16).Value=ProjectSuite.Variables.VAR9;

     //}

     //}

   Excel.ActiveWorkbook.Save();

   Excel.Quit();

}

4 Replies

Sort By
  • shankar_r's avatar
    shankar_r
    Community Hero
    7 years ago

    Hi,

     

    • You don't really need to have Delay between Creating object and opening an excel workbook
    • You can switch between sheets using WorkBook.Sheets.Item("<Sheet name>");

    For Ex:

     I have modified your code

     

    function WriteExcel(Record)
{
      var Excel,row, column, x,sheetObj,wrkbookObj;
      x=Record+1;
      Excel = Sys.OleObject("Excel.Application");
      //Delay (3000);
  
      wrkbookObj = Excel.Workbooks.Open("C:\\Users\\...DATA TABLE IDs.xls");
      
      sheetObj = wrkbookObj.Sheets.Item("Sheet1");
      sheetObj.Cells.Item(x,1).Value=Record;
      sheetObj.Cells.Item(x,2).Value=ProjectSuite.Variables.VAR1;
      
      sheetObj = wrkbookObj.Sheets.Item("Sheet2");
      
      sheetObj.Cells.Item(x,6).Value=aqDateTime.Today();

      wrkbookObj.Save();
      Excel.Quit();
}

     

     

    • CS's avatar
      CS
      New Contributor
      7 years ago

      Thanks Shankar!

  • NisHera's avatar
    NisHera
    Valued Contributor
    7 years ago

    try adding following...

    var worksheet = workbook.Sheets["Sheet2"];         
        worksheet.Activate();
    • CS's avatar
      CS
      New Contributor
      7 years ago

      Thank you so much NisHera!