Professional Excel Development [Electronic resources] : The Definitive Guide to Developing Applications Using Microsoft® Excel and VBA®

The Primary Office Application Object Models

Now that we can reliably detect, start, control and shut down other Office applications, the final piece of the puzzle is to learn each application's object model. This part of the chapter provides an introduction to the main objects within each object model, demonstrating some typical uses within Excel-based applications.

All the Office applications have a top-level Application object, which is the object we get a reference to when creating new instances of the application. From the Application object, we drill down to the other objects that provide the application's functionality.

All the examples in this section can be found on the CD in the \Concepts\Ch18Controlling Other Office Applications folder. The workbook Ch18Examples.xls contains all the example code and a data sheet to represent the results of some analysis.

Access and Data Access Objects

It's actually quite rare to automate Access itself from an Excel application. We can easily manipulate the data in an Access (Jet) database outside of Access using ActiveX Data Objects, as described in Chapter 13 Programming with Databases, and there's little reason to use Access forms instead of VBA userforms. However, Access is much better than Excel for creating continuous data-driven reports, with its sorting and grouping and separate group, page and report headers and footers.

Application

Each instance of Access has a single database, which we open using Application.OpenCurrentDatabase and close using Application.CloseCurrentDatabase. The CurrentDb object exposes a Data Access Objects (DAO) Database object, which we can use to manipulate the structure of the tables and queries in the database. Most of the other properties of the Application object provide information about the state of the application, such as which tables the user has open, and are very rarely relevant when controlling Access from Excel.

DAO.Database

The DAO Database object that we get from Application.CurrentDb provides programmatic access to the structure of the database, via the TableDefs, QueryDefs and Relations collections. The most commonly used of these is the TableDefs collection, through which we can access the properties of the database tables. In many situations, we may have an access table linked to a separate data source, such as an Excel workbook or SQL Server table and will need to change the table's link information prior to running a report.

DoCmd

Most automation of Access is done through the DoCmd object, which provides programmatic access to most of Access' menus, including deleting tables, importing data and running reports.

Example

The procedure in Listing 18-11 runs an Access report based on data in an Excel workbook. The Access database ReportOnExcelData.mdb contains a single table, tblExcelData, to link to the Excel workbook, a query to sort the data and a report, rptExcelData, to run. The procedure creates an instance of Access, opens the database, updates the table's connection information and runs the report, leaving it displayed onscreen.

This code can be found in the MAccess module of the Ch18Examples.xls workbook and the database containing the linked table and report is called ReportOnExcelData.mdb.

Listing 18-11. Running an Access Report Using Excel Data

Sub AccessRunReport()
'Requires references to the Microsoft Access and
'Microsoft DAO object libraries
Dim objApp As Object
Dim accApp As Access.Application
Dim dbData As DAO.Database
Dim tdExcelData As DAO.TableDef
'Update the export range
wksAccess.Range("tblExcelDataStart").CurrentRegion _
.Name = "tblExcelData"
'Save any changes to the workbook, so Access can read
'the latest version from disk
ThisWorkbook.Save
'Attempt to create a new instance of Access
Set accApp = Nothing
Set accApp = CreateObject("Access.Application")
With accApp
'Set the access automation security,
'so the database opens without prompts.
'Use late binding and On Error Resume Next
'to ignore version issues
On Error Resume Next
Set objApp = accApp
objApp.AutomationSecurity = 2 'msoAutomationSecurityLow
On Error GoTo 0
'Open the database
.OpenCurrentDatabase FilePath:=ThisWorkbook.Path & _
"\ReportOnExcelData.mdb"
'Get a reference to the DAO TableDef for the
'tblExcelData linked table
Set dbData = .CurrentDb
Set tdExcelData = dbData.TableDefs("tblExcelData")
'Update the table link to point to this workbook
tdExcelData.Connect = "Excel 8.0;HDR=YES;IMEX=2;" & _
"DATABASE=" & ThisWorkbook.FullName
Set tdExcelData = Nothing
Set dbData = Nothing
'Open and preview the report
.DoCmd.OpenReport ReportName:="rptExcelData", _
View:=acViewPreview
'Make the App visible
.Visible = True
End With
'Clear the variable from memory
Set accApp = Nothing
End Sub

Word

Word is often automated from Excel when we need to populate a Word document from data in Excelsuch as a monthly report that contains some data analyzed in Excel.

Application

As well as the usual properties to control the application itself, the Word Application object has a Documents collection that we use to create, open and access Word documents.

Document

The Document object provides all the information about a Word document, akin to Excel's Workbook object.

Bookmark

Each bookmark within a document is included in the Document.Bookmarks collection and exposed as a Bookmark object. Bookmarks enable us to easily identify elements of text within a document.

Range

A Range is a contiguous area in a document, identified by its start and end points. Many Word objects (such as Paragraph and Bookmark) have a Range property that returns the area enclosed by the object. We can populate a bookmark by setting the text of its Range. One issue with doing this is that setting the text in a bookmark deletes the bookmark. To set a bookmark's text, we have to store the bookmark's range, set the text of the range, then re-create the bookmark, as shown in Listing 18-12.

Example

Survey results are very often analyzed in Excel and published as a Word document. This is usually achieved by creating a Word template for the survey results, identifying each insertion point as a bookmark, then copying the data from the Excel workbook to the Word document using VBA. It is quite common in corporate surveys to create a document specific to each of the respondents, where each report is essentially the same, but with that respondent's results and rankings. Listing 18-12 shows a very simple example of this, where we loop through all the divisions in a company, analyzing the data and producing a document for each.

This code can be found in the MWord module of the Ch18Examples.xls workbook and the document template is called SalaryReport.dot.

Listing 18-12. Populating a Word Template from Excel Data

Sub GenerateDivisionSummaries()
Dim wrdApp As Word.Application
Dim wrdDoc As Word.Document
Dim wrdrngBM As Word.Range
Dim piDiv As Excel.PivotItem
Dim rngBookmark As Excel.Range
Dim sPath As String
Dim sBookmarkName As String
'Start Word
Set wrdApp = CreateObject("Word.Application")
sPath = ThisWorkbook.Path & "\"
'Create a new document based on the template
Set wrdDoc = wrdApp.Documents.Add(Template:= _
sPath & "SalaryReport.dot")
'Loop through each division in the pivot table
For Each piDiv In wksData.PivotTables(1) _
.PivotFields("Division").PivotItems
'Populate the Division Name cell
wksData.Range("ptrDivName") = piDiv.Value
'Recalc the sheet to update the results
'for the division
wksData.Calculate
'Populate the bookmarks from the sheet
For Each rngBookmark In _
wksData.Range("rngBookmarks").Rows
'Get the name of the bookmark
sBookmarkName = rngBookmark.Cells(1, 1).Value
'Get the Word Range that the bookmark spans
Set wrdrngBM = wrdDoc.Bookmarks(sBookmarkName) _
.Range
'Set the text of the range
'(which deletes the bookmark)
wrdrngBM.Text = rngBookmark.Cells(1, 2).Text
'Re-create the bookmark for the next iteration
wrdDoc.Bookmarks.Add sBookmarkName, wrdrngBM
Next rngBookmark
'Update any fields linked to these bookmarks
wrdDoc.Fields.Update
'Save the filled document
wrdDoc.SaveAs sPath & "Salary Results - " & _
piDiv.Value & ".doc"
Next piDiv
'Close the Word document
wrdDoc.Close
Set wrdDoc = Nothing
'Close Word
wrdApp.Quit False
Set wrdApp = Nothing
End Sub

PowerPoint and MSGraph

PowerPoint is usually used in a similar way to Wordpopulating pre-prepared presentations with data from Excel.

Application

As well as the usual properties to control the application itself, the PowerPoint Application object has a Presentations collection that we use to create, open and access PowerPoint presentations.

Presentation

The Presentation object provides all the information about a PowerPoint presentation, akin to Excel's Workbook object.

Slide

The Slide object provides the information about a slide within a presentation, akin to Excel's Worksheet object. When automating PowerPoint, it helps to give each slide a meaningful name, which can be done by selecting the slide and running the following statement from the PowerPoint VBE's Immediate window:

ActiveWindow.Selection.SlideRange(1).Name = "NewSlideName"

Shape

The Shape object is the same as Excel's Shape object and is a drawing object on a Slide, which can be a container for text boxes, lines, pictures or embedded objects such as charts. A shape can be given a meaningful name by selecting it and running the following statement from the PowerPoint VBE's Immediate window:

ActiveWindow.Selection.ShapeRange(1).Name = "NewShapeName"

Charts

PowerPoint charts are provided by the MSGraph object model, which is a version of Excel's charting engine, modified to remove the worksheet links. As such, most Excel charting code will work on a PowerPoint chart. However, it is quite common to prepare the charts within Excel, copy them to the clipboard and paste them as pictures in PowerPoint.

Example

The procedure in Listing 18-13 updates a PowerPoint presentation with data from an Excel spreadsheet. It updates both text in a bulleted list and the source data for an embedded chart.

This code can be found in the MPowerPoint module of the Ch18Examples.xls workbook and the presentation we're updating is called Salary Presentation.ppt.

Listing 18-13. Populating a PowerPoint Presentation from Excel Data

Sub PPTGenerateSalarySummary()
'Powerpoint objects
Dim pptApp As PowerPoint.Application
Dim pptPres As PowerPoint.Presentation
Dim pptSlide As PowerPoint.Slide
Dim pptBullets As PowerPoint.Shape
'MSGraph objects
Dim gphChart As Graph.Chart
Dim gphData As Graph.DataSheet
'Excel objects
Dim pfDiv As Excel.PivotField
Dim rngDiv As Excel.Range
'Other variables
Dim sBulletText As String
Dim lDiv As Long
'Start PowerPoint
Set pptApp = CreateObject("PowerPoint.Application")
'Switch back to Excel
AppActivate Application.Caption
'Open the presentation
Set pptPres = pptApp.Presentations.Open(Filename:= _
ThisWorkbook.Path & "\Salary Presentation.ppt", _
withwindow:=False)
'Get the 'Detail' slide
Set pptSlide = pptPres.Slides("sldDetail")
'Get the shape containing the bulleted list
Set pptBullets = pptSlide.Shapes("shpBullets")
'Get the text of the first bullet in the list
sBulletText = pptBullets.TextFrame.TextRange _
.Paragraphs(1).Text
'Update the text with the calculated total
'from the worksheet
sBulletText = Replace(sBulletText, "#SalaryTotal#", _
wksData.Range("ptrSalaryTotal").Text)
'Update the presentation with the correct text
pptBullets.TextFrame.TextRange.Paragraphs(1) _
.Text = sBulletText
'Get the MSGraph Chart object embedded in the slide
Set gphChart = pptSlide.Shapes("shpChart").OLEFormat _
.Object
'Get the graph's data sheet
Set gphData = gphChart.Application.DataSheet
'Get the 'Division' pivot field in the Data worksheet
Set pfDiv = wksData.PivotTables(1).PivotFields("Division")
'Loop through the range of Divisions in the pivot table
For Each rngDiv In pfDiv.DataRange
lDiv = lDiv + 1
'Write the division name and total salary to the
'graph data sheet
gphData.Cells(1, lDiv + 1).Value = rngDiv.Text
gphData.Cells(2, lDiv + 1).Value = rngDiv _
.Offset(0, 1).Value
Next rngDiv
'Apply the datasheet changes
gphChart.Application.Update
'Redraw the chart object
gphChart.Refresh
'Save the presentation with a new name
pptPres.SaveAs ThisWorkbook.Path & "\Salaries 2003.ppt"
'Tidy up object variables
Set pptSlide = Nothing
Set pptBullets = Nothing
Set gphChart = Nothing
Set gphData = Nothing
'Close the presentation
pptPres.Close
Set pptPres = Nothing
'Close PowerPoint
pptApp.Quit
Set pptApp = Nothing
'Display confirmation message
MsgBox "Salary Summary Presentation Generated OK."
End Sub

Outlook

Outlook behaves quite differently to the rest of the Office applications. It only allows one instance to be open at any time and doesn't use the 'document' concept. Instead, it stores all its data in a single data file, represented by a Namespace object. The data file is internally structured as multiple folders that each contain a specific category of information, such as e-mails, contacts, appointments and so on.

Application

The Outlook Application object provides access to the data store through the GetNamespace property and allows us to easily create new data items (e-mails, contacts, appointments and so on) using the CreateItem method.

Namespace

The Namespace object represents an Outlook data store. Outlook was originally designed to support multiple types of data store, but only one was ever implemented. That is called the MAPI data store and is retrieved using Application.GetNamespace("MAPI"). The Namespace object acts as a container for all the Outlook folders, enabling us to navigate the entire folder hierarchy. It also provides the GetDefaultFolder() property to access each of the top-level folders.

MAPIFolder

The MAPIFolder object represents a single Outlook folder, such as the Inbox, Contacts or Calendar folder. It has a Folders property that returns a collection of child folders, enabling us to drill down and an Items property that returns a collection of all the individual items (e-mails, contacts and so on) contained in the folder.

AppointmentItem, ContactItem, DistributionListItem, JournalItem, MailItem, NoteItem, PostItItem and TaskItem

These objects represent the individual items within an Outlook folder. We can access them through the Items collection of a MAPIFolder and create them by using either the Add method of an Items collection or the CreateItem method of the Application object. In either case, we get an empty object of the appropriate type which we populate and save to the data store.

Example

The procedure shown in Listing 18-14 retrieves all the holidays for a specified year from the default Outlook Calendar, displaying them in an Excel worksheet.

Listing 18-14. Retrieving Holiday Dates from the Outlook Calendar

Sub OutlookRetrieveHolidays()
'Outlook objects
Dim olApp As Outlook.Application
Dim olNS As Outlook.Namespace
Dim olItems As Outlook.Items
Dim olAppt As Outlook.AppointmentItem
Dim bCreated As Boolean
Dim lYear As Long
Dim lRow As Long
'Obtain a reference to Outlook
On Error Resume Next
Set olApp = GetObject(, "Outlook.Application")
On Error GoTo 0
'If Outlook isn't running, start it and remember
If olApp Is Nothing Then
Set olApp = CreateObject("Outlook.Application")
bCreated = True
End If
'Get the MAPI namespace
Set olNS = olApp.GetNamespace("MAPI")
'Get the items in the Calendar
Set olItems = olNS.GetDefaultFolder(olFolderCalendar) _
.Items
'Clear the destination range if previously used
wksOutlook.Range("tblStart").CurrentRegion.ClearContents
'Get the year criteria
lYear = wksOutlook.Range("Year").Value
'Set the default row counter
lRow = 1
'Loop through the calendar entries
For Each olAppt In olItems
'We only want holidays...
If olAppt.Categories = "Holiday" Then
'... with a title ...
If Len(olAppt.Subject) > 0 Then
'... that start in the given year ...
If Year(olAppt.Start) = lYear Then
wksOutlook.Range("tblStart") _
.Cells(lRow, 1).Value = olAppt.Subject
wksOutlook.Range("tblStart") _
.Cells(lRow, 2).Value = olAppt.Start
lRow = lRow + 1
End If
End If
End If
Next olAppt
'Sort the holidays by date
With wksOutlook.Range("tblStart").CurrentRegion
.Sort key1:=.Cells(1, 2), order1:=xlAscending, _
Header:=xlNo
End With
'Clear intermediate Outlook object variables
Set olAppt = Nothing
Set olItems = Nothing
Set olNS = Nothing
'Close Outlook if we started it
If bCreated Then olApp.Quit
'Clear the Outlook application variable
Set olApp = Nothing
End Sub

Further Reading

The Office Developer Center on the MSDN Web site should be your first point of call to learn about programming the Office applications. Start at