Ask a Question

Saving an Excel file

K_Ali
New Contributor

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 8
tristaanogre
Community Hero

Re: Saving an Excel file

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.


Robert Martin
[Hall of Fame]
Please consider giving a Kudo if I write good stuff
----

Why automate?  I do automated testing because there's only so much a human being can do and remain healthy.  Sleep is a requirement.  So, while people sleep, automation that I create does what I've described above in order to make sure that nothing gets past the final defense of the testing group.
I love good food, good books, good friends, and good fun.

Mysterious Gremlin Master
Vegas Thrill Rider
Extensions available
K_Ali
New Contributor

Re: Saving an Excel file

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

Re: Saving an Excel file

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();
}

Thanks
Shankar R

LinkedIn | CG-VAK Software | Bitbucket | shankarr.75@gmail.com

“You must expect great things from you, before you can do them”

Extension Available

Nidhi_Arora
Occasional Contributor

Re: Saving an Excel file

Hi, 

 

I am using the similar code which you have suggested. However, a new excel file is getting created and saved in 'My Documents' folder instead of  the changes being saved in the same file. Please suggest.

shankar_r
Community Hero

Re: Saving an Excel file

Post your code here, so that it can be checked for issues why are you getting stored in the My documents folder instead of same file


Thanks
Shankar R

LinkedIn | CG-VAK Software | Bitbucket | shankarr.75@gmail.com

“You must expect great things from you, before you can do them”

Extension Available

Nidhi_Arora
Occasional Contributor

Re: Saving an Excel file

I have also tried to use Book.SaveAs(filepath) . It is giving an error -- 'Cannot access read-only document 'dealImports.xlsx'. Please refer the code. The function has few parameters.

 

function updateExcel(fileName, sheetName, arrayStartDate, arrayEndDate) {
var book;
var filepath=Project.Path+fileName;
var app1 = Sys.OleObject("Excel.Application");
app1.DisplayAlerts = false;
book = app1.Workbooks.Open(filepath);
var sheet = book.Sheets(sheetName);
var rowCount = sheet.UsedRange.Rows.Count;
Log.Message('Row Count '+rowCount);
var j=0;
for(var row = 2; row <=rowCount+1; row++) {
Log.Message('Trade Start Date -- '+arrayStartDate[j]+ ', Trade End Date --' +arrayEndDate[j]);
sheet.Cells.Item(row, 7).Value = arrayStartDate[j];
sheet.Cells.Item(row, 8).Value = arrayEndDate[j];
if(row==arrayStartDate.length+1) {
break;
}
j=j+1;
}
book.Save();

app1.Quit();

}

shankar_r
Community Hero

Re: Saving an Excel file

I think below will solve your problem, Please try and let me know the results,

 

function updateExcel(fileName, sheetName, arrayStartDate, arrayEndDate) {
	var book;
	var filepath = Project.Path + fileName;
	var app1 = Sys.OleObject("Excel.Application");
	app1.DisplayAlerts = false;
	var curSecurityValue = app1.AutomationSecurity;
	app1.AutomationSecurity = 3; //refer https://msdn.microsoft.com/en-us/library/aa432468(v=office.12).aspx
book = app1.Workbooks.Open(filepath); var sheet = book.Sheets(sheetName); var rowCount = sheet.UsedRange.Rows.Count; Log.Message('Row Count ' + rowCount); var j = 0; for (var row = 2; row <= rowCount + 1; row++) { Log.Message('Trade Start Date -- ' + arrayStartDate[j] + ', Trade End Date --' + arrayEndDate[j]); sheet.Cells.Item(row, 7).Value = arrayStartDate[j]; sheet.Cells.Item(row, 8).Value = arrayEndDate[j]; if (row == arrayStartDate.length + 1) { break; } j = j + 1; } book.Save(); app1.AutomationSecurity = curSecurityValue; app1.Quit(); }

ref: https://stackoverflow.com/questions/25889742/remove-protected-view-from-excel-sheet-opened-programma...


Thanks
Shankar R

LinkedIn | CG-VAK Software | Bitbucket | shankarr.75@gmail.com

“You must expect great things from you, before you can do them”

Extension Available

Nidhi_Arora
Occasional Contributor

Re: Saving an Excel file

I tried the code. It is still saving the file under 'My Documents' folder with the same file name and it doesn't replace the previously existing file. It saves only if there is no file with the same name under 'my Documents'. 

cancel
Showing results for 
Search instead for 
Did you mean: