endorium
6 years agoFrequent 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();
}
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(); } }