Section 5.6. Programming SharePoint in VBA

5.6. Programming SharePoint in VBA

Many Excel users write macros in VBA. If you're not one of those power users, please skip the rest of this chapter. If you know VBA, you'll find the following tasks helpful when working with SharePoint.

5.6.1. Creating Workspaces

Use the Workbook object's SharedWorkspace property to work with shared workspaces in Excel. The SharedWorkspace property returns a SharedWorkspace object that you use to share the workbook, update the workbook, and navigate among other elements in the shared workspace. For example, this code uses the SharedWorkspace object's CreateNew method to create a new shared workspace and add the current workbook to it:

 ThisWorkbook.Save     ThisWorkbook.SharedWorkspace.CreateNew "http://wombat1", _         "Team Wombat" 

You must save the workbook before adding it to a shared workspace, otherwise the CreateNew method will fail. The preceding code adds the current workbook to the SharePoint site on the Wombat1 server. If you click on Open site in browser from the Shared Workspace task pane, Excel displays the new workspace site created at http://wombat1/Team%20Wombat.

If you call CreateNew again, Excel will create another new SharePoint site and increment the site name to http://wombat1/Team%20Wombat(1). To add a workbook to an existing SharePoint site instead of creating a new site, follow these steps:

  1. Open an existing document from the SharePoint site .

  2. Get a reference to that document's SharedWorkspace object .

  3. Add your workbook to the SharedWorkspace object's Files collection .

  4. Close the document you opened in step 1 .

  5. Close the workbook you just added and reopen it from the SharePoint site .

The following code demonstrates how to add a workbook to an existing SharePoint site. The file Blank.xls is simply an empty workbook used to get a reference to the SharePoint site:

 Set wb = Application.Workbooks.Open("http://wombat1/Team Wombat/Shared Documents/ &_         Blank.xls")     If wb.SharedWorkspace.Connected Then         wb.SharedWorkspace.Files.Add ThisWorkbook.Path & "\" & ThisWorkbook.Name     End If 

Steps 4 and 5 are not shown in the code above because they require some explaining. Even though you have added the workbook file to the SharePoint site, the currently open workbook is the local version, not the shared version. You can't close the current workbook from code and then open it from the SharePoint site for two reasons: the code stops running the moment you close the current workbook, and you can't have two workbooks with the same name open at the same time.

There are a number of ways to work around this: you can save the shared workbook with a different file name, you can share the workbook from code running outside of the current workbook, or you can simply display the SharePoint site and allow the user to reopen the shared workbook from there. The following code demonstrates that last approach:

 If MsgBox("Click Yes to close this workbook " & _       "and then open the workbook from the SharePoint site.", vbYesNo, _       "Workbook added to shared workspace.") = vbYes Then         ' Open the SharePoint site in IE.         ThisWorkbook.FollowHyperlink wb.SharedWorkspace.url, , True         ' Close the temporary workbook.         wb.Close         ' Close this workbook.         ThisWorkbook.Close     End If 

Now if the user clicks Yes, Excel displays the SharePoint web site and closes the current and temporary workbooks.

You can tell if a workbook belongs to a shared workspace by checking the Connected property. You should make sure the Connected property is true before using SharedWorkspace methods ; otherwise an error may occur.

5.6.2. Opening Workbooks from a Shared Workspace

To open a workbook from a shared workspace in code, simply use the Workbooks collection's Open method with the address of the workbook from the SharePoint site. For example, the following code opens a workbook from the http://wombat1/Team Wombat/ site:

 Application.Workbooks.Open "http//wombat1/Team Wombat/Shared Documents/temp.xls" 

To check a file out from code, use the Workbook object's CanCheckOut property and the CheckOut method. For example, the following code attempts to check out a file, and if it is successful it opens the file in Excel:

 fil = " http//wombat1/Team Wombat/Shared Documents/temp.xls"     If Application.Workbooks.CanCheckOut(fil) Then         Application.Workbooks.CheckOut fil         Set wb = Application.Workbooks.Open(fil)         MsgBox wb.Name & " is check out to you."     End If 

The CheckOut method doesn't open the workbook, so you need to add the Open method as shown above. Checking a file in automatically closes the file as shown here:

 Set wb = Application.Workbooks("temp.xls")     If wb.CanCheckIn Then         ' CheckIn closes the file.         wb.CheckIn True, "Minor change"         MsgBox "File was checked in."     Else         MsgBox wb.Name & " could not be checked in."     End If 

In some cases, a file may not be able to be checked in. For instance, you can't check in the current workbook from within its own code:

 If ThisWorbook.CanCheckIn Then ' Always False! 

In such cases, you can display the SharePoint site to provide a way to check the workbook back in.

5.6.3. Removing Sharing

There are two levels of removing sharing from a workbook stored in a shared workspace. You can:

  • Delete the file from the SharePoint server. This method breaks the connection that other users share.

  • Disconnect the file from the shared workspace. This method only breaks the connection between the local copy of the workbook and the shared workbook.

Use the RemoveDocument method to delete the current document from the shared workspace as shown by the following code:

 If ThisWorkbook.SharedWorkspace.Connected Then _         ThisWorkbook.SharedWorkspace.RemoveDocument 

The preceding code leaves intact local copies that users have downloaded from the shared workspace, but all become disconnected since the shared workbook no longer exists. Alternately, you can leave the workbook in the shared workspace and disconnect only your own local copy with this code:

 If ThisWorkbook.SharedWorkspace.Connected Then _         ThisWorkbook.SharedWorkspace.Disconnect 

Now, your local copy can no longer be updated from or send updates to the shared workbook. If you want an updatable copy, you must reopen the workbook from the shared workspace.

You can also use the Files collection to remove workbooks from a shared workspace. This technique works well if you want to remove a file other than the current workbook. For example, the following code removes Security.xls from the current workbook's shared workspace:

 Dim file As Office.SharedWorkspaceFile     If ThisWorkbook.SharedWorkspace.Connected Then         For Each file In ThisWorkbook.SharedWorkspace.Files             If InStr(1, file.urlThisWorkbook, "security.xls") Then _                 file.Delete         Next     End If 

In the preceding case, you need to locate the file to remove using the Instr function because the Files collection doesn't provide an indexer to locate the file by name.

5.6.4. Responding to Updates

The Workbook object provides events you can use to respond to user actions. In order to use these events, write your code in the ThisWorkbook class of the workbook (in the Visual Basic editor, double-click on ThisWorkbook in the Project window). Visual Basic displays the Workbook events in the event list at the top of the Code window.

Use the Sync event to respond to document updates from SharePoint:

 Private Sub Workbook_Sync(ByVal SyncEventType As Office.MsoSyncEventType)     End Sub 

By default, document updates occur automatically every 10 minutes and any time a linked file is opened or closed.

5.6.5. Creating a List

Use the Add method of the ListObjects collection to create a list in code. The ListObjects collection is exposed as a property of the Worksheet object. The following code creates a new list for all the contiguous data, starting with the active cell :


Use the Add method's arguments to create a list out of a specific range of cells . For example, the following code creates a list out the range A1:C3:

 Dim ws As Worksheet     Dim rng As Range     Set ws = ThisWorkbook.Sheets("Sheet1")     Set rng = ws.Range("A1:C3")     ws.ListObjects.Add xlSrcRange, rng 

When Excel creates the list, it automatically adds column headings to the list, either by converting the first row into column headings or by adding a new row and shifting the subsequent data rows down. It's hard to determine exactly what will happen because Excel evaluates how the first row was intended. You can avoid this unpredictability by supplying the HasHeaders argument:

 Set rng = ws.Range("A2:C4")     ws.ListObjects.Add xlSrcRange, rng, , xlNo 

The preceding code adds headers to the second row and shifts the range down a row.

Lists always include column headers. To avoid shifting the range down one row each time you create a list, include a blank row at the top of the source range and specify xlYes for HasHeaders :

 Set rng = ws.Range("A1:C4")     ws.ListObjects.Add xlSrcRange, rng, , xlYes 

Since column headers and new rows added to a list cause the subsequent rows to shift down, it is a good idea to avoid placing data or other items in the rows below a list.

When creating lists in code, it is also a good idea to name the list so that subsequent references to the list can use the list's name rather than its index on the worksheet. To name a list, set the Name property of the ListObject :

 Dim lst As ListObject     Set rng = ws.Range("A1:C4")     Set lst = ws.ListObjects.Add (xlSrcRange, rng, , xlYes)     lst.Name = "Test List" 

You can get a reference to a named list using the Worksheet object's ListObjects property:

 Set ws = ThisWorkbook.Worksheets("Sheet1")     Set lst = ws.ListObjects("Test List") 

5.6.6. Sharing a List

Once a list exists on a worksheet, you can share that list using the Publish method. The first argument of the Publish method is a three-element string array containing the address of the SharePoint server, a unique name for the list, and an optional description of the list. For example, the following code publishes the list created in the preceding section:

 Set lst = ws.ListObjects("Test List")     Dim str As String     Dim dest(2) As Variant     dest(0) = ""     dest(1) = "Test List"     dest(2) = "A description goes here..."     str = lst.Publish(dest, True)     MsgBox "Your list has been shared. You can view it at: " & str 

The Publish method returns a string containing the address of the published list. The preceding code displays that address in a message box, but you may want to navigate to that address or include a link to it somewhere on the sheet. To add a hyperlink to the list on the SharePoint server, add a hyperlink to a range:

 Dim lnk As Hyperlink     Set lnk = ws.Hyperlinks.Add(Range("A6"), str) 

After adding the hyperlink, you can display the Web page for the list by using the Follow method:


To navigate to the list without adding a hyperlink, use the FollowHyperlink method:

 ThisWorkbook.FollowHyperLink str 

The ListObject 's SharePointURL property returns the address of the list, so it is easy to get the address of the shared list after it has been created, as shown here:

 str = ws.ListObjects("Test List").SharePointURL     Set lnk = ws.Hyperlinks.Add(Range("A6"), str, , _       "Click to display list site.", "View") 

5.6.7. Inserting a Shared List

Once a list is published on a SharePoint site, you can insert that list into other worksheets using the ListObjects Add method and the SourceType argument xlSrcExternal :

 Set ws = ThisWorkbook.Worksheets("Sheet2")     Dim src(1) As Variant     src(0) = ""     src(1) = "0B803D34-FDA7-4E2D-A341-D1CF7FE95DE9"     ws.ListObjects.Add xlSrcExternal, src, True, xlYes, ws.Range("A1") 

When SourceType is xlSrcExternal , the Source argument is a two-element array containing this information:



List address. This is the SharePoint address plus the folder name /_vti_bin .


The name or GUID of the list. This is a 32-digit numeric string that identifies the list on the server.

To find the GUID of a list, view the list on the SharePoint server and choose Modify Columns and Settings on the list's web pagethe GUID of the list is displayed as part of the URL in the Address bar of the browser.

5.6.8. Refreshing and Updating

Use the ListObject 's Refresh method to discard changes to the list on the worksheet and refresh it with data from the SharePoint server as shown here:


Use the UpdateChanges method to send data from the worksheet list to the SharePoint server and retrieve new and changed data from the SharePoint server:

 lst.UpdateChanges xlListConflictDialog 

As mentioned earlier, if two authors modify the same item in a list, a conflict will occur when the second author updates his or her list. The iConflictType argument determines what happens when a conflict occurs. Possible settings are shown here:



xlListConflictDialog (the default)

Conflict displays dialog.


Worksheet data wins conflict.


Server data wins conflict.


Conflict causes error.

5.6.9. Unlinking, Unlisting, and Deleting

Use these ListObject methods to unlink, unlist, or delete a list:


Use to


Remove the link between the worksheet list and the SharePoint list.


Convert the worksheet list to a range, preserving the list's data.


Delete the worksheet list and all its data.

Once you have unlinked a list, you can't relink it. To reestablish the link, you must delete the list and insert it back onto the worksheet from the SharePoint list.

5.6.10. Resources

To get

Look here

Office 2003 web components

Search for "Office Web Components."

Information on Office 2003 web component licensing;en-us;828949

Office 2003 web parts

Search for "Office Web Parts."

Help on the Office Spreadsheet web part

C:\Program Files\Common Files\Microsoft Shared\Web Components\11\1033\OWCRSS11.CHM

Programming information on the Excel ShareWorkspace objects

C:\Program Files\Microsoft Office\OFFICE11\1033\VBAOF11.CHM

Essential SharePoint
Essential SharePoint 2007: A Practical Guide for Users, Administrators and Developers
ISBN: 0596514077
EAN: 2147483647
Year: 2005
Pages: 153
Authors: Jeff Webb

Similar book on Amazon © 2008-2017.
If you may any questions please contact us: