Forum Discussion

DCat1223's avatar
DCat1223
Frequent Contributor
6 years ago

Write a variable value to Excel in Keyword Test

Hi.

 

I have a Keyword test I am looking for assistance with.  I am testing a process that creates a position that will later be filled by a newly hired employee.  In the create postion process, I can identify the position(s) being created by a unique ID number that is generated for each position created.  I can capture this Position ID using a Variable (setting a temporary variable = to the contentText of the object), but what I do not know how to do is to write the Position ID to an excel spreadsheet (not the sheet I am using for my create position data, as it is locked by this test).  This Position ID will be used in a different test to assign workers to positions.

 

Does anybody have any idea on how to write this captured value to Excel in a Keyword test without a great deal of complicated code?  I am not a strong coede writer.

 

Thank you in advance for your attention! 

  • LinoTadros's avatar
    LinoTadros
    6 years ago

    I noticed you mentioned that you are trying to write the value to a different sheet of the workbook in excel.

    That won't work.  The Workbook HAS to be closed when you access it from TestComplete due to the fact that Excel Workbooks REQUIRE exclusive locks.  Even if it is a different sheet, it won't work, the workbook has to be closed.

     

    If that is not a big deal, then the code below should get you going to write whatever value you want to Excel:

    function WriteDataToExcel()
{
  let app = Sys.OleObject("Excel.Application");
  let book = app.Workbooks.Open("C:\\Users\\Lino\\Documents\\LinoTest.xlsx");
  let sheet = book.Sheets.Item("Sheet1");
  sheet.Cells.Item(1, 1).Value2 = "Lino" ;
  book.Save();
  app.Quit();
}

    This will write the value "Lino" to the A1 cell of the Sheet1 of the workbook.

     

    Hope that helps

    Cheers

    Lino

  • LinoTadros's avatar
    LinoTadros
    Community Hero
    6 years ago

    I noticed you mentioned that you are trying to write the value to a different sheet of the workbook in excel.

    That won't work.  The Workbook HAS to be closed when you access it from TestComplete due to the fact that Excel Workbooks REQUIRE exclusive locks.  Even if it is a different sheet, it won't work, the workbook has to be closed.

     

    If that is not a big deal, then the code below should get you going to write whatever value you want to Excel:

    function WriteDataToExcel()
{
  let app = Sys.OleObject("Excel.Application");
  let book = app.Workbooks.Open("C:\\Users\\Lino\\Documents\\LinoTest.xlsx");
  let sheet = book.Sheets.Item("Sheet1");
  sheet.Cells.Item(1, 1).Value2 = "Lino" ;
  book.Save();
  app.Quit();
}

    This will write the value "Lino" to the A1 cell of the Sheet1 of the workbook.

     

    Hope that helps

    Cheers

    Lino

    • DCat1223's avatar
      DCat1223
      Frequent Contributor
      6 years ago

      Hey Lino...  Thank you.  This looks promising.  I am having trouble getting this to run.  I plugged this into a scritp, but am getting the following Syntax error:

       

           At "sFunction Write Data to Excel()

           "

           Error 110" SYNTAX ERRPR whil leatind character "{".

       

           Error Location:

           Unit: "Workday Testing\Create Postion\Script\Unit1"

           Line: 2 Column : 1.

       

      It doesn't like the "{".  

       

      What am I missing here?  

       

      Thank you.  

      Dave.

       

      • LinoTadros's avatar
        LinoTadros
        Community Hero
        6 years ago

        This is using JavaScript

        You are probably using a different language for scripting in TestComplete.

        Cheers

        -Lino