Blue Prism Product

 View Only
last person joined: 9 hours ago 

This community covers the core Blue Prism RPA product.

Expand all | Collapse all

Excel Utility - Get Number of Rows

  • 1.  Excel Utility - Get Number of Rows

    Posted 11-29-2023 07:42

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



  • 2.  RE: Excel Utility - Get Number of Rows

    Posted 11-29-2023 13:24

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



  • 3.  RE: Excel Utility - Get Number of Rows

    Posted 12-01-2023 13:18

    Hi @Sakshi Shivhare

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



  • 4.  RE: Excel Utility - Get Number of Rows

    Posted 12-04-2023 08:39

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



  • 5.  RE: Excel Utility - Get Number of Rows
    Best Answer

    Posted 12-04-2023 09:30

    Hi @Sakshi Shivhare

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



  • 6.  RE: Excel Utility - Get Number of Rows

    Posted 12-04-2023 14:57

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



  • 7.  RE: Excel Utility - Get Number of Rows

    Posted 12-04-2023 15:47

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



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



  • 8.  RE: Excel Utility - Get Number of Rows

    Posted 12-04-2023 16:26

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



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



  • 9.  RE: Excel Utility - Get Number of Rows

    Posted 12-05-2023 09:27

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



  • 10.  RE: Excel Utility - Get Number of Rows

    Posted 12-05-2023 12:51

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



  • 11.  RE: Excel Utility - Get Number of Rows

    Posted 12-05-2023 14:55

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



  • 12.  RE: Excel Utility - Get Number of Rows

    Posted 12-02-2023 12:24

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



  • 13.  RE: Excel Utility - Get Number of Rows

    Posted 12-05-2023 15:45

    Can you share how to call for get row number



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



  • 14.  RE: Excel Utility - Get Number of Rows

    Posted 12-06-2023 10:06

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



Welcome to the Blue Prism RPA Product Community!

Whether you’re looking to manage a complex infrastructure, maintain security and compliance, bring new products to market faster, or gain operational speed and agility in an uncertain economy, Blue Prism delivers — with the flexibility you need to create the business you want. From deployment on-premise, through a cloud service provider or as SaaS, to a skillful and adaptable digital workforce that continually expands to meet your enterprise needs, you can gain enhanced operational insight and control while your people reclaim the time they need to focus on great work.

Product PageKnowledge BaseBlue Prism Training Offering
Product Research Programv6 End of Life

FAQs

Blue Prism is intelligent automation — business-developed, no-code automation that pushes the boundaries of robotic process automation (RPA) to deliver value across any business process in a connected enterprise.

A combination of RPA with expanded cognitive and AI capabilities, Blue Prism is different than other automation technology on the market. With one Blue Prism license, you gain instant access to an already AI equipped digital workforce, along with the tools you need to build and delegate automations. Click here for more information on Blue Prism and Intelligent Automation.
To learn more about how Blue Prism RPA can help your organization and how much it will cost to get started, please Contact our Sales department.
Blue Prism RPA can be downloaded from our customer portal. If you would like to consume or download any material it is necessary to create an account on the Portal. Once you have registered, you can access the download options for Blue Prism here.
Yes! Installed on your own machine and supported by our training materials and product documentation, you can use all the features of the full enterprise product for free with our Blue Prism Trial – giving you the opportunity to learn the basics before moving to a full production implementation. Click here for more information and to download the trial.
Yes! You can access our known issue list for Blue Prism from our Support Portal.
Regardless of your industry, Blue Prism’s Digital Workforce can adhere to strict governance and compliance standards without limiting productivity. Click here for more information on how your industry can benefit from Blue Prism.