Author: Germán Estévez
Why do we write applications? One answer could be “for fun”, but not always we are in such a comfortable situation of doing something without a real (business) reason. This is where “Creating Value from Programming” comes in.
Recently we’ve done a joint session with Jeroen Pluimers on the Software Developer Event “Office Stuff!!” about different ways of automating Excel and Word from Delphi. Lots of people in banks, insurance companies, corporate headquarters and similar institutions spend most of their time creating Excel spreadsheets. If a computer program can automate a repetitive task of authoring Excel reports then this is a real, tangible business value that comes from programming.
Before joining CodeGear/Borland I’ve been working at the ING Bank Slaski headquarters in the Controlling Deparment. Our financial planning team was responsible for consolidating data coming from different sources into a model of Medium Term Plan. Initially the model was done entirely in Excel. This means a huge number of Excel worksheets with formulas referencing cells in different physical files. With every new version of data we had to reopen master spreadsheets to recalculate totals. The whole process was very tedious so I was looking for some means to automate as much of our work as possible.
At that time Borland released Delphi version 3. One of the most important new features introduced in Delphi 3 were interfaces and support to OLE Automation and COM. I did not have too much time to research this new technology at work, but luckily Charlie Calvert – at that time working at Borland as Evangelist – published an excellent article “Delphi and Microsoft Office: Automating Excel and Word”. This article serves as a great example of how to write professional technical whitepapers, but also provided me with code samples that I could immediately use in my little utility applications that were generating Excel workbooks. Instead of creating files manually, which was very time consuming and error prone, we were able to generate most of the consolidation worksheets used in our models. Can you imagine faces of collegues from other departments coming to our room to see how Delphi application automates Excel? Some of them were concerned, that it would not be enough work left for them to do:-)
Ten years after Charlie Calvert’s original article his code still works with Delphi 2007 for Win32 and Office 2003 installed on my XP machine. There are two ways of automating Excel from Windows native Delphi application: through variants (late binding) and through COM interfaces (early binding). Both approaches have advantages and disadvantages. Code that uses variants is typically easier to write and more portable across different Delphi and Excel versions. This comes at the expense of speed and compile time type checking. With variants communication with OLE Automation server goes through IDispatch interface, which is not the fastest solution, and compiler is not even able to check if a given method exists on the Automation server. At the other hand COM interfaces provide the best possible performance, and compile time type checking. With early binding our code is tied to a specific version of the OLE Automation server. This is why during Delphi installation you need to choose which version of Office components you want to install. These components are generated from type libraries of corresponding Office applications and installed in the Tool Palette “Servers” tab
I have never moved my application from variants to type libraries. The difference in speed that I would be able to achieve did not justify a considerable investment of time. And with variants I was more portable. The only problem is the lack of type information at design time. Anything you write will be happily compiled by Delphi compiler, and you will not get any errors until runtime. The help file with Excel Object Model (“VBAXL10.CHM”) that ships with Office is a very good starting point to check what are the method names, and what are the expected parameter types. Probably the most powerful technique for discovering method names and parameter values is recording a macro within Excel. Click on a “record” button, do in Excel what you want your application to do, stop recording and look into generated VBA code. It is typically quite straightforward to translate this code to Delphi.
During preparation to the Office event I have created a “TSimpleExcel” component that wraps basic functionality of automating Excel from Delphi for Win32 application using variants approach. Private “FApp: Variant” field that stores a reference to running Excel application is also exposed as a public read-only property “App” so it is possible to call arbitrary Excel routines that are not surfaced through component methods and properties. The “TSimpleExcel” component source code can be downloaded from CodeGear Code Central.
There are two published boolean properties in the “TSimpleExcel” component. “ExcelVisible” controls if Excel application is visible. In general it is a good practice to set this property to “False”. First of all you do not want anybody to remove or rename worksheets, that you have created from within your Delphi client application. The other reason is performance. If Excel is hidden, its main window does not need to be repainted in response to our operations. The “DisplayAlerts” property controls whether Excel displays warnings. It is useful to set it to “False” so Excel does not display modal confirmation dialogs to save files before closing etc.
Automating Excel from a Delphi application is just an example of value created from programming. If you find the “TSimpleExcel” component useful, or just want to share your opinion about automating Office applications in Delphi, please make sure to add a comment to this post.
References:
- “Delphi and Microsoft Office: Automating Excel and Word” by Charlie Calvert