Excel Utility - Get Number of Rows

[SakshiShivhare]

Hi,

We have recently upgraded the VM's memory from 32 bit to 64 bit and we are facing an issue with the "Get Number of rows" action for Excel Utility. It is not giving the appropriate/Correct output and giving some random numbers as output.  

What can be the solution to get correct number of rows from Excel.

------------------------------
Sakshi Shivhare
Application Development Senior Analyst
Accenture
Pune
------------------------------

[Ian_At_RoyalSurrey]

Hi Sakshi

I first go to cell "A1" and then  "Go to next empty cell down". The output of "Cell Reference" will give the next empty row, so the number is that number minus 1

Hope this helps 

------------------------------
Ian Meldrum
------------------------------

[michaeloneil]

Hi @SakshiShivhare

I had a similar issue with a few excel files previously and it might not be the action is wrong but rather the excel file is information you arent seeing. Most commonly when I've seen this issue is because the file I was using had, at a glance blank cells, but excel was considering that there was data in there. To fix it I selected all blank cells below the data needed and deleted all rows then saved the file. As the file was a template file this fixed the issue and the get rows worked normally again. Other times when I've seen this its usually because someone has left a formula or unused information in a cell that was way down at the bottom or way off to the side so might be worth checking there are no rows/columns with information in there that isnt required or alternatively someone has set some rows to hidden and BP will still count these rows even when hidden so best to check your file doesnt have any hidden rows.

But if you have checked these things and still having the same issue then I think the workaround suggested by @Ian_At_RoyalSurrey is the best solution, although as I remember the get next empty cell action will produce a cell reference including column and rows such as B4256. If you need to set the last row with cell value you need to extract the row reference from this and minus it by one e.g. "B"&4256-1.

Alternatively I did create a custom action previously that is to do a Get last cell with value which should find the last one with a value instead of the next empty cell. Let me know if this is something you could use and I can send of the details of how to create the action.

Hope this helps 🙂

------------------------------
Michael ONeil
Technical Lead developer
NTTData
Europe/London
------------------------------

[rsrivastava2]

Hi Sakshi,

This started with me recently when I upgraded MS Excel VBO to latest, which gives it super power to read even the hidden fields.

That's where everything started going down hill. Well I had to revert the updated VBO but if you have less dependencies, you can develop solutions around it.

Hope this helps!

------------------------------
WeitghtRPAMatey
------------------------------

[SakshiShivhare]

Hi Michael,

Thankyou so much for your response. I have already checked the details you have mentioned and there is no such data or blank rows present.

Can you please share the details of Custom action which you created I think that might help me.

------------------------------
Sakshi Shivhare
Application Development Senior Analyst
Accenture
Pune
------------------------------

[michaeloneil · Accepted solution]

Hi @SakshiShivhare

No problem you need to create a new action with a code stage using the following code:

Dim wb, ws As Object

Dim excel, sheet, range As Object

Try

wb = GetWorkbook(Handle, Workbook)

ws = GetWorksheet(Handle, Workbook, Worksheet)

wb.Activate()

ws.Activate()

excel = ws.Application

sheet = excel.ActiveSheet

range = sheet.Range(StartCell)

range.Select()

range.End(direction).Activate()

Success = True

cellref = excel.ActiveCell.Address(false, false)

Catch e As Exception

Success = False

Message = e.Message

Finally

wb = Nothing

ws = Nothing

excel = Nothing

sheet = Nothing

range = Nothing

End Try

The inputs/outputs are shown in the screenshots below. The screenshot of the object shows the setting of direction enum which are 

DOWN -4121
LEFT -4159
RIGHT -4161
UP   -4162

Hopefully this is clear.

------------------------------
Michael ONeil
Technical Lead developer
NTTData
Europe/London
------------------------------

[SakshiShivhare]

Hi Michael,

Thank you for the details. I put everything as you mentioned but I am getting the error - "Exception from HRESULT: 0x800A03EC". Not sure why. Any idea on it?

------------------------------
Sakshi Shivhare
Application Development Senior Analyst
Accenture
Pune
------------------------------

[michaeloneil]

Can you share some screens etc of your code set up? 

------------------------------
Michael ONeil
Technical Lead developer
NTTData
Europe/London
------------------------------

[SakshiShivhare]

Attaching the screenshots. As of now I set the object with down direction.

------------------------------
Sakshi Shivhare
Application Development Senior Analyst
Accenture
Pune
------------------------------

[michaeloneil]

I dont see any obvious issues in your set up but can you check the direction enum data item is set to a number, also just to be sure its setting it correctly use a calculation to add the enum to the data item. It looks like a range error so im not sure if its maybe best to also check your workbook, startingcell and worksheet name are all correct?

------------------------------
Michael ONeil
Technical Lead developer
NTTData
Europe/London
------------------------------