Automate Office Excel 2007 Reports with Visual Basic 2005

Ed Robinson

Intergen Ltd

October 2007

Summary: This article discusses how to create a regular reporting system by pulling data from a database, inserting that data into a Microsoft Office Excel spreadsheet (and chart), and e-mailing it to users. We show how to use Microsoft Visual Basic 2005 to automate Office Excel 2007, create a workbook, and update cells in the workbook. We also demonstrate how to programmatically create a Windows scheduled task that creates and e-mails the workbook on a regular schedule.

Click here to download the code sample for this article.

Contents

Introduction

Understanding How Office Excel Automation Works

What's in the Sample?

Essential Automation Techniques

Populating a Worksheet with an ADO.NET DataTable

Creating a Scheduled Task to E-Mail a Workbook Regularly

Conclusion

Introduction

A common requirement in many businesses is to have status reports created and e-mailed regularly. While all of the information can be automatically populated from database systems, reporting is often an afterthought in the design stage—leaving developers who are accustomed to building complex line-of-business applications having to pull reports manually every week. Office Excel automation, combined with a regular e-mail schedule, turns this task from a manual process into an automated one; the code sample for this article is a working framework that you can adapt to your needs.

Office Excel automation is a technique for "remote-controlling" Office Excel to start, load a document, update cells, print, save, and exit—essentially, any task that you would perform in Office Excel itself. This article introduces two techniques. First, we show how to use automation to create an Office Excel workbook and populate it with data. Second, we show how to programmatically create a workbook and set up a schedule to e-mail it regularly to a recipient.

This is the third article in the Visual Basic automation series and builds on concepts that were introduced in the first article, Automating the Creation of Data-Rich Business Documents with Word 2007 and Visual Basic 2005.

Understanding How Office Excel Automation Works

Microsoft Office Excel 2007 is a COM application and can be automated by using a COM object library that is installed with Office Excel and available to Visual Basic 2005. If you've had experience writing VBA Office Excel macros, many of the objects that are used in automation will be familiar to you, because VBA uses the same object library. Visual Basic 2005 is a fantastic system for creating applications that exploit COM Interop and automation; thus, the process for automating Office Excel is easier than ever before.

To assist with understanding the automation objects, Visual Basic 2005 includes F1 help for most of the objects in the Office Excel 2007 library. The only limitation is that the help library is one-version-behind—targeting Office Excel 2003. While writing the sample code for this article, we found it useful to refer to the latest online documentation, which can be found in the MSDN Library by clicking here.

To automate Office Excel, the basic process is to follow this set of steps:

1. Create an Office Excel application object.
2. Create a new workbook, or open an existing workbook.
3. Update the cells and content.
4. Print or save the workbook.

The Office Excel object model has about 250 objects; in practice, however, there are only four objects with which you should really be familiar.

Application Object

The Application object represents the Office Excel 2007 application itself, and creating an Application object is the first step in automation. When you create an Application object, a new instance of Office Excel is started—enabling you to manipulate workbooks programmatically. During the process of automation, Office Excel can be made visible or kept invisible. As you will see in the sample, we choose to keep Office Excel visible when users click a button, so that they can watch Office Excel performing an action on screen. We keep Office Excel invisible when e-mailing or automating Office Excel in the background. Only one instance of the application object has to be created, and you can use this single instance to manipulate multiple workbooks.

Workbook Object

After creating the Application object, the next step is to create or load a workbook. This step is necessary, because Office Excel does not create a blank workbook when it starts. The Workbook object represents a workbook in Office Excel, and it is the primary document type that you will create, open, and save.

Worksheet Object

Every workbook contains one or more worksheets. A worksheet is the actual document—represented as a familiar Office Excel grid into which you can enter content. The Worksheet object represents an Office Excel worksheet, and it is the primary object for entering and reading content from Office Excel.

Cell Object

Every sheet contains cells, which are the editable boxes in a worksheet in which information is stored. Cells are referenced by row and column. In the Office Excel user interface, rows are numbered 1, 2, 3, and so on; and columns are lettered A, B, C, and so on. In the Office Excel programming model, both rows and columns are referenced as numbers that start from 1,1 at the top left corner of a worksheet. Interestingly, in the Office Excel user interface, a cell location is referred to by column, then row; whereas in the programming model, the location is referred to by row, then column.

For example, in Figure 1, the selected cell would be referred to as B3 in the user interface, but 3,2 in the programming model.

Figure 1

Office Excel 2007 supports sheets with up to 1,048,576 rows and 16,384 columns, which means that you can store more than 17 billion values in a single sheet.

The Cell object represents a cell in Office Excel. You can write to, read from, and set up formulas in a cell by using automation, just as you would by using the Office Excel application itself.

What's in the Sample?

Let's get started by looking at the sample application that accompanies this article.

The "ExcelAuto" sample application demonstrates how to start Office Excel, create a workbook from a template, and update cells in a sheet that then populate graphs inside the workbook. The sample also shows how to e-mail the workbook to an e-mail address—either one-time or on a regular schedule.

First, we'll have to ensure that your machine has all of the necessary prerequisites:

• You will need to have Visual Studio 2005 installed, with Service Pack 1.
• You will also need to install Office Excel 2007.

After installing the prerequisites, unzip the sample files to a directory on your local hard disk. The sample consists of a solution that is named ExcelAuto.sln and contains two projects: ExcelAuto.vbproj is an interactive Windows application for automating Office Excel and setting up a scheduled task; ExcelAutoTask is a console application that contains Office Excel automation code that is run as the scheduled task.

Now, we're ready to walk through the sample:

1. Open the ExcelAuto.sln solution.
2. Press F5 to compile and run the application.
3. When the ExcelAutomation application runs, it opens a single form with buttons for automating Office Excel, as shown in Figure 2.
   Figure 2
4. Click the Load Spreadsheet with Data button. ExcelAuto creates a new Office Excel application. It then creates an Office Excel workbook from a template, and updates the cells from data that is stored in the OrderData.xml file. This data represents order status from the Northwind sample database. The spreadsheet itself is a status report that shows the status of Northwind orders, as shown in Figure 3.
   Figure 3
   Notice how the graphs that are inside the workbook update with the new data that we inserted. One of the powerful features of Office Excel is the ability to link cells and data ranges—meaning that we can define a rich report that populates automatically, as data is entered or changed.
5. When you've finished with the spreadsheet, close Office Excel. This is necessary, because Office Excel stays open unless the automation code explicitly tells it to quit.

Now, let's look at the e-mail and task-scheduling features in the sample:

1. The sample includes the ability to e-mail a spreadsheet. In order to demonstrate this, you will need to enter your e-mail address into the E-mail Address field. By using Microsoft Office Outlook automation, you can e-mail the spreadsheet by clicking the E-mail spreadsheet via Outlook button. By using SMTP, you can e-mail the spreadsheet by entering the SMTP host into the SMTP Host field and clicking the E-mail spreadsheet via SMTP button.
   For more information on how to configure the application for SMTP mail, see the section Creating a Scheduled Task to E-Mail a Workbook Regularly, later in this document. The application creates, saves, and e-mails a workbook to the e-mail address. The workbook is uniquely named with the data and time that it was sent.
   Figure 4
2. You can also schedule the workbook to be e-mailed on a regular basis: on a daily, hourly, or even per-minute basis. To do this, use the controls in the Recurrence pattern group box to set up the schedule and start date, and then click Schedule recurring e-mail via SMTP button. The application sets up a Windows scheduled task that executes the ExcelTaskAuto application on the schedule that you specified. You can see and modify the schedule by using the Scheduled Tasks utility that is found in Start Menu | All Programs | Accessories |System Tools |Scheduled Tasks.
   Figure 5
3. Finally, you can remove the task from Task Scheduler by clicking the Remove Recurrence button.

Essential Automation Techniques

The following code snippets illustrate the techniques that are used in the sample application.

Setting Up

You can try the following techniques in any Visual Basic client application. Before beginning, there are two things that you must do to set up your project for Office Excel automation:

1. Add a project reference to the "Microsoft Excel 12.0 Object Library" COM object.
2. Import the Microsoft.Office.Interop namespace by adding the statement "Imports Microsoft.Office.Interop" to the top of any module, form, or class that contains Office Excel automation code.

Starting and Quitting Office Excel

To start Office Excel, use the following code, which creates an Excel.Application object:

Dim objExcel As New Excel.Application

By default, Office Excel opens invisibly. If you plan to automate Office Excel interactively so that users can see what is going on, you can make Office Excel visible by using the following code:

objExcel.Visible = IsVisible

To quit Office Excel, use the following code:

objExcel.Quit()

When Visual Basic 2005 creates an instance of Office Excel by using automation, it maintains only a loose coupling between the applications; if the Visual Basic 2005 application ends prematurely, Office Excel will still remain in memory. We recommend explicitly quitting Office Excel, unless you want it to remain open for the user to interact with. We also recommend that you use structured exception handling (Try...Catch) to wrap any code that might cause an exception, and call the Excel.Quit method in the exception handler to ensure that Office Excel quits.

Creating and Opening Workbooks

To create a new workbook, first start Office Excel, and then create a workbook by using the Application.Documents.Add method:

Dim objWorkbook As Excel.Workbook
objWorkbook = objExcel.Workbooks.Add()

To open a workbook, use the Application.Workbooks.Open method:

Dim objWorkbook As Excel.Workbook
objWorkbook = objExcel.Workbooks.Open("c:\myworkbook.xlsx")

To save a document, use the Workbook.SaveAs method:

objWorkbook.SaveAs(strSaveFilename, Excel.XlFileFormat.xlWorkbookDefault)

The second parameter in the SaveAs method allows you to save in different formats, including Office Excel 2003, when you need to save a workbook in a backwards-compatible file format.

Reading and Updating Cells

A workbook contains one or more worksheets; the worksheet is the primary object with which we work, to read from and update cells. Because every worksheet already has a full set of rows and columns, we don't have to create cells to write to them; instead, we just update their contents.

As mentioned previously, cells are referred to by row, column. The following code shows how to write "Foo" as the text of the cell in the 5th row, 2nd column:

Dim objExcel As New Excel.Application
Dim objWorkbook As Excel.Workbook
Dim objSheet As Excel.Worksheet
objExcel.Visible = True
objWorkbook = objExcel.Workbooks.Add
objSheet = objWorkbook.Worksheets(1)
objSheet.Cells(5, 3).Value = "Foo"

Reading from a cell is equally simple. The following code shows how to read the value of the cell at position 1,1:

Dim strText As String
strText = objSheet.Cells(1, 1).Value

What variable type should you use for reading a value from a cell? We recommend transferring the contents of a cell into a string, then casting to another variable type if you need to. This enables you to perform any data validation before casting to a type. Whatever the value of a cell, a string will accept the contents. If a cell is blank, Office Excel returns its value as an empty string.

Printing

To print a worksheet, use the PrintOutEx method:

objSheet.PrintOutEx()

Populating a Worksheet with an ADO.NET DataTable

The data in an Office Excel worksheet is very similar to an ADO.NET DataTable, with information that is stored in cells referenced by row and column. In the sample, we include a simple method for copying an ADO.NET DataTable into an Office Excel spreadsheet. This method just uses a For...Next loop to copy each cell from a data table into the spreadsheet. You can reuse this method as is or modify the code in your own applications.

Here is the method, as well as an explanation of how to use the parameters:

Sub DataTableToExcelSheet(ByVal dt As DataTable, _
ByVal objSheet As Excel.Worksheet, ByVal nStartRow As Integer, _
ByVal nStartCol As Integer)

Here are the parameters:

dt	The DataTable that contains the data
objSheet	The worksheet to copy the data into
nStartRow	The starting row (top position) to copy the data into
nStartCol	The starting column (left hand side) to copy the data into

For example, to insert the "MyTable" DataTable at cell C5 in the "MySheet" sheet, you would call the method by using the following parameters:

DataTableToExcelSheet(MyTable, MySheet, 5, 3)

Creating a Scheduled Task to E-Mail a Workbook Regularly

The sample that accompanies this article shows how to set up a regular task, to e-mail a workbook to one or more recipients. This illustrates two important techniques that we will now discuss in greater detail.

Setting Up a Scheduled Task

Microsoft Windows has a very useful Scheduled Tasks capability. You can set up any task to run either once or on a regular basis. While the .NET Framework does not support programmatically controlling Task Scheduler, there are two simple ways that this can be achieved:

By using Task Scheduler (found in Start Menu | All Programs | Accessories |System Tools |Scheduled Tasks) In Vista the name is changed from “Scheduled Tasks” to “Task Scheduler”: Start Menu | All Programs | Accessories |System Tools |Task Scheduler. This requires Administrator privileges.

• By using the Windows console.

In the sample application, we set up the scheduled task by using the SchTasks.exe console command, which is invoked with the Shell command. This works incredibly well; with the Shell command, the console itself can be hidden, which makes the entire process invisible to the user.

If you want to experiment with the different capabilities that the SchTasks.exe console command offers, open a new Command Prompt, and type the SchTasks.exe /? command to see the list of available options, as shown in Figure 6.

Figure 6

Tasks can be scheduled to run in the future on a one-time or regular basis. Almost any application can be scheduled, as long as you can figure out an appropriate command line for it.

Because many users turn off their workstations at night or restart them during the day, scheduled tasks are commonly executed from a server that generally will have higher availability. When running them from a server, one thing to think about is what Windows account to execute under. In the sample, the task is run by using the System account, which has administrator privileges. You can easily change this to run under another account (documented in the sample code).

One limitation of running under the System account is that tasks are prevented from showing any form of user interface. The ExcelAutoTask.exe application operates in these constraints and opens Office Excel invisibly; it also e-mails the workbook without showing any user interface. In practice, this is a great mechanism—allowing essential tasks to operate quietly in the background.

Sending E-mail from Visual Basic 2005

In the sample, we demonstrate two methods for sending e-mail:

1. Automating Office Outlook
2. Using the System.Net.Mail classes

Automating Office Outlook is simple to use and great for desktop use. If you plan to run the application as a scheduled task, you'll need to use the new .NET framework 2005 e-mail classes to send e-mail from Visual Basic 2005, because the scheduled task should not show a user interface. These classes are located in the System.Net.Mail namespace and are very easy to use. Documentation on sending e-mail from Visual Basic 2005 can be found in the MSDN Library by clicking here.

The System.Net.Mail classes use the simple mail-transfer protocol (SMTP) to send e-mail. The one critical factor is specifying the SMTP host (the e-mail server) that will actually send the e-mail. In the sample application, this is entered in the SMTP Host text box. The name of the host depends on what your e-mail server is named. However here are some general rules for figuring it out:

• Use "LocalHost" if the machine on which the application is running has an SMTP service running; otherwise, use the address of your e-mail server, which should be in the format <servername>.<domainname> (for example: exchange.example.com). Be aware that some antivirus software prevents software from automatically sending e-mail.
• Some SMTP servers also require you to provide your user name and password, which you can add to the e-mail sample by using the following code:

 objEmailClient.Credentials = New NetworkCredential("username", "password")

Conclusion

In this article, we introduced techniques for using Visual Basic 2005 and automation to remote-control Office Excel 2007. We also demonstrated how to schedule a task automatically to e-mail workbooks on a regular interval. The previous article in this series demonstrates how to automate Office Word 2007 with Visual Basic 2005; and it contains guidance on the different Office programming techniques, and when to use what. Microsoft Office continues to grow as an incredibly popular platform for developing solutions that improve the productivity of information workers. With Office Excel automation, Visual Basic 2005, and the techniques that were introduced in this article, we hope that you're on your way to producing the next generation of productivity solutions that simplify and automate some of the manual tasks that face information workers.

About the author

Ed Robinson has coauthored Upgrading Microsoft Visual Basic 6.0 to Microsoft Visual Basic .NET, Security for Microsoft Visual Basic .NET, and numerous technology articles. Ed is the CIO for Intergen Ltd—one of New Zealand's most prominent Microsoft Gold Certified Partners.