cancel
Showing results for 
Search instead for 
Did you mean: 

Getting "Java Run time Error" when writing to Excel

SOLVED
Occasional Contributor

Getting "Java Run time Error" when writing to Excel

Hi,

Am trying to write my result back to an excel file. Am using the code as below .But am getting "Java Run time error". Any help here would be really helpful. 

 

let Excel = Sys.OleObject("Excel.Application");
var results = Excel.Workbooks.Open("Excel file Path name");

var sheet = results.Sheets.Item("Sheet1");

Excel.Cells.$set("Item" , 0, 18, "Generate Claim Number");

 

I have also tried 

sheet.Cells.$set("Item" , 0, 18, "Generate Claim Number");
sheet.Cells.Item(0, 18).Value = "Generate Claim Number";

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Leader

A more complete working sample just for you

Beware of indexes of Line and Column starting at 0.

 

function writeExcel(Valeur, Line, Column, ExcelFile, SheetName) {
  let excelObject, excelWorkbookObj, excelMainSheet;
  try {
    excelObject               = Sys.OleObject("Excel.Application");
    if (excelObject == null)
      throw Error("Excel n'est pas installé !");
    excelObject.Visible       = false;
    excelObject.DisplayAlerts = false;
    excelWorkbookObj          = excelObject.Workbooks.Open(ExcelFile);
    excelMainSheet            = excelWorkbookObj.Sheets.item(SheetName);
    excelMainSheet.Cells.Item(Line, Column).Value2 = Valeur;
    excelObject.Application.ActiveWorkbook.Save;
  }
  catch(e) {
    Log.Message("Erreur durant la mise à jour Excel", e.message, pmHigher);
  }
  finally {
    if (excelObject != null) {
      excelObject.Application.ActiveWorkbook.Close;
      excelObject.Quit;
      excelObject      = null;
      excelWorkbookObj = null;
      excelMainSheet   = null;
    }
  }
}

 

Un sourire et ça repart

View solution in original post

3 REPLIES 3
Highlighted
Community Leader

To set a value of a cell :

// Item accepte double syntax, index et name
excelMainSheet.Cells.Item("4","J").Value2 = myValue,
excelMainSheet.Cells.Item(4, 10).Value2 = myValue;

Un sourire et ça repart

Highlighted
Community Leader

A more complete working sample just for you

Beware of indexes of Line and Column starting at 0.

 

function writeExcel(Valeur, Line, Column, ExcelFile, SheetName) {
  let excelObject, excelWorkbookObj, excelMainSheet;
  try {
    excelObject               = Sys.OleObject("Excel.Application");
    if (excelObject == null)
      throw Error("Excel n'est pas installé !");
    excelObject.Visible       = false;
    excelObject.DisplayAlerts = false;
    excelWorkbookObj          = excelObject.Workbooks.Open(ExcelFile);
    excelMainSheet            = excelWorkbookObj.Sheets.item(SheetName);
    excelMainSheet.Cells.Item(Line, Column).Value2 = Valeur;
    excelObject.Application.ActiveWorkbook.Save;
  }
  catch(e) {
    Log.Message("Erreur durant la mise à jour Excel", e.message, pmHigher);
  }
  finally {
    if (excelObject != null) {
      excelObject.Application.ActiveWorkbook.Close;
      excelObject.Quit;
      excelObject      = null;
      excelWorkbookObj = null;
      excelMainSheet   = null;
    }
  }
}

 

Un sourire et ça repart

View solution in original post

Highlighted
Occasional Contributor

Thanks very much for the reply. I had found the issue. It was because I didnot know that excel was taking the first row as 1 and not as 0. 

New Here?
Join us and watch the welcome video:
Announcements
Join TechCorner Challenge!
Want a fun and easy way to learn TestComplete? Try solving weekly TechCorner challenges and get into the Leaderboard!


Challenge Status

Get properties of a web page element

See replies!

Compare images using the Region Checkpoint

See replies!

Compare HTML table with Excel file and correct data in Excel file

Participate!

How to execute remote test and obtain results via Test Runner REST API

Participate!
Top Kudoed Authors