PhysiologyWeb Logo  Search
PhysiologyWeb Loading...

Automation of Microsoft Products with Delphi
Among the many useful features of Microsoft products is that they can be automated (i.e., controlled externally) by an external program by using their exposed objects, methods, properties, and events. That is to say, an external program can automatically open an instance of a Microsoft product (such as Excel) and do just about anything a user can do manually by personally interacting with the software. Therefore, most Microsoft products can be controlled programmatically by external applications.
In our own work, we use Microsoft Outlook, Word, Excel, and PowerPoint every day, all day. Often, repetitive tasks get in the way of our productivity and sanity, and are best accomplished by running well-designed code to take control of an instance of these Microsoft products to perform well-defined tasks. For example, we have reports that require quarterly updates with new numbers, dates, projections, and references. Rather than have a staff member manually update these reports, data runs are scheduled as needed and outputs are automatically added to template Excel and/or Word files, date-stamped, and saved (in native or pdf format) with a new file name. In some cases, we combine automation in Excel/Word with Outlook to email the newly-generated file(s) to appropriate recipient(s). Although, more and more, we post the files to secured shared folders.
While for dynamic and organization-wide reporting, it is best to develop appropriate data and/or visualization dashboards (using Tableau, Microsoft Power BI, IBM Cognos, etc.), for internal office use, limited distribution, and/or confidential reports, it is easier and faster, and less prone to human error, to use automation of Microsoft products.
Here, we have collected our most commonly used lines of Delphi code for getting Excel, Word, PowerPoint, and Outlook to do repetitive tasks for us. They work beautifully! The key requirement is that you have these Microsoft Office products already installed on your computer. We test our code with Delphi 6, Delphi XE, and Delphi 10.1 Berlin. Our code is for the Windows operating environment. We have not, and do not plan to, adapt the code for other operating environments. The code works well with Office 365 versions of Excel, Word, PowerPoint, and Outlook, however, some of our code has been in use since we were using Office 2010 (and continues to work well).

Posted: Saturday, December 5, 2020