topic RE: Excel Utility - Get Number of Rows in Product Forum

Excel Utility - Get Number of Rows

SakshiShivhare — Wed, 29 Nov 2023 07:42:00 GMT

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
------------------------------

RE: Excel Utility - Get Number of Rows

Ian_At_RoyalSurrey — Wed, 29 Nov 2023 13:24:00 GMT

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
------------------------------

RE: Excel Utility - Get Number of Rows

michaeloneil — Fri, 01 Dec 2023 13:18:00 GMT

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
------------------------------

RE: Excel Utility - Get Number of Rows

rsrivastava2 — Sat, 02 Dec 2023 12:24:00 GMT

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
------------------------------

RE: Excel Utility - Get Number of Rows

SakshiShivhare — Mon, 04 Dec 2023 08:39:00 GMT

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
------------------------------

RE: Excel Utility - Get Number of Rows

michaeloneil — Mon, 04 Dec 2023 09:30:00 GMT

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
------------------------------

RE: Excel Utility - Get Number of Rows

SakshiShivhare — Mon, 04 Dec 2023 14:57:00 GMT

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
------------------------------

RE: Excel Utility - Get Number of Rows

michaeloneil — Mon, 04 Dec 2023 15:47:00 GMT

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

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

RE: Excel Utility - Get Number of Rows

SakshiShivhare — Mon, 04 Dec 2023 16:26:00 GMT

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

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

RE: Excel Utility - Get Number of Rows

michaeloneil — Tue, 05 Dec 2023 09:27:00 GMT

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
------------------------------

RE: Excel Utility - Get Number of Rows

SakshiShivhare — Tue, 05 Dec 2023 12:51:00 GMT

I have created the data item to set direction enum and it worked. Thank you so much for your response and solution.

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

RE: Excel Utility - Get Number of Rows

michaeloneil — Tue, 05 Dec 2023 14:55:00 GMT

Good to know Sakshi Im glad its working for you now and hopefully it resolves the issue you were having.

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

RE: Excel Utility - Get Number of Rows

AnkitShukla11 — Tue, 05 Dec 2023 15:45:00 GMT

Can you share how to call for get row number

------------------------------
Ankit Shukla
RPA Developer
Arvius Software Pvt Ltd.
Noida
------------------------------

RE: Excel Utility - Get Number of Rows

michaeloneil — Wed, 06 Dec 2023 10:06:00 GMT

Hi @Ankit Shukla

The action is in the object MS Excel - Extended and the action is called Get number of rows.

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