Forum Discussion

endorium's avatar
endorium
Frequent Contributor
6 years ago
Solved

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

 

  • shankar_r's avatar
    shankar_r
    6 years ago

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

     

     

3 Replies

Sort By
  • shankar_r's avatar
    shankar_r
    Community Hero
    6 years ago

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

     

     

    • TanyaYatskovska's avatar
      TanyaYatskovska
      SmartBear Alumni (Retired)
      6 years ago

      Shankar, Rudolf, thanks for your suggestions!

       

      endorium, did you find the answer to your question?

  • RUDOLF_BOTHMA's avatar
    RUDOLF_BOTHMA
    Community Hero
    6 years ago

    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;