working with the microsoft office object models

Copyright © by Application Developers Training Company

Working with the Microsoft Working with the Microsoft Office Object ModelsOffice Object Models


ObjectivesObjectives• Learn how unmanaged objects work in a

managed code environment• Find out about the hierarchical object

models that Office products use• Learn how to respond to events in your

Office-based solutions• Discover how to control some of the most

commonly used Excel, Word, and PowerPoint objects through code

• Find out how to add and change command bars in Microsoft Office 2003 products


AgendaAgenda• COM Objects in a Managed Code

Environment• Working with Properties, Methods, and

Member Objects• Responding to Events• Using Word Objects• Working with Excel Objects• Microsoft PowerPoint Objects• Building Command Bars in Office 2003


COM Objects in a Managed Code COM Objects in a Managed Code EnvironmentEnvironment• The Office object models are huge• The more proficient you are as an Office user,

the better you'll be at creating Office solutions• All the Office object models

Are hierarchical Start with the Application object

• Remember that they're also all COM, not .NET You get to the object models through the PIAs The transition from COM to .NET isn't always

seamless


Starting at the Top: The Application Starting at the Top: The Application ObjectObject• The products we'll use here all start with

an Application object• Use it to get to everything else• VSTO 2005 SE solutions

Template does the plumbing Application object is already there because the

application launches before your code runs The templates all define an Application

variable for you


Requirements for Automation Requirements for Automation SolutionsSolutions• Set a reference to the PIA you need• Declare a variable to hold the Application

instance• (Optionally import the namespace to

make coding easier to type/read)• Write code to launch the Office product

Private app As Word.Application...app = New Word.Application



Environment• Working with Properties, Methods,

and Member Objects• Responding to Events• Using Word Objects• Working with Excel Objects• Microsoft PowerPoint Objects• Building Command Bars in Office 2003


Working with Properties, Methods, Working with Properties, Methods, and Member Objectsand Member Objects

• Properties affect the way the object looks or acts

• Methods allow you to do something with the object

• Member objects Are really properties Return other objects Used to organize objects into a hierarchy


PropertiesProperties• Programmatic way for you to change what

the user would change through the UI

app.ActiveDocument.Paragraphs(1).Bold = True

app.Range("A1:B12").Borders.LineStyle = _ XlLineStyle.xlDouble


MethodsMethods• Programmatic way for you to do

something end users would do through the UIappExcel.Quit

appExcel.Range("A1:B12").PrintOut

appWord.Quit SaveChanges:=wdSaveChanges, _ OriginalFormat:=wdPromptUser, RouteDocument:=True


Member ObjectsMember ObjectsApplication

Documents

Document

Paragraphs

Paragraph

Range

Range


Responding to EventsResponding to Events• In Office, events usually happen in

response to user action• Use an event handler to code a response

to an event• Event handlers for some objects are

available in the templates automatically Document/Workbook Open events ItemSend event

• In VB, use the WithEvents keyword to expose other events


The Word Object ModelThe Word Object ModelApplication

Documents

Document

Paragraphs

Paragraph

Range

Range


Using Word ObjectsUsing Word Objects• Largest object model in Office• Documents

You can get a reference to any open document using its name or index with the Documents collection

Two special Document objectso ActiveDocument (member of Application)o ThisDocument (member of Globals class)

Dim doc As Word.Document = _ Application.Documents("Customer.doc")


Finding Out What’s ActiveFinding Out What’s Active• ActiveDocument tells you which document

is active• ThisDocument (in document-level

solutions) tells you which document contains the code that's running

• Selection tells you where the cursor is in the document


Working with RangesWorking with Ranges• Most of the time, you can (and should)

use a Range object instead of the Selection object Doesn't change location of the cursor Easier to work with text relative to the cursor

position (paragraph before/after)• Lots of objects have a Range property

Document Selection Bookmark Footnote Paragraph, Sentence, Word, etc.


Working with RangesWorking with Ranges

ActiveDocument.Range(0,50) Start to character 50

ActiveDocument.RangeEntire Document

ActiveDocument.Paragraphs(3).Range

Third paragraph


Inserting TextInserting Text• Use the Text property of the Range to

change text

Dim rng As Word.Range = _ Application.ActiveDocument.Rangerng.Text = "Text has been inserted!"


Inserting TextInserting Text• Collapse method

Changes a Range object so it refers to an insertion point at the beginning or end of the starting range

Arguments: wdCollapseStart or wdCollapseEnd

Hello

rng

Hello

rng.Collapse Word.WdCollapseDirection.wdCollapseEnd


Inserting TextInserting Text• The Insert methods

Insert text before or after a range Insert paragraph markers Insert cross references Insert dates

• Some replace range, others append to and extend range

• Best practice: Collapse range first and then use InsertBefore or InsertAfter


Working with Excel ObjectsWorking with Excel ObjectsApplication

Workbooks

Workbook

Sheets

Charts

WorkSheets

Worksheet

ChartObjects

Chart

Chart


ActiveWorkbook and ActiveWorkbook and ThisWorkbookThisWorkbook• ActiveWorkbook

The one the cursor is in Member of the Application object

• ThisWorkbook The one the code is in Member of the Globals class in a document-

level solution Like ThisDocument, doesn't apply to

application-level solutions


ActiveSheetActiveSheet• Whatever sheet the cursor is in• Like the items in the Sheets collect, could

be worksheet or chart (or dialog/macro sheet)

• Need to cast to a Worksheet or Chart type Required for C# or VB with Option Strict On Handy even with Option Strict Off for

IntelliSenseDim wks As Excel.Worksheet = _ DirectCast(Application.ActiveSheet, Excel.Worksheet)

Dim cht As Excel.Chart = _ DirectCast(Application.ActiveSheet, Excel.Chart)


Casting in Visual BasicCasting in Visual Basic• CType

Takes care of any valid conversions for you

• DirectCast A little faster than CType if you're sure the object can be cast to the specified type

SalesChart = _ CType(Application.ActiveSheet,_ Excel.Chart)

SalesChart = _ DirectCast(Application.ActiveSheet,_ Excel.Chart)


Casting in Visual BasicCasting in Visual Basic• TryCast

Sets variable to Nothing if cast can't be done

Dim cht As Excel.Chart = _ TryCast(Application.ActiveSheet, _ Excel.Chart)If cht IsNot Nothing Then…


ActiveCell and SelectionActiveCell and Selection• ActiveCell is a single-cell range• Selection might be

A multi-cell range An item in a chart


Using Excel RangesUsing Excel Ranges• Refers to a cell or group of cells

Can refer to a single cell

Worksheets(1).Range("A1")

Worksheets(1).Range("A1","B5")

Worksheets(1).Range("A1:B5")

Can refer to multiple cells

Can refer to the corners of a range

Can refer to a named range

Worksheets(1).Range("SalesData")


CurrentRegionCurrentRegion• Property of a range• Returns a Range object bounded by

empty row and empty column• Tells you how much of the area around

the cell is filled in


The Item PropertyThe Item Property• Default property of a Range object• Refers to specific cell within a Range by

row and column coordinates• Coordinates are relative to Range, not

worksheet• Use Cells property of worksheet to specify

item relative to the worksheet


Working with a Sub-RangeWorking with a Sub-Range• Use Columns or Rows property to find

out how wide or long a range is• Build the sub-range from ranges of

starting and ending points

Dim rng As Excel.Range = Application.Range(rngStart, rngEnd)


Excel’s Chart ObjectExcel’s Chart Object

Workbook

Chart

Worksheet

ChartObjects

ChartObject

Chart


Creating Excel ChartsCreating Excel Charts• Could create a chart in one line of code

with the ChartWizard method• Unless you're very familiar with charts,

just record it In Excel, turn on the macro recorder Create the chart you want Edit the resulting VBA code to be more

efficient/generic/robust Copy into your Visual Studio project and fix

any code that doesn't translate


Microsoft PowerPoint ObjectsMicrosoft PowerPoint ObjectsApplication

Presentations

Presentation

Slides

Slide

Shapes

Shape

HasText

TextFrame

TextRange


Creating SlidesCreating Slides• The Slides.Add method has two required

arguments Index: Location of the new slide within the

presentation Layout: A member of the PpSlideLayout

enumeration

Dim sld As PowerPoint.Slide = _ pres.Slides.Add(pres.Slides.Count + 1, _ PowerPoint.PpSlideLayout.ppLayoutText)


Using the Shape ObjectUsing the Shape Object• Everything you put on a slide is a Shape

Text box AutoShape OLE object Etc.

• Exact properties supported depend on type of shape

• Unless you use the layout ppLayoutBlank, every slide you add has one or more shapes on it by default


Finding the Text PropertyFinding the Text Property• Many shapes support text, but not all• You need to check for a Text Frame before

trying to add text• Use the HasTextFrame property

Dim shp As PowerPoint.Shapeshp = sld.Shapes(1)If shp.HasTextFrame = _ Office.MsoTriState.msoTrue Then…


Finding the Text PropertyFinding the Text Property• Once you know shape has a text frame,

use it to get at the shape's TextRange property

• TextRange object is similar to Word's Range object

Dim trng As PowerPoint.TextRangetrng = shp.TextFrame.TextRangetrng.Text = "This is an example"


Finding the Text PropertyFinding the Text Property• Change text directly with Text property• Add text before or after existing text with

InsertBefore or InsertAfter methods• Other Insert methods include

InsertDateTime InsertSlideNumber InsertSymbol




Member Objects• Responding to Events• Using Word Objects• Working with Excel Objects• Microsoft PowerPoint Objects• Building Command Bars in Office

2003


Building Command Bars in Office Building Command Bars in Office 20032003• Both document-level and application-level

solutions for Office 2003 often include custom menu items and toolbars

• To add/change menus and toolbars, you use Office's CommandBars object model In VSTO 2005 SE, part of Microsoft.Office.Core

namespace CommandBars object model is still supported

in Office 2007


Creating Command BarsCreating Command Bars• Can reference and customize existing

command bars

Dim cbr As Office.CommandBar = _ Application.CommandBars("Standard")

• Can create your own command bar If you mark it as Temporary, the command bar

will be deleted when host app closes

Dim cbr As Office.CommandBar = _ Application.CommandBars.Add( _ "New Example", Temporary:=True)


Creating Command Bar Creating Command Bar ControlsControls• Controls can be one of several types

Button Edit Dropdown ComboBox Popup

• Each control type supports properties• Common properties include

Caption TooltipText OnAction


Creating Command Bar Creating Command Bar ControlsControls• In VBA, you use the OnAction property to

specify which VBA procedure to run when an event (like a button click) occurs

• In VSTO 2005 SE, you need to use a procedure that complies with the event's delegate

Delegate Sub _ _CommandBarButtonEvents_ClickEventHandler _ (Ctrl As Microsoft.Office.Core.CommandBarButton, _ ByRef CancelDefault As Boolean)


Creating Command Bar Creating Command Bar ControlsControls• To link the command bar control's event

to its handler, use the AddHandler statement

AddHandler ctl.Click, _ AddressOf NewControl_Click

• Alternatively, use the WithEvents keyword when you declare the control variable and let Visual Studio hook the control and event handler together


Command Bars in Office 2007Command Bars in Office 2007• Command bars are supported by Office

2007• Microsoft recommends you use them only

for backward compatibility• All custom command bars, as well as

controls added to built-in command bars, are grouped together in an Add-Ins tab on the ribbon

working with the microsoft office object models

Technology