Retrieving specific value from excel

[endorium]

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

[RUDOLF_BOTHMA]

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;

[shankar_r · Accepted solution]

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]

Shankar, Rudolf, thanks for your suggestions!

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