cancel
Showing results for 
Search instead for 
Did you mean: 

Write a variable value to Excel in Keyword Test

SOLVED
Frequent Contributor

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! 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Leader

Re: Write a variable value to Excel in Keyword Test

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

3 REPLIES 3
Community Leader

Re: Write a variable value to Excel in Keyword Test

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

Frequent Contributor

Re: Write a variable value to Excel in Keyword Test

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.

 

Highlighted
Community Leader

Re: Write a variable value to Excel in Keyword Test

This is using JavaScript

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

Cheers

-Lino

New Here?
Join us and watch the welcome video:
Top Kudoed Authors