Page 1 of 1

Office Automation Automating Word, Excel, and Outlook from within VB.NET. Rate Topic: ****- 1 Votes

#1 Bort  Icon User is online

  • Ill-informed Mongoloid
  • member icon

Reputation: 396
  • Posts: 2,920
  • Joined: 18-September 06

Posted 18 August 2008 - 01:40 AM

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:

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.

Happy coding.
Bort

Is This A Good Question/Topic? 2
  • +

Replies To: Office Automation

#2 Bort  Icon User is online

  • Ill-informed Mongoloid
  • member icon

Reputation: 396
  • Posts: 2,920
  • Joined: 18-September 06

Posted 02 September 2008 - 07:35 AM

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.

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



Now to get the page number variables.

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



And now add the page numbers to your header.

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: 05 September 2008 - 03:01 AM

Was This Post Helpful? 1
  • +
  • -

#3 Bort  Icon User is online

  • Ill-informed Mongoloid
  • member icon

Reputation: 396
  • Posts: 2,920
  • Joined: 18-September 06

Posted 10 June 2009 - 06:02 AM

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:

Imports Excel = Microsoft.Office.Interop.Excel

Public Class Form1



Thanks to Mussi for pointing me towards this :)
Was This Post Helpful? 1
  • +
  • -

#4 gate7cy  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 2
  • Joined: 29-October 09

Posted 29 October 2009 - 07:09 AM

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 October 2009 - 07:09 AM

Was This Post Helpful? 0
  • +
  • -

#5 Crashnburn5590  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 1
  • Joined: 15-February 10

Posted 15 February 2010 - 06:13 PM

Thanks for posting the code. In addition to accessing an Excel worksheet, I'd like to access a tab in the worksheet. How would I go about doing that?

Thanks,

Rick
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1