Recently when doing some work in UiPath, I ran into an issue that took me quite a while to work out a proper solution. As part of a bigger project, I was using the indico api to extract tables from a PDF document and push the results into an Excel document. I needed to merge multiple cells in the Excel document and there wasn't a readily apparent way to do it.
A quick search of google and the UiPath forms suggested using the invoke code activity to achieve the cell merge. I struggled with that approach for a while, running into failures for a variety of reasons. I *think* the main culprit being that the Excel Activities package had been update to a point where broke that approach.
Eventually I discovered the Invoke VBA Activity nested under the Excel/Processing Activity group. This activity allows you to execute a vb script in the scope of the current Excel application.
First create a VB script called merge-cells.vb with the following code and add it to your project.
This is pretty straight forward. It takes in a sheet name and a cell range as a string (e.g. A1:B2).
Next, you'll need to adjust some setting in Excel to allow this macro to be run by UiPath. In Excel go to File -> Options -> Trust Center -> Macro Settings -> (Check) Trust Access to the VBA project object model.
In your workflow you'll need to be sure that you've added the UiPath.Excel.Activities package. Then drag an Excel Application Scope activity to your sequence or flow chart, making sure to set the Workbook Path property. Next you'll need to identify the sheet you want to use, and store it in a variable (you'll need this in a minute). I went ahead and added a Write Cell activity to make sure that the sheet is created.
Now we're ready to perform the cell merge. Add an Invoke VBA activity to your workflow (inside the Excel Application Scope). I've also added an assign statement to set a string with the cell range I want to merge. In the properties for the Invoke VBA set:
Save and run your workflow.
After the process has completed, you should see something like this.
This is just a simple example that merges three cells across three rows, but you should be able to see now how you can use Invoke VBA to accomplish a wide range of formatting challenges.
To be honest, there are probably multiple ways this could have been done. With a bit of work, you could probably automate the process in Excel itself with a combination of UiPath's Excel Activities, some screen recording and a bit of logic. That felt a little bit clunky to me though, so I opted for the programmatic approach.