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 ex...
Forum Discussion
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.
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();
}
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(); }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.
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
