Section 10.2. Using the Office Object Model

10.2. Using the Office Object Model

The Office 2003 object library includes a set of objects for working with documents stored in document workspace sites and document libraries. You can use those objects from VBA, Visual Basic .NET, or C#.

The SharePoint objects are connected to the top-level Office document in each application. For example, the following lines each get a reference to the document workspace in Excel, Word, and PowerPoint, respectively, from within VBA:

 Set wsXL = ActiveWorkbook.SharedWorkspace     Set wsWord = ActiveDocument.SharedWorkspace     Set wsPPT = ActivePresentation.SharedWorkspace 

From the document object, the SharePoint objects are organized as shown in Figure 10-1.

animal 10-1. Office objects for SharePoint Services

10.2.1. From VBA

Use the SharedWorkspace object to create, get, or delete a SharePoint document workspace site. The technique is the same for any of the supported document types. For example, to create a new workspace:

1. Save the document .

2. Call the SharedWorkspace object's CreateNew method to create the document workspace .

3. Use the document's SharedWorkspace object to add members , tasks , links, files, or folders .

Chapter 5 demonstrates performing a wide range of SharePoint tasks with an Excel workbook using the SharedWorkspace object in VBA. It also shows how to share lists between Excel and SharePoint through VBA. Table 10-3 lists frequently asked questions to provide a quick reference for VBA programming.

Table 10-3. SharePoint VBA quick reference

How do I?	Do this
Open a document library file?	Use the Open method and specify the document's web address.
Add a file to a document library?	Use the SaveAs method and save to the document library's web address.
Display the document's site?	Use the FollowHyperLink method to open document .SharedWorkspace.Url .
Include multiple documents in a shared workspace?	Open a document from the workspace and use document.S haredWorkspace.Files . Add to add the files.
Send mail to site members?	Use the Member object's Email property to get the address; then use FollowHyperLink( " mailto: " & address ).
Delete a workspace?	Notify workspace members; then use document .SharedWorkspace.Delete .
Remove a file?	Get the file from the Files collection; then use file .Delete .
Respond to update events?	Write code for the document class's Sync event.

10.2.2. From .NET

Office 2003 Professional provides primary interop assemblies (PIAs) to allow you to use the Office object libraries from Visual Basic .NET and C#. These assemblies are installed in your global assembly cache (GAC) when you install Office 2003 Professional with .NET Programmability selected (which is the default).

To use the PIAs from Visual Studio .NET:

1. Add references to the Microsoft Office 11.0 Object Library, Microsoft Excel 11.0 Object Library, and so on. The libraries are found on the COM tab of the References dialog .

2. Include Imports / using declarations for the Office.Interop and Office. Core namespaces .

3. Use the object model to get a reference to a document object .

The following .NET console application demonstrates using the Office object model to create a document workspace; add members and tasks; send mail to members; and delete the workspace:

 Imports Microsoft.Office.Interop     Imports Microsoft.Office.Core     Imports System.Reflection     ' Simple demo of using Office SharePoint objects from .NET.     Module Module1         ' Address of SharePoint site.         Const SPSite As String = "http://wombat1/"         ' From Microsoft.Interop namespace.         Dim WithEvents _wb As Excel.Workbook         ' From Microsoft.Office.Core namespace.         Dim _sp As SharedWorkspace         Sub Main(  )             OpenWorkbook(  )             GetWorkspace(  )             AddMember(  )             AddTask(  )             SendMail(  )             DeleteWorkspace(  )         End Sub         Sub OpenWorkbook(  )             ' Start  Excel             Dim xl As Excel.Application = GetObject(, "Excel.Application")             xl.Visible = True             ' Get the workbook object.             ChDir("..")             _wb = xl.Workbooks.Open(CurDir(  ) & "\xlSPDemo1.xls")         End Sub         Sub GetWorkspace(  )             ' Check if workspace exists; if it doesn't,             ' create it.             Try                 Debug.Write(_wb.SharedWorkspace.URL)             Catch ex As Exception                 _wb.SharedWorkspace.CreateNew(SPSite, "xlSPDemo")             End Try             ' Get the workspace object.             _sp = _wb.SharedWorkspace         End Sub         Sub AddMember(  )             ' Note: 'wombat1\ExcelDemo' must be a valid account.             _sp.Members.Add("[e-mail]", "wombat1\ExcelDemo", _               "Excel Demo", "Contributor")         End Sub         Sub AddTask(  )             ' Note: 'wombat1\ExcelDemo' must be a valid account.             _sp.Tasks.Add("Task1", _               MsoSharedWorkspaceTaskStatus.msoSharedWorkspaceTaskStatusInProgress, _               MsoSharedWorkspaceTaskPriority.msoSharedWorkspaceTaskPriorityHigh, _               "wombat1\ExcelDemo", _               "Some task", _               Today)         End Sub         Sub SendMail(  )             Dim toAddress As String             ' Build address string.             For Each mem As SharedWorkspaceMember In _sp.Members                 toAddress = toAddress & mem.Email & ";"             Next             ' Send mail from client.             _wb.FollowHyperlink("mailto:" & toAddress & _               "?Subject=Deleting " & _sp.URL)         End Sub         Sub DeleteWorkspace(  )             ' Delete the workspace.             _sp.Delete(  )         End Sub     End Module 

There are some tricks in the preceding code that merit some explanation. I use GetObject to connect to a running instance of Excel if it is already loaded. If Excel is not running, GetObject starts Excel. Using CreateObject or Dim As New always starts a new instance of Excel, which uses a lot of memory. I also use a TRy/Catch block to see if the workbook is already shared. The way the PIAs work, you can't use IsNothing to test whether the object exists.

You can't get help on Office objects from within Visual Studio. It's a good idea to create a shortcut to the Office VBA help files listed in Table 10-4 and open those files manually when you need reference information on Office objects.

Table 10-4. Help files for SharePoint and Office models

For help on	Open this file
SharePoint and other core Office objects	C:\Program Files\Microsoft Office\OFFICE11\1033\VBAOF11.CHM
Excel objects	C:\Program Files\Microsoft Office\OFFICE11\1033\VBAXL10.CHM
Word objects	C:\Program Files\Microsoft Office\OFFICE11\1033\VBAWD10.CHM
PowerPoint objects	C:\Program Files\Microsoft Office\OFFICE11\1033\VBAPP10.CHM

Microsoft sells a programming toolkit for working with Office 2003 from .NET called Visual Studio Tools for the Microsoft Office System (VSTO for short). VSTO provides project templates for creating document-based applications in .NET.

VSTO applications offer two key advantages over VBA-based applications. First, the application assemblies can be deployed to a trusted network address, and so can be maintained from a single location, allowing users to automatically get the latest release without having to install the assembly locally. Second, using .NET assemblies rather than VBA allows you to lock down macro security in Office, prohibiting users from running macros while still allowing automation.

However, there are also some disadvantages of using VSTO over VBA. VSTO only supports Office 2003 applications, and performance is generally slower since object access is through the .NET PIAs rather than a more direct path through COM.