cancel
Showing results for 
Search instead for 
Did you mean: 

Getting "Java Run time Error" when writing to Excel

SOLVED
Highlighted
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

Re: Getting "Java Run time Error" when writing to Excel

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
Community Leader

Re: Getting "Java Run time Error" when writing to Excel

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

Re: Getting "Java Run time Error" when writing to Excel

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

Re: Getting "Java Run time Error" when writing to Excel

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
Top Kudoed Authors