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:
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.
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:
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) = "http://wombat.sharepointsite.com" 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:
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) = "http://wombat.sharepointsite.com/_vti_bin" 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:
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:
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:
5.6.9. Unlinking, Unlisting, and Deleting
Use these ListObject methods to unlink, unlist, or delete a list:
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.