topic Excel Object Failing with 64 bit Office in Product Forum

Excel Object Failing with 64 bit Office

sbass2 — Fri, 19 Jan 2024 16:29:00 GMT

We have used the VB code below to count the number of rows in an excel file for years using Blue Prism and 32 bit office products and its worked reliably. Our company is upgrading to 64bit Office and we are doing regression testing of all Blue Prism processes and now are getting inaccurate results with the Excel Object Action. All other excel actions are working as expected in 64 bit Office, only the Get Number of Rows action is failing. Its not giving an error, its giving the output as 1 even though there is more than 1 row filled out in the spreadsheet. We are on Blue Prism 7.1.2v

In the example file attached, the object is giving 1 row as its output when we would expect 3

Is there another method you suggest that would give the output as the number of rows in a excel file that have data in them that is compatible with 64 bit Office?

rowcount = GetWorksheet(handle,workbookname,worksheetname).Cells.Find("*", , , , , 2).Row

------------------------------
Shannon Berlin
Business Process Specialist
Chesapeake Energy
America/Chicago
------------------------------

RE: Excel Object Failing with 64 bit Office

ThomasHutchins — Mon, 22 Jan 2024 14:06:00 GMT

This is the infamous action with the Blue Prism description "Actually, I've no idea whatsoever what this does."
We recently noticed this action was not always returning the correct value, and replaced the code with the following:

Dim row1 as Long, row2 as Long
row1 = GetWorksheet(handle,workbookname,worksheetname).Cells.Find("*", , , , , 2).Row
row2 = GetWorksheet(handle,workbookname,worksheetname).Cells.Find("*", , , , , 1).Row
If row1 >= row2 Then
rowcount = row1
Else
rowcount = row2
End If

Now it executes a .Find by columns, then again by rows, and returns the highest row number found. It's giving the correct last row used value now.

Hope this helps,

Hutch

------------------------------
Thomas Hutchins
Lead Developer
Discover
America/Chicago
------------------------------

RE: Excel Object Failing with 64 bit Office

ewilson — Tue, 23 Jan 2024 20:16:00 GMT

@sbass2,

To get a count of the used rows, try this:

rowcount = GetWorksheet(handle,workbookname,worksheetname).UsedRange.Rows.Count

Cheers,

------------------------------
Eric Wilson
Director, Integrations and Enablement
Blue Prism Digital Exchange
------------------------------