Microsoft Excel Automation with Delphi

Here is a collection of Delphi lines of code that we use routinely to automate Excel. These have helped us tremendously with streamlining repetitive tasks. The key requirement for using these code lines is that you have Microsoft Excel already installed on your computer. We have tested 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 version of Excel, however, some our code has been in use since we were using Office 2010 (and continues to work well). We hope these will be useful to you. If you find errors, encounter unhandled errors/exceptions, or have suggestions, please let us know.

As you browse through the lines of code, please remember that an Excel file is also referred to as an Excel workbook. An Excel worksheet is what many people commonly refer to as an Excel tab. By default, a new workbook has one worksheet (used to be three in earlier versions of Excel), but a workbook can contain many worksheets (limited only by the computer's available memory). See here for workbook and worksheet specifications and limits.

Excel File (i.e., Workbook) Operations

• Open and close an existing Excel file (simple open/close code without saving)
• Open, modify, save, and close an existing Excel file
• Open and modify an existing Excel file and save with a new name (Save As)
• Open a password protected Excel file and save with a new name (Save As)
• Open a new Excel file and save
• Open a new Excel file with a specified number of worksheets
• Add title, keywords (i.e., tags), and category to an Excel file
• Read and/or Write Microsoft Excel File Built-In Properties
• Print an Excel file to Adobe pdf

Excel Worksheet (i.e., Tab) Operations

• Add worksheets (i.e., tabs) within an Excel workbook (i.e., Excel file)
• Delete worksheets (i.e., tabs) within an Excel workbook (i.e., Excel file)
• Rename and color Excel worksheet tab
• Hide Excel worksheets
• Add header and footer to an Excel worksheet
• Freeze and unfreeze panes in Microsoft Excel worksheets

Excel Row and Column Operations

• Copy and paste rows and columns in Microsoft Excel
• Autofit entire row and column in an Excel worksheet
• Set row height and column width in an Excel worksheet
• Get number of rows and columns used in an Excel worksheet

Excel Cell Formatting Operations

• Microsoft Excel cell formatting
• Microsoft Excel date formatting
• Microsoft Excel time formatting
• Microsoft Excel number, percentage, fraction, and scientific formatting
• Microsoft Excel currency formatting
• Microsoft Excel accounting formatting
• Microsoft Excel text formatting
• Horizontal and vertical alignment of cell contents in Microsoft Excel
• Insert/create table in Microsoft Excel to organize and analyze data
• Merge rows, columns, and cells in Microsoft Excel