Forum Discussion

yaacovk's avatar
yaacovk
Contributor
9 years ago

How to write to Excel file

Hi, To write to Excel file, I found only 1 script example (in javascript), located at: https://support.smartbear.com/viewarticle/20878/ But this script support only writing to Excel 2003-2007, as ...
yaacovk's avatar
yaacovk
Contributor

I tried it on Excel 2010 on an Existing file but without success.

I tried also on Excel 2007, and it succeeded only a few times. Perhaps it is related to Excel propeties, but I didn't find which configuration to change.

In "Format cell> Protection" is unlocked.

In Properties (right click on Excel file)> Security, all users have Full control on this file.

Sometimes, in some attempts of this test, Text sent to another Excel file with gibrish name.

 

 Is it possible that if text is sent form output of previous function in flow, this is causing the problem? (I checked in this case that the text sent is really String). I have not found yet a solution for that.

hautzenroeder1's avatar
hautzenroeder1
Occasional Contributor
9 years ago

My example above works with Excel 2010.

 

This opens a new Excel spreadsheet.

 

To open an existing Excel spreadsheet, use

Excel.Workbooks.Open(sFilePath)

where

sFilePath is the path to the existing Excel file.

Example:

sFirePath = "D:\Test\Test.xlsx"

 

 

  • yaacovk's avatar
    yaacovk
    Contributor
    9 years ago

    In your example you propose to use SaveAs.

    This syntax cause a javascript error.

    Is it possible it doesn't match javascript syntax?

    • hhagay's avatar
      hhagay
      Contributor
      9 years ago

      Well,

      I have implemented the following code and I am receiving errors:

       

      The code:

      function WriteDataToExcel(fname, sheetName)
      {
      let app = Sys.OleObject("Excel.Application");
      let book = app.Workbooks.Open(fname);
      let sheet = book.Sheets(sheetName);
      let rowCount = sheet.UsedRange.Rows.Count;
      let columnCount = sheet.UsedRange.Columns.Count;
      sheet.Cells(rowCount, columnCount+1)= rowCount+","+columnCount ;

      book.Save();
      app.Quit();
      }

       

      The error:

      book.Sheets is not a function

       

      any help is appreciated.

       

      Thank you

       

       

      • HKosova's avatar
        HKosova
        SmartBear Alumni (Retired)
        9 years ago

        Hi hhagay,

         

        In JavaScript (not JScript) you need to access Excel collection items via the .Item property. This applies to the .Sheets, .Cells and similar collections.


        Also, in JavaScript you cannot assign to an indexed property directly:

        sheet.Cells.Item(rowCount, columnCount+1) = rowCount+","+columnCount ;

        You need to use either the $set method, or some property of the object you are assigning to:

        sheet.Cells.Item.$set(rowCount, columnCount+1, rowCount+","+columnCount);
        // or
        sheet.Cells.Item(rowCount, columnCount+1).Value2 = rowCount+","+columnCount;

         

        So the working code should look like: 

        function WriteDataToExcel(fname, sheetName)
{
  let app = Sys.OleObject("Excel.Application");
  let book = app.Workbooks.Open(fname);
  let sheet = book.Sheets.Item(sheetName);
  let rowCount = sheet.UsedRange.Rows.Count;
  let columnCount = sheet.UsedRange.Columns.Count;
  sheet.Cells.Item(rowCount, columnCount+1).Value2 = rowCount+","+columnCount ;
  book.Save();
  app.Quit();
}

        Check out these help topics for other JavaScript specifics:

        JavaScript - Specifics of Usage
        Differences Between JavaScript and JScript