|
The Microsoft Office products (Word, Excel, PowerPoint, Access, Outlook) provide servers that you can use to manipulate Office documents from Visual Basic or Visual Basic for Applications (VBA) code. For example, you could write a Visual Basic program that opens Excel, initializes a series of cells, and uses the cells to display a chart. For another example, you could write a VBA macro in PowerPoint that opens a Word document and copies its headings into PowerPoint slides.
The basic approach is to declare an instance of the Office application server, create the server, and then use the server's properties and methods to do your work. There are two basic methods for declaring the server: early binding and late binding.
Early Binding
To use early binding, select the Project menu's References command, check the appropriate Office application's library, and click OK. For instance, depending on the version you have installed Excel's library will be called something like Microsoft Excel 9.0 Object Library.
Next declare the server object using the type Xxx.Application where Xxx is the name of the Office application. For example, Excel.Application. Use the New statement to create the server.
Dim excel_app As Excel.Application
Set excel_app = New Excel.Application
If the reference and declaration are correct, when you type "Excel." Intellisense will list the objects and methods defined by the Excel library. The list should include Application.
Now you can use Excel's Application object to do things such as opening an Excel workbook, storing data in a workbook, and saving the results.
Late Binding
To use late binding, declare the server object using the generic Object data type. Then creat the server by passing the CreateObject function the name of the server class.
Dim excel_app As Object
Set excel_app = CreateObject("Excel.Application")
Which Method?
Late binding does not require a library reference so it provide a little more flexibility. For example, if you copy the program to another computer that has a different version of Excel installed, the program will probably be able to run (it won't if the program tries to use features not present in the installed version). If the program used a library reference, it would complain about not finding the correct library.
On the other hand, declaring server objects using an explicit data type such as Excel.Application tells Visual Basic a lot about the object. At design time, Visual Basic knows the properties and methods the object supports. At run time, that lets the system invoke the object's methods relatively quickly.
If the variable is declared using the generic type Object, Visual Basic must query the object at run time to see if it supports a particular method. That means everything you do with the object takes a little longer.
Of course the Office servers are very large applications and loading them takes a considerable amount of time. If you aren't performing extensive calculations, the extra time required by late binding will not be noticable compared to the time it takes to load the server.
A much better reason for selecting early binding is that it allows Intellisense to help you program. Many Office methods take dozens of parameters and Intellisense can be invaluable in remembering what they are.
Overall, I would use early binding unless I had a real reason not to.
| 
