Solved
Please Help!! Excel problem.
The 2 functions provided below will do the following:
1) Read from an Excel file.
2) Write to an Excel file.
3) But it does not read and write to the 'same' excel file. It will only read from one Excel file, and write to another Excel file.
4) The problem is my readFromXL() function is not closing the already open excel file properly.
Code for reading from Excel:
function readFromXL(fileName, sheetName, row, col){
try{
var Driver;
// Creates the driver
// If you connect to an Excel 2007 sheet, use the following method call:
Driver = DDT.ExcelDriver(fileName, sheetName, true);
//Driver = DDT.ExcelDriver(fileName, sheetName);
Driver.First();
var i;
for (i=2; i<row; i++){
Driver.Next();
}
return aqConvert.VarToStr(DDT.CurrentDriver.Value(col));
DDT.CloseDriver(Driver.Name);
}catch(e){
Log.Message(e.description);
}
}
Code for writing to Excel
function writeToXL(fileName, sheetName, row, col, data)
{
try{
var app = Sys.OleObject("Excel.Application");
var book = app.Workbooks.Open(fileName);
var sheet = book.Sheets(sheetName);
app.DisplayAlerts = false;
sheet.Cells(row, col) = aqConvert.VarToStr(data);
book.Save();
book.Close();
app.Quit();
Log.Message("Following data was added to " +row +"," +col + " cell : "+ data)
}catch(e){
Log.Message(e.description);
}
}
1) Read from an Excel file.
2) Write to an Excel file.
3) But it does not read and write to the 'same' excel file. It will only read from one Excel file, and write to another Excel file.
4) The problem is my readFromXL() function is not closing the already open excel file properly.
Code for reading from Excel:
function readFromXL(fileName, sheetName, row, col){
try{
var Driver;
// Creates the driver
// If you connect to an Excel 2007 sheet, use the following method call:
Driver = DDT.ExcelDriver(fileName, sheetName, true);
//Driver = DDT.ExcelDriver(fileName, sheetName);
Driver.First();
var i;
for (i=2; i<row; i++){
Driver.Next();
}
return aqConvert.VarToStr(DDT.CurrentDriver.Value(col));
DDT.CloseDriver(Driver.Name);
}catch(e){
Log.Message(e.description);
}
}
Code for writing to Excel
function writeToXL(fileName, sheetName, row, col, data)
{
try{
var app = Sys.OleObject("Excel.Application");
var book = app.Workbooks.Open(fileName);
var sheet = book.Sheets(sheetName);
app.DisplayAlerts = false;
sheet.Cells(row, col) = aqConvert.VarToStr(data);
book.Save();
book.Close();
app.Quit();
Log.Message("Following data was added to " +row +"," +col + " cell : "+ data)
}catch(e){
Log.Message(e.description);
}
}
- //Code for reading from Excel:
function readFromXL(fileName, sheetName, row, col){
try{
var Driver;
// Creates the driver
// If you connect to an Excel 2007 sheet, use the following method call:
Driver = DDT.ExcelDriver(fileName, sheetName,true);
//Driver = DDT.ExcelDriver(fileName, sheetName);
Driver.First();
var i;
for (i=2; i<row; i++){
Driver.Next();
}
tmpCol= aqConvert.VarToStr(DDT.CurrentDriver.Value(col));
DDT.CloseDriver(Driver.Name);
return tmpCol
}catch(e){
Log.Message(e.description);
}
}
