HOWTO: Use MS Office COM interop with UiPath

[oddrationale (Dariel Dato-on)]

HOWTO: Use MS Office COM interop with UiPath

This tutorial is a step-by-step guide on how to use MS Office COM interop with UiPath.

There are many questions on the forums on how to use the Microsoft Office COM interop with UiPath. People were looking for ways to use the Invoke Code activity along with the Microsoft.Office.Interop.Excel or Microsoft.Office.Interop.Outlook assemblies. Some people have been able to get it to work, but others have not been able to get it to work even when following their suggestions.

I’ll show step-by-step what you need to do and we will also discover a weird UiPath bug along the way! I have tested these steps with UiPath Studio v2018.3.1.

Step-by-step

Step 1: Create a new workflow.

Go to Design → New → Sequence to create a new sequence. Within the sequence, put the Invoke Code activity.

Step 2 (optional): Import the namespace.

Contrary to many of the other forum posts, importing the Microsoft.Office.Interop namespace is not actually required. Importing the namespace is helpful so you don’t have to type out the full name of the objects or properties within the namespace each time you use it. For example, the following code snippets within the Invoke Code activity would all be equivalent:

Example 1: No namespace imported

Dim ExcelApp As Microsoft.Office.Interop.Excel.Application
ExcelApp = New Microsoft.Office.Interop.Excel.Application

Example 2: Microsoft.Office.Interop.Excel namespace imported

Dim ExcelApp As Application
ExcelApp = New Application

Example 3: Microsoft.Office.Interop namespace imported

Dim ExcelApp As Excel.Application
ExcelApp = New Excel.Application

It all depends on your coding preference. And also, you want to avoid namespace collisions where multiple namespaces may have the same object or property names. After you import a namespace, you will have to save and close the workflow file. The new namespace will take effect when you reopen the workflow file. Be aware, if you add a namespace, the only way to modify or remove the import is to manually edit the .xaml.

For this tutorial, I’ll assume that you have not imported any namespaces.

Step 3: Load the assembly reference

To load the assembly reference, you will have to manually modify the .xaml workflow file.

NOTE: Modifying the xaml files directly is not supported or recommended by UiPath.

However, modifying the .xaml file is the only way (at this time) to load the assembly references. To do so, close the workflow file in UiPath Studio. Then open the .xaml file for your workflow using a text editor such as Notepad or Notepad++. Somewhere typically around line 40, you will see all the assembly references. Add the assembly reference here.

For example, if you are trying to use the Excel interop, then add <AssemblyReference>Microsoft.Office.Interop.Excel</AssemblyReference>. When you are done it should look something like:

...
      <AssemblyReference>System.ValueTuple</AssemblyReference>
      <AssemblyReference>UiPath.Studio.Plugin.Workflow</AssemblyReference>
      <AssemblyReference>Microsoft.Office.Interop.Excel</AssemblyReference>
...

You can substitute Excel for Outlook, Word, or any of the other supported COM Interops. See the References below.

Save the file and reopen the .xaml file in UiPath Studio.

Step 4: Write your code

Write your code in the Invoke Code activity. See the References below for more information on using the MS Office COM interop objects.

Step 5: Watch out for weird UiPath bug

Sometimes, even after you complete the steps above, you will still get this error:

Source: Invoke Code

Message: No compiled code to run
error BC31539: Cannot find the interop type that matches the embedded type 'Microsoft.Office.Interop.Excel.Application'. Are you missing an assembly reference? At line 1
error BC31539: Cannot find the interop type that matches the embedded type 'Microsoft.Office.Interop.Excel.Workbook'. Are you missing an assembly reference? At line 2
error BC31539: Cannot find the interop type that matches the embedded type 'Microsoft.Office.Interop.Excel.Application'. Are you missing an assembly reference? At line 4


Exception Type: System.ArgumentException

There is a weird bug in UiPath where you need to have some sort of assign activity in order for the code to compile. For example, the following workflow will fail with the above error:

But if you add an assign statement, it will work:

So you just need to make sure there is some sort of assignment activity anywhere on your workflow. It does not have to be the Assign activity. But can be any activity where the output is assigned to a variable.

I do not know why this happens, but through much trial and error, I was able to narrow it down to this issue and it is reproducible on v2018.3.1. If any developers from UiPath have any idea on why this is case, I would be happy to know!

References

• General info on MS Office COM Interop: Office primary interop assemblies - Visual Studio | Microsoft Learn
• Excel Interop Reference: Microsoft.Office.Interop.Excel Namespace | Microsoft Learn
• Outlook Interop Reference: Microsoft.Office.Interop.Outlook Namespace | Microsoft Learn
• Word Interop Reference: Microsoft.Office.Interop.Word Namespace | Microsoft Learn

[MattTheList]

Awesome tutorial and discovery!

[J0ska]

Excellent! Thx

[sreenivasm]

@oddrationale - This is an excellent post! You also gave the work around for the UiPath bug and that makes this post more useful!

[NHL]

Thanks, man! I’ve learned a lot from this! Great stuff!

[Toby (Toby Tang)]

Great tutorial! It’s very helpful for me.

[StefanSchnell]

Thanks for sharing this information @oddrationale

[Gluon (Gaston Tan)]

Thanks for this information. Good job.

[Dane_whirl]

You are a genius . you made my day… Thanks a lot

[jcgonzalez (Juan Carlos)]

@oddrationale

why this happens, but through much trial and error, I was able to narrow it down to this issue and it is reproducible on v2018.3.1. If any developers fr

Very nice! Thanks a lot!!

[pattyricarte (Patty Boy)]

@oddrationale

Nice topic i will bookmark it and share to our community once needed.

cheers

Happy learning

[Java]

Has anyone been able to do this with Microsoft.Office.Interop.PowerPoint?
I can’t find this namespace, even modding assembly references.

[RockSolid (Martí Pomés)]

Hi Java,

You have to install the package for interop power point:

It doesn’t come as default

[Shelby_Pons]

Hi RockSolid,

I’m having an issue with PowerPoint, despite having installed that package. If you have a moment to review, I’d really appreciate any thoughts on a solution to my issue.

PowerPoint COM Interop

Thank you!
Shelby

[Olivera_Kalinic (Olivera Kalinić)]

@oddrationale

n

HI!It’s very helpful post, but when I tried to compile code I’ve been imported using steps you described above, I got this erro " Aplication isambiguous in the namespace…" . What do you think this could be?

[AndyMenon]

Hi @oddrationale ,

Thank you very much for sharing this information. However, I feel somehow that the UiPath IDE does have it quirks when it comes to handling assemblies such as the Office Interop Excel.

I don’t know where it is I am going wrong, but in my case I did not have to modify the .XAML file to manually add the InterOp reference. It was already in there. That is likely because I used the Imports feature to pull the Interop namespace in my Studio environment.

In addition the Excel Interop package has been installed within the project from package manager.

Now here is my issue. I have built an Excel Library in UiPath but I am finding that the code is not working reliably for some portions of the functionality. For example, I am able to Delete Sheets from a Workbook, but if I run this code (below), there is a design time error popping up all the time.

You are absolutely correct in stating that we don’t need to use the Imports feature if we are fully referencing the Interop objects. Nevertheless, here is the code I am trying to execute, and below that are error messages at Design time, and then at Run time.

My humble opinion - there must be an easier way to make this work in UiPath because it isn’t practical all the time to perform Hotkey operations on Excel. I have a requirement where a heavily formatted Worksheet has to be copied several times, but the user is concerned about Hotkey operations interfering with user activity in Attended mode. And I don’t know how it would work if the automation is Unattended.

' Input(s): 
'vb_Target_SheetNm - Name of the cloned  Worksheet 
'vb_ExcelPath - The  full path of the Excel Workbook

' Create Excel Application and Workbook references
Dim ExcelAppClass As Microsoft.Office.Interop.Excel.Application
Dim ExcelWorkBook As Microsoft.Office.Interop.Excel.Workbook

ExcelAppClass = New Microsoft.Office.Interop.Excel.ApplicationClass
ExcelWorkBook = ExcelAppClass.Workbooks.Open(vb_ExcelPath)

'#1 Create the empty (clone) worksheet
ExcelWorkBook.Worksheets.Add(vb_Target_SheetNm)

'#2 Fetch the source worksheet -currently assumed to be the first sheet
'DESIGN TIME NAG: If I remove CType, I get the Design-time error below  
Dim ExcelWorkSheet As Microsoft.Office.Interop.Excel.Worksheet = CType(ExcelWorkBook.Worksheets(0),Microsoft.Office.Interop.Excel.Worksheet)

'#3 Copy the source worksheet into the empty cloned worksheet
' DESIGN TIME NAG: Will not compile and shows the design-time error below  
ExcelWorkBook.Worksheets(1).CopyFrom(ExcelWorkSheet)
'# RUNTIME NAG: Will compile but fails at run time as shown below  :thinking:  
'ExcelWorkSheet.Copy()

' Save and Close all references
ExcelWorkBook.Save()
ExcelWorkBook.Close()
ExcelAppClass.Quit()

' Clean up and release all references
ExcelWorkSheet = Nothing
ExcelWorkBook = Nothing
ExcelAppClass = Nothing 

Design Time Nag:

Run Time Nag:

[matthew_young]

Adding to this in case it helps anyone else.

The above had many pieces of the puzzle, but did not fully work for me. My use-case was to simply open a semi-populated email for an Attended user.

After banging my head over constant late-binding and not defined errors, the following workflow worked for me:

1. Add the Microsoft.Office.Interop.Outlook namespace via “Manage Packages” instead of adding it via Imports:
   

   

2. Creating the Application and MailItem variables as such:

> Dim app As Microsoft.Office.Interop.Outlook.Application = New Microsoft.Office.Interop.Outlook.Application()
> Dim newMail As Microsoft.Office.Interop.Outlook.MailItem = CType(app.CreateItem(Microsoft.Office.Interop.Outlook.OlItemType.olMailItem), Microsoft.Office.Interop.Outlook.MailItem)
> 
> newMail.Subject = Arg_Subject
> newMail.HTMLBody = Arg_Body
> newMail.Display

[hannigton.kisambo]

Error opening workbook. If Excel is installed, run the ‘Repair Tool for Microsoft Office Interop’ from the Tools tab on the Home screen.

THIS MESSAGE IS WHAT I GET WHEN I AM TRYING TO DO MY EXCEL WORKFLOW CAN ANYONE HELP WHAT AM I SUPPOSED TO DO