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.

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.

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.

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.

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.

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.
   

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.

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.

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.

To print a worksheet, use the PrintOutEx method:

objSheet.PrintOutEx()

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.

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")

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.