cancel
Showing results for 
Search instead for 
Did you mean: 

Retrieving specific value from excel

SOLVED
endorium
Frequent Contributor

Retrieving specific value from excel

I have a function where I am trying to get the last value from the data in column 'A' in an excel spreadsheet.

Still fairly new to this. Below it was i have but I cannot get it to work. I know It will be full of basic mistakes but any help is appreciated. So 'Version' is the sheet the data is on and its the data on Column A. Only the last row on column A is needed.

 

 

function datareturn ()
{
var Excel = Sys.OleObject("Excel.Application");
Excel.Workbooks.Open("C:\\trial.xlsx");
var RowCount = Excel.Version.UsedRange.Rows.Count;
var value = Excel.ActiveSheet.Cells("A", RowCount)
Log.Message(value);

Excel.Quit();
}

 

1 ACCEPTED SOLUTION

Accepted Solutions
shankar_r
Community Hero

Re: Retrieving specific value from excel

Below code will retrieve you the last row 's column A value.

 

 

function testas() {
    var strFileName = "D:\\Users\\sramasamy\\Desktop\\mass.xlsx";
    var sName = "Version";

    exObj = Sys.OleObject("Excel.Application");
    if (exObj == null) return;
    try {
        exObj.Workbooks.Open(strFileName);
        var sheetObj = exObj.Sheets(sName);
        var rCount = sheetObj.UsedRange.Rows.Count;
        Log.Message(sheetObj.Range("A" + rCount));
    } catch (ex) {
        Log.Error(ex.stack);
    } finally {
        if (exObj != null) exObj.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

View solution in original post

3 REPLIES 3
RUDOLF_BOTHMA
Community Hero

Re: Retrieving specific value from excel

Edit:  Found the link from the support site:

DDT driver usage

or 

Working with COM

TestComplete has properties that can tell you the number of rows and columns of these objects. you may need to search for DDT drivers in the TestComplete help files to find it.  Not sure where it is on the Smartbear support site off the top of my head.  COM example:

 

      ExcelObj = Sys.OleObject("Excel.Application");
      if(ExcelObj==null)
        return false;
      ExcelObj.Workbooks.Open(workbookname);
var sheetObject = ExcelObj.Workbooks(1).Sheets(sheetName);
      }
      
      var RowCount = sheetObject.UsedRange.Rows.Count;
      var ColumnCount = sheetObject.UsedRange.Columns.Count;

 


-------------------------------------------------
Standard syntax disclaimers apply
Regards,
shankar_r
Community Hero

Re: Retrieving specific value from excel

Below code will retrieve you the last row 's column A value.

 

 

function testas() {
    var strFileName = "D:\\Users\\sramasamy\\Desktop\\mass.xlsx";
    var sName = "Version";

    exObj = Sys.OleObject("Excel.Application");
    if (exObj == null) return;
    try {
        exObj.Workbooks.Open(strFileName);
        var sheetObj = exObj.Sheets(sName);
        var rCount = sheetObj.UsedRange.Rows.Count;
        Log.Message(sheetObj.Range("A" + rCount));
    } catch (ex) {
        Log.Error(ex.stack);
    } finally {
        if (exObj != null) exObj.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

View solution in original post

TanyaYatskovska
Community Manager

Re: Retrieving specific value from excel

Shankar, Rudolf, thanks for your suggestions!

 

@endorium, did you find the answer to your question?

---------
Tanya Yatskovskaya
SmartBear Community and Education Manager

Did my reply answer your question? Give Kudos or Accept it as a Solution to help others. ⬇️⬇️⬇️
New Here?
Join us and watch the welcome video:
Announcements
Top Kudoed Authors