topic MS Excel VBO - Excel with Multiple Lines in Header Cells in Product Forum

MS Excel VBO - Excel with Multiple Lines in Header Cells

OliverMohajeri — Thu, 24 Sep 2020 16:03:00 GMT

Hi all,

I am trying to interact with an Excel file that has multiple lines of data in the header cells.

Blue prism Excel VBOs seem to struggle with this. Do you know a way around this? I cannot edit the Excel template unfortunately.

My first though is using OLEDB instead of Excel VBOs, but this will require a lot of logic adjusting. Has anyone come across anything similar before and has a code stage or something to use?

Thanks!

Oliver

------------------------------
Oliver Mohajeri
Consultant
EY
Europe/Zurich
------------------------------

RE: MS Excel VBO - Excel with Multiple Lines in Header Cells

MadhurChopra — Fri, 25 Sep 2020 00:27:00 GMT

Hey @OliverMohajeri,

Can you please provide more context on the issue? Do you mean a single cell contains data with line feeds? Also, what do you mean by "header" cells?

Regards.

------------------------------
Madhur Chopra
Sr. Product Consultant
Blue Prism - Professional Services
America/Los_Angeles
------------------------------

RE: MS Excel VBO - Excel with Multiple Lines in Header Cells

OliverMohajeri — Fri, 25 Sep 2020 07:10:00 GMT

Please find attached an excel with the issue I am describing.

Excel Table headers have the text split over multiple lines, but as BP collection fields can only have a single line of text, it seems to be throwing an error.

------------------------------
Oliver Mohajeri
Consultant
EY
Europe/Zurich
------------------------------

RE: MS Excel VBO - Excel with Multiple Lines in Header Cells

NicholasZejdlik — Fri, 25 Sep 2020 13:36:00 GMT

I was able to load in the Excel sheet without error via the Get Worksheet As Collection action, albeit the collection fields for Test Header 3 and 4 can't be referred to directly because of the newline issue. However, you can use the Collection Manipulation object to rename the field to something that can be referenced.

You could also scan the header row before you load it in as a collection using the Get Cell Value / Set Cell Value actions and replace the offending characters.

------------------------------
Nicholas Zejdlik
RPA Developer
------------------------------