topic RE: How to use RPA to format Excel doc? in Product Forum

How to use RPA to format Excel doc?

cvmac — Tue, 16 Jul 2019 07:17:00 GMT

Hello,

I want to use RPA to format an Excel doc - set bottom borders, fill cells with colour, etc.

I cannot use a template for this as different cells will need to have borders set and colours filled each time.

Can anyone advise how to go about this?

Thanks,

------------------------------
Chloe MacIntosh
Automation Developer
Zurich Insurance
Europe/London
------------------------------

RE: How to use RPA to format Excel doc?

GopalBhaire — Tue, 16 Jul 2019 10:17:00 GMT

You'll have to edit the Excel VBO to add the mentioned features.

------------------------------
Gopal Bhaire
Analyst
Accenture
------------------------------

RE: How to use RPA to format Excel doc?

cvmac — Tue, 16 Jul 2019 11:23:00 GMT

Yeah I got that far - I am looking for guidance on building the said features into a code stage.

------------------------------
Chloe MacIntosh
Automation Developer
Zurich Insurance
Europe/London
------------------------------

RE: How to use RPA to format Excel doc?

tjunicho — Tue, 16 Jul 2019 16:05:00 GMT

Hi,

Here's the Extending the MS Excel VBO: https://portal.blueprism.com/documents/standard?title=extending+the+ms+excel&category=All&lang=1382

Hope this helps.

------------------------------
Tetsuji Junicho
Manager of RPA Practice Grp.
Accenture Japan
Asia/Tokyo
------------------------------

RE: How to use RPA to format Excel doc?

RoarNilsen — Thu, 25 Jul 2019 10:46:00 GMT

Here is an alternative solution if you are able to run Excel macros.
* Run Excel macro from Blue Prism.
* Use VBA in the macro to edit the layout.

Split the VBA code into 2 subs
UpdateExcel - Macro/sub called from Blue Prism Action with Run Macro from MS Excel VBO
ChangeExcelLayout - The actual code that changes the layout. Do the layout changes while recording a macro to get some of the VBA code.

Sub UpdateExcel
' Called from Blue Prism Action with Run Macro from MS Excel VBO

   'Turn off error handling to let Blue Prism continue in case of an error
    On Error Resume Next
    
	ThisWorkbook.Worksheets(cMyWorksheet).Activate
    Call ChangeExcelLayout
  	
   'resume Error handling
   On Error GoTo 0
   
End Sub

------------------------------
Roar Nilsen
Manager
Duit AS
Europe/Oslo
------------------------------