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.
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.
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:
Public Class Form1 Dim objWord As New Word.Application Dim objDoc As New Word.Document Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click objDoc = objWord.Documents.Open(Application.StartupPath &_ "\template.dot")
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.
With objWord 'Make Word Application Visible .Visible = True 'Sets Alignment to Left .Selection.Paragraphs.Alignment = Word.WdParagraphAlignment.wdAlignParagraphLeft 'Set Font .Activewindow.Selection.Font.Name = "Arial" 'Set Font Size .Activewindow.Selection.Font.Size = 8 End With
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:
Dim strOpening As String = "Dear " & TextBox1.Text & "," & vbCrLf & vbCrLf Dim strLetter As String = "Letter Body Here" & vbCrLf & vbCrLf Dim strClosing As String = "Yours Sincerely," & vbCrLf & vbCrLf & TextBox2.Text objWord.Activewindow.Selection.TypeText(strOpening & strLetter & strClosing)
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.
With objWord 'Focus on Footer (Use wdSeekCurrentPageHeader to focus on header) .Activewindow.ActivePane.View.SeekView = Word.WdSeekView.wdSeekCurrentPageFooter 'Set Font .Activewindow.Selection.Font.Name = "Arial" 'Set Font Size .Activewindow.Selection.Font.Size = 8 'Type in Footer (Use TypeText(TextBox1.Text) etc for adding a footer from a TextBox) .Activewindow.Selection.TypeText("Company Name Here") 'Resets focus back to main document .Activewindow.ActivePane.View.SeekView = Word.WdSeekView.wdSeekMainDocument .PrintOut() End With
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.
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load If My.Computer.FileSystem.DirectoryExists("C:\WordAutomation") = False Then My.Computer.FileSystem.CreateDirectory("C:\WordAutomation") MessageBox.Show("A 'WordAutomation' folder has been created on your C: drive. This is where any letters will be saved.") End If End Sub
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:
Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click 'Find out how many files are saved in the Save folder Dim int As Integer int = My.Computer.FileSystem.GetFiles("C:\WordAutomation\").Count + 1 'Save As command objDoc.SaveAs("c:\WordAutomation\Letter" & int & ".doc") End Sub
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.
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click oExcel = CType(CreateObject("Excel.Application"), Excel.Application) oBook = oExcel.Workbooks.Open(Application.StartupPath & "\" & TextBox5.Text & ".xlt") Try With oExcel .Visible = True 'Add details .Range("E12").Value = TextBox1.Text .Range("E13").Value = TextBox2.Text .Range("E14").Value = TextBox3.Text .Range("E15").Value = TextBox4.Text End With Catch ex As Exception MsgBox("Error: " & ex.ToString, MsgBoxStyle.Critical, "Error!") End Try End Sub
This is the format for programatically creating an email in Outlook.
Try Dim objOutlook As Outlook.Application Dim objEmail As Outlook.MailItem objOutlook = CType(CreateObject("Outlook.Application"), Outlook.Application) objEmail = objOutlook.CreateItem(Outlook.OlItemType.olMailItem) Dim body As String 'Gets First word in TextBox Dim firstWord As String = System.Text.RegularExpressions.Regex.Match(txtContact.Text, "^\w+\b").Value body = "Hi " & firstWord & "," & vbCrLf & vbCrLf body += "Please find attached purchase order number " & txtPONumber.Text & "." & vbCrLf & vbCrLf body += "Please confirm lead time." & vbCrLf & vbCrLf body += "Thanks and Best Regards," & vbCrLf & cmbBuyer.SelectedItem.ToString With objEmail .Subject = "Purchase Order " & txtPONumber.Text .To = txtEmail.Text .Body = body .Attachments.Add(PDFFile) .Display(True) End With Catch ex As Exception MsgBox("Unable to generate automatic email. Please create the email manually.", MsgBoxStyle.Information, "Error!") End Try
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.
Private Sub Form1_FormClosing(ByVal sender As Object, ByVal e As System.Windows.Forms.FormClosingEventArgs) Handles Me.FormClosing objExcel.Quit() Try objExcel.Quit() Catch ex As Exception End Try End End Sub
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.