MS Excel VBO - Excel with Multiple Lines in Header Cells

[OliverMohajeri]

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

[MadhurChopra]

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

[OliverMohajeri]

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

[NicholasZejdlik]

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