Forum Discussion

K_Ali's avatar
K_Ali
New Contributor
7 years ago

Saving an Excel file

Hey

 

I need help saving an excel file.

 

I am using Jscript.  I have a script that connects to DB and runs a query and copies that result in specified excel sheet.

But when I try to save the excel using “MsExcel.Save();”  Excel give me an alert about another excel file

“A file named ‘RESUME.XLW’ already exists in this location. Do you want to replace it?”

 

The file ‘RESUME.XLW’ has nothing to do with this script. Im not sure why I keep getting this alert? Can someone please help????

8 Replies

Sort By
  • tristaanogre's avatar
    tristaanogre
    Esteemed Contributor
    7 years ago

    Do you have that file (resume.xlw) open in another window while you're running your script?  The reason being is that I think MsExcel.Save() (if MsExcel is an instance of the Excel.Application object) sends the "Save" command to the application in general rather than the specific document you're working on.

    Try closing out all Excel windows you have open and rerun.

     

    Also, it might be helpful for you to post the code or a sample code of what you're doing to write to and save the excel file so we can perhaps debug further.

    • K_Ali's avatar
      K_Ali
      New Contributor
      7 years ago

      I do have few files name (resume.xlw) but none of them are open. 

      the only application that open at the time of run the test is testcomplete & SQL server

       

       

       

      The script I am using, I found it online.

      below is sample of script I used of course with my modification to fit my settings

       

      Function Main()

      {

      var ConnectionString = “DRIVER = SQL Server; SERVER=H_kptest; UID=aaa; PWD=aaa”;

      var TableName = “ListofOrders”;

      var ExcelFilePath = “C:\Book1.xls”;

      var SheetName = “sheet1”;

      ExportTbaleToExcelFile(ConnectionString, TableName, ExcelFilePath, SheetName);

      }

       

      Function ExportTbaleToExcelFile (ConnectionString, TableName, ExcelFilePath, SheetName)

      {

      //Creates a new connection

      var connDB = ADO.CreateConnection();

      connDB.ConnectionString = connectionString;

      connDb.Open();

       

      //opens a record set

      var Tbl = connDB.Execute(“SELECT * FROM” + TableName);

       

      //opens an Excel file

      var MsExcel = Sys.OleObject(“Excel.Application”);

      MsExcel.Workbooks.Open(ExcelFilePath);

      var Sheet = MsExcel.Sheets(SheetName);

      MsExcel.Visible = true;

       

      //copies field Names

      for (var i=0; i< Tbl.Fields.Count; i++)

       Sheet.Cells(1, i+1).value = Tble.Fields.Item(i).Name;

       

      //scans all  records returned by the query

      Tbl.MoveFirst();

      var RowIndex =2;

      while (! Tbl.EOF)

      {

      For (i=0; i < Tbl.Fields.Count; i++)

      Sheet.Cells(RowIndex, i + 1).Value = Tbl.Fields.Item(i).Value;

      Tbl.MoveNext();

      RowIndex++;

      }

       

      //closes the Excel File

      MsExcel.Save();

      MsExcel.Workbooks.Close();

      //Closes the recordset and the connection

      Tbl.Close();

      connDB.Close();

      }

      • shankar_r's avatar
        shankar_r
        Community Hero
        7 years ago

        Hi,

         

        We use OleObject in-order to write the excel file.

         

        function WriteExcelFile(Value)
{
      var Excel,row, column, x,sheetObj,wrkbookObj;
      x=Record+1;
      Excel = Sys.OleObject("Excel.Application");
  
      wrkbookObj = Excel.Workbooks.Open("C:\\Users\\...DATA TABLE IDs.xls");
      
      sheetObj = wrkbookObj.Sheets.Item("Sheet1");
      sheetObj.Cells.Item(x,1).Value=Value;
      
      wrkbookObj.Save();
      Excel.Quit();
}