VB.NET Tutorials

Welcome to Dream.In.Code
	Become an Expert! Join 300,579 Programmers for FREE! Get instant access to thousands of experts, tutorials, code snippets, and more! There are 2,236 people online right now. Registration is fast and FREE... Join Now! Chat LIVE With a Expert

Office Automation

Office Automation, Automating Word, Excel, and Outlook from within VB.NET.

Options

Bort

18 Aug, 2008 - 12:40 AM

Post #1

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:

CODE

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.

CODE

        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:

CODE

        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.

CODE

        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.

CODE

    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:

CODE

    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.

CODE

    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.

CODE

        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.

CODE

    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.

Happy coding.
Bort

Register to Make This Ad Go Away!

Bort

2 Sep, 2008 - 06:35 AM

Post #2

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.

CODE

oWord.ActiveWindow.ActivePane.View.SeekView = Word.WdSeekView.wdSeekCurrentPageHeader

Now to get the page number variables.

CODE

Dim CurrentPage As Object = oWord.WdFieldType.wdFieldPage
Dim TotalPages As Object = oWord.WdFieldType.wdFieldNumPages

And now add the page numbers to your header.

CODE

oWord.ActiveWindow.Selection.TypeText("Page " & CStr(CurrentPage) & " of " & CStr(TotalPages))

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

This post has been edited by Bort: 5 Sep, 2008 - 02:01 AM

Bort

10 Jun, 2009 - 05:02 AM

Post #3

Guys, I've been looking a bit more at Automation, and with the more recent versions of Office, you will need a line similar to this one at the top of your project:

CODE

Imports Excel = Microsoft.Office.Interop.Excel

Public Class Form1

Thanks to Mussi for pointing me towards this

gate7cy

29 Oct, 2009 - 06:09 AM

Post #4

How about openning the word template within a winform. Instead of calline Word to open and show the document why not the document being shown within a winform of the application. How can you accomplish that? nice topic with some very usefull tips

This post has been edited by gate7cy: 29 Oct, 2009 - 06:09 AM