Saving an Excel file

[K_Ali]

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????

[tristaanogre]

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]

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]

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

[Nidhi_Arora]

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]

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

[Nidhi_Arora]

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]

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

[Nidhi_Arora]

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'.