Hello guys, i have been struggling with automating some basic tasks in Excel and i have finally found a way how to do it relatively simply. We are still running 2016.2 and dont have an option to invoke a code directly in studio, in case you have similar problem you can find some examples in the attachment.
ExcelExample.zip (19.5 KB)
The issue was that Excel’s namespace contained only few members that were used in other activities. I have created a dummy custom activity which imports namespace with all the members and thus you can use all the properties (usedrange, format…) and most of the methods (clear cells, clear contents, copy, pastespecial…). There are some issues with multiple imports of this namespace (UiPath.Activities, UiPath.Excel) but still i find it really helpfull. Once you have the namespace imported it can run on any other machine with UiPath Studio as the reference to assembly is stored directly in xaml file.
All you have to do is install custom activity, drag and drop it to your workflow and then you can remove it. Once you do it you will see Microsoft.Office.Interop.Excel (Excel 2013) with all the members.
which can be used in your workflow.
Unfortunately i was not able to convert objects directly in Assign activity and i had to use Convert activity from Micosoft.Activities.Expressions which can be used in most of the cases.
Another issue i faced was using Invoke Method activity, which was not able to find public methods of some objects. Thankfully a lot of methods return some value and thus can be used directly in Assign activity, where you can use public method even if it is not listed right away.
Also Intellisense does not work for Workbook/Worksheet Interfaces, but this can be handled by changing the type to WorkbookClass/WorksheetClass while developing and then change it back before running the code.
Hope this will save you some time