Navigate back to the homepage

Merging Cells in Excel with UiPath

Tori Holmes-Kirk
June 20th, 2019 · 2 min read

Using Invoke VBA to script Excel actions.

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.

1'merge-cells.vb
2Sub mergeCells(sheetName AS string, cellRange AS string)
3 Set sh = Sheets(sheetName)
4 sh.Range(cellRange).Merge
5End 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

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

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

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

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.

More articles from Tori Holmes-Kirk

Welcome

Thank you for visiting my site. Over the coming months I hope to share my experiences and tutorials through this website.

May 5th, 2019 · 1 min read

Five Things I Miss from Building Web Sites in the 90s

The styles, tools, and technology of the early internet are unforgettable. Here are five things I miss from early in my web development career.

April 11th, 2021 · 3 min read
© 2019–2021 Tori Holmes-Kirk
Link to $https://supertorio.medium.com/Link to $https://twitter.com/supertorioLink to $https://github.com/supertorioLink to $https://linkedin.com/in/supertorioLink to $https://supertor.io