Automation with Microsoft Office

Solutions for automating Microsoft Office

Here, I will list the solutions for automating Microsoft Office from latest and most general to oldest an most limited:

Power Automate

Since Windows 10, Office 365. Integrated into Windows 11. GUI to create automated workflows across multiple Microsoft applications.

Office-Add-Ins (Apps for Office)

Since Office 2013. platform independent, uses less storage capacity, uses web technologies, installation from Office Store or internal corporate app store, managed distribution

Visual Studio Tools for Office (VSTO)

Since Office 2007: VSTO-Add-ins (Managed add-ins built with Visual Studio Tools for Office): Use Microsoft Visual Studio to create COM-Add-Ins. More integration with Office compared to Office-Add-Ins.

Since Visual Studio 2013: Document-level customizations. Use Microsoft Visual Studio to create Office Projects with document-level customizations. These workbooks are documents with linked assembly (they have managed code extensions).

COM-Add-Ins

Full support for “Visual Basic on .NET-Framework” (no further development) and C#. Commmunicate with the “COM components” of the Microsoft Office application.

XLAM Add-Ins

Until Excel 2003. Least secure.

Office Scripts

Since Office 365. Compatible with Office online and Power Automate. Higher security but more limited functionality compared to VBA.

Visual Basic for Applications (VBA) for writing macros

Based on classic Visual Basic 6 (not Visual Basic on .NET-Framework, not Visual Basic on .NET core). Difficult to use with Office online. A tutorial can be found at

https://learn.microsoft.com/de-de/office/vba/library-reference/concepts/getting-started-with-vba-in-office

Other useful resources:

DERANDERE