Office Automation in VB.NET.

For the purposes of this article, I am using VB 2005 Express Edition and Office 2000.

One of the great features in Visual Studio .NET is the ability to automate Office applications, such as Word, Excel and Outlook. This tutorial will cover all three of these.

References

Before you can start automating any of the Office applications, you will need to add a reference to the application into your project. To do this, go to the Project menu, select Add Reference, and click on the COM tab. In the list, find the following entry:

Microsoft <App> Object Library <x>.0

Where <App> is the application you wish to automate, and <x> is the version number. For example, if I wanted to automate Word, it would be Microsoft Word Object Library 9.0. The version number depends on what version of Office you are using.

Office 2000 = version 9.0
Office XP = version 10.0
Office 2003 = version 11.0
Office 2007 = version 12.0.

Templates

I found that most times I have used automation, I have used the same template file again, and again. It got to the point where I found it easier to create the Word, Excel, etc file I would need for my solution, then add it to the project. I found this especially helpful when dealing with Excel files, because, with Excel having a cell based layout, I could make items appear exactly where I wanted them to.

After you have created your template file (don’t worry about saving it as a template, simply .xls or .doc files will do), right-click on your project name in Solution Explorer, select Add… > Existing Item, then find your template in the browser window that comes up. Click Ok.

In Solution Explorer, select the newly placed template file. In the Properties window, change the Build Action to Content. Now go to Project > <Project Name> Properties… where, guess what, <Project Name> is what you have called your project.

In the Properties screen that is now showing in the main window, go to the last tab down the left hand side, Publish. Click on it. Then click the Application Files button. In the new window that comes up, make sure the Publish Status and Download Group of your template file are Include (or Include (Auto)) and Required respectively. Click Ok, and go to the Form1.vb [Design] screen.

Drop in 2 TextBoxes and a Button. Label the TextBoxes ‘To’ and ‘From’. Double-click the Button.

Before you do anything, you need to declare the Office application you are using. Put them before the Button1_Click event, then open the template file (inside the Button1_Click event) as shown here:



Now, before you start adding information to the Word document, this is how you format the document. I find it easier and faster to use the ‘With’ statement, like so.



Very simple. This makes the Word object visible, sets the alignment to the left of the page, the font to Arial, and the font size to 8.

Now we need text. For a Word document (which is likely to be mainly text), this may take a while because you will need to produce all of the text to go into the document. If your project will be producing lots of the same document, eg letters, then I would recommend using a StreamReader and store the text in one or more small .txt files, or even in the code of your project, like this:



When you are ready, press F5 to start debugging. You should now see your project running in front of you. Add some text into the TextBoxes. In the letter example, they should obviously be names. When you click on the Button, you should see your project open Word and load the text you put in your code and the TextBoxes.

Congratulations, that is how easy Word automation is. There are a lot more advanced features, a few of which we will look at now. Add another Button and TextBox to your project. The TextBox should be labelled File Name.

Continuing the letter example, if it is a company letter, and maybe you wanted to display your campany name in the footer section of each page, then print the letter out, follow these steps.



When you save the document, remember that you will not want to save over the template, but rather save each letter as a new file. In order to do this, each file will have to have a unique filename. To help this, I recommend using the following lines of code to create a folder to keep all of the letter files from this project in. I usually put this in the Form1_Load event so it checks for the save folder whenever the project loads and creates it only if the folder is not already present.



Now, to save you will need something to identify each different letter. You could use the contents of one of the TextBoxes for this, or you could just number each file, like this:



This about sums up just about all I know about Word automation although we will quickly look at Excel and Outlook automation before we finish.

Like Word, you will need to add the reference for both Excel and Outlook. Most of the commands will be the same, but this is the format for adding text to the cells in an Excel spreadsheet.



This is the format for programatically creating an email in Outlook.



One last thing that is fairly important to know. Regardless of which Office application you are automating, if you want to close the app from within your project, you will need this command. I usually put in a Form1_FormClosing event, so the Office application closes when my project does.



Hopefully this has helped you out. If you think I’ve missed anything, please let me know so I can add, or add it yourself.

Happy coding.
Bort

I spotted someone in the VB.NET forums asking about adding page numbers to their Word document, so here goes.

First of all, focus on the header of the document.



Now to get the page number variables.



And now add the page numbers to your header.




This should work, but it has not been tested yet (not enough time today), so please post here whether it works or not. If not, I'll see what else I can come up with.

Edit: Correcting a mistake in the code.

Happy coding,
Bort