Merging Cells in Excel with UiPath

Using Invoke VBA to script Excel actions.

Posted by Tori Holmes-Kirk on June 20, 2019 · 4 mins read

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.

My Solution

First create a VB script called merge-cells.vb with the following code and add it to your project.

Sub mergeCells(sheetName AS string, cellRange AS string)
    Set sh = Sheets(sheetName)
End Sub

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.

Excel Trust Settings
Excel Trust Settings

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.

Workflow Step 1
Workflow Step 1

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:

  • CodeFilePath to "merge-cells.vb"
  • EntryMethodName to "mergeCells"
  • EntryMethodParameters to {sheetname, cellsToMerge}
Note that the entry method parameters match up with the arguments for the function you are calling in your vb script.

Save and run your workflow.

Workflow Step 2
Workflow Step 2

After the process has completed, you should see something like this.

Excel Results
Excel Results

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.

Other Solutions?

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.