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 mentioned there.

Can you help me how to write to Excel 2010.

Thanks

 

  • HKosova's avatar
    HKosova
    SmartBear Alumni (Retired)

    Hi Yaacov,

     

    That example works fine with Excel 2010. Did you try it? Note that the example writes to an existing Excel file; it does not create a new file.

    • hautzenroeder1's avatar
      hautzenroeder1
      Occasional Contributor

      I use this to create a new Excel workbook and then write data to it:

       

        Set Excel = Sys.OleObject("Excel.Application")
        Excel.Workbooks.Add
        Excel.Visible = "True"

        Excel.ActiveSheet.Name = ("Test")  '<-- use to change name of active worksheet if desired

      '.

      '.

      '.

      'code here

      ' Write data to speadsheet with:

        Excel.Cells(1,1) = "Test"

      '.

      '.

      '.

      ' Saving Excel file

       

        Excel.Application.DisplayAlerts = False     '<-- suppresses any dialog boxes
        Excel.Application.AlertBeforeOverwriting = False   ' <-- suppresses any warniing about overwriting existing file

        Excel.Application.ActiveWorkbook.SaveAs("D:\Test.xlsx")   ' <-- Set save path and file name
        Excel.Application.ActiveWorkbook.Close
        Excel.Quit

       

      Path can be predefined such as:

       

        Path = "D:\Test_File\Test.xlsx"

       

        Excel.Application.ActiveWorkbook.SaveAs(Path)

       

      Notes: The path must exist otherwise the script fails.

       

    • 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

        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"