Excel : detect last row

Hello, One of our users asked my to fill in a template from within axapta. I have a template with a number of articles in a column and he wants to see the number we sold of each article in the next column. l_sItemId = l_sheet.cells().item(4,1).value().bStr(); j=4; while (l_sItemId != "END") { if (InventTable::find(l_sItemId) != null) { while select l_inventTrans where l_inventTrans.ItemId == l_sItemId { l_qty += l_inventTrans.qty; } l_sheet.cells().item(j,2).value(l_qty); } j++; l_sItemId = l_sheet.cells().item(j,1).value().bStr(); } I can do all of this if he adds the word “END” in the last row. But, I would like to do something like in VBA where last element is found by “Selection.End(xlDown)”. Can I do that with the SysExcelApplication object? thx, Kathleen

Hi Kathleen, You can also do this without the “END” line. Just replace while (l_sItemId != “END”) with while (l_sItemId). In Axapta, the ItemId cannot be empty, so the last row with data will also be the last row to update. Regards, Ciprian

From what I know, Axapta’s Excel api’s do not have such a method. Another way to find the last row, but VERY time consuming, so not really applicable would be to manually check in a loop the content of whole column [:)] SysOperationProgress prg; #Avifiles ; prg = SysOperationProgress::newGeneral (#aviupdate,“Checking”,65536); for (i=1;i<=65536;i++) { l_sItemId = l_sheet.cells().item(i,1).value().bStr(); if (strlrtrim(l_sItemId)!=’’) { lastRow = i;} prg.incCount(); prg.setText(strfmt("%1 of 65536",i)); } With regards, Ciprian