Accessing SharePoint Lists with Visual Basic for Applications

SharePoint has many useful integration points with Microsoft Office out-of-the-box, but for truly deep integration you have to roll-up your sleeves and dig into some code. While there are multiple methods to do this, one of the most versatile is leveraging SharePoint’s many web services directly via Visual Basic for Applications. For those, like me, that lack SharePoint Designer access, this is often the only method available.

This method can be used in any application that supports VBA. This includes all the standard MS Office applications – Word, Excel and PowerPoint – but also other applications such as my favorite drawing suite, CorelDraw, or Autodesk AutoCAD. These examples were developed against SharePoint 2010 and MS Office 2010, but should also work in the 2007 and 2013 versions.

A full discussion of how to create and code VBA macros is beyond the scope of this article, but here are the absolute bare-bones basics: in Excel 2010 (which I’m using here) begin by selecting “View” from the Ribbon, then “Macros”, then “View Macros”. Type in a name for your macro and press the “Create” button. This will launch the VBA editor. You’ll be started inside a new Sub procedure – the code samples below would be entered here.

Gathering Your Parameters

You can use any of the many available web services in SharePoint, but we’ll be leveraging the Lists service to access the data in a SharePoint list. First, you’ll need to gather some information required by the process:

• You must have permission to access the site and list, so you’ll need a valid username and password.
• You need the unique identifiers for the list and view that you’ll access. The view used will determine which columns are returned in your request.
• You need the URL of the list service itself. Normally this is “http://yoursite/_vti_bin/Lists.asmx”
• You’ll need the “SOAPAction” to perform, which will depend on the service being used and the action being performed. We want to fetch data from a list, so will be using the “GetListItems” action which is, in full: “http://schemas.microsoft.com/sharepoint/soap/GetListItems“

I recommend that you paramatize this information into variables like so:

' Set credentials
Dim CurUserName As String, CurPassword As String
CurUserName = "UserName"
CurPassword = "Password"

' Set SOAP/Webservice Parameters
Dim SOAPURL_List As String, SOAPListName As String, SOAPViewName As String
SOAPURL_List = "http://yoursite/_vti_bin/Lists.asmx"
SOAPListName = "{xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx}"
SOAPViewName = "{xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx}"

' SOAP Action URL
Dim SOAPAction As String
SOAPAction = "http://schemas.microsoft.com/sharepoint/soap/GetListItems"

Once your parameters are set, you’ll create the actual SOAP packet representing the request.

Creating the SOAP Request

Next you’ll need to prepare the request itself. As this is a standard web service call, well be constructing a SOAP message. I highly recommend that you take a divide and conquer approach and segment the message into smaller, reusable sections. This makes maintenance much easier to understand.

I create “bookends” for the packet: a “Pre” and “Post” block that can be reused by any service call. The body of the packet contains the action-specific information. In our case we’re using the “GetListItems” method to retrieve everything from the selected view. The method also supports the addition of a CAML query to request specific data and parameters that can limit the rows or columns returned, but we’re going to stay simple:

' SOAP Envelope
Dim SOAPEnvelope_Pre As String, SOAPEnvelope_Pst As String
SOAPEnvelope_Pre = "<?xml version=""1.0"" encoding=""utf-8""?>" & _
"<soap:Envelope xmlns:xsi=""http://www.w3.org/2001/XMLSchema-instance"" xmlns:xsd=""http://www.w3.org/2001/XMLSchema"" xmlns:soap=""http://schemas.xmlsoap.org/soap/envelope/"">" & _
"<soap:Body>"
SOAPEnvelope_Pst = "</soap:Body>" & _
"</soap:Envelope>"

' Complete the packet
Dim SOAPMessage As String
SOAPMessage = SOAPEnvelope_Pre & _
" <GetListItems xmlns=""http://schemas.microsoft.com/sharepoint/soap/"">" & _
" <listName>" & SOAPListName &amp; "</listName>" & _
" <viewName>" & SOAPViewName &amp; "</viewName>" & _
" </GetListItems>" & _
SOAPEnvelope_Pst

Now that the SOAP packet is complete, we can finally make our actual HTTP call.

Calling the Web Service

We’ll be leveraging the HTTP request object available in the MSXML package (which should be available by default). There are many versions of this object, going back several years, but I’ve found this one to be stable and thread safe.

You’ll first create an instance of the object, populate it and then send it. Two custom HTTP headers are set; both are required. One sets the content type and the other provides the SOAPAction that you determined earlier. Note that the “False” in the Request.Open line sets the call to synchronous (the request will complete before moving onto the next line of the code). This is much simpler if you have the option, but will lock the interface while the request is in progress.

' Create HTTP Object
Dim Request As Object
Set Request = CreateObject("MSXML2.ServerXMLHTTP.6.0")
' Call the service to get the List
Request.Open "POST", SOAPURL_List, False, CurUserName, CurPassword
Request.setRequestHeader "Content-Type", "text/xml; charset=utf-8"
Request.setRequestHeader "SOAPAction", SOAPAction
Request.Send (SOAPMessage)

That’s it! If all has gone well, you will have accessed SharePoint data directly within your application!

Once the call is complete, you’ll be able to access the XML response as the “responseXML” property of the object. Details about accessing and parsing the data in this response will be left for a future article, but here is a small example of how you could loop over the rows of the response and create a list of the record ID’s returned:

' Init Vars
Dim IDList As String, CurID As Integer
Dim ReturnedRow

' Loop over returned rows to get keys for deletions
For Each ReturnedRow In Request.responseXML.getElementsByTagName("z:row")
' Get the Current ID
CurID = ReturnedRow.getAttribute("ows_ID")
' Create the Current Batch Part
IDList = IDList & ", " & CurID
Next

' Output the IDList
MsgBox ("IDs are: " & IDList)

You can, of course, also use breakpoints and the immediate window to explore the response in more detail.

In Conclusion

Accessing SharePoint data directly within Office documents offers near-limitless versatility. Using other services and methods you can update or create list items, access user data and group information and create or manage SharePoint calendars or alerts. Some of the projects that I’ve completed using the method are:

• Created an attractive, macro-driven monthly team schedule in Excel for various roles and responsibilities. Macros are used to bulk load data from the schedule into a SharePoint list where it  feeds various reports and displays. Data entry is significantly easier in Excel than in SharePoint.
• Created a macro in a Word document that accesses my team’s group data via the SharePoint “UserGroup” service and generates a team roster automatically based on it. Now, changes in the team makeup can be made in SharePoint and will automatically cascade to related documents.
• Created an Excel template that pulls current metric data directly from multiple SharePoint lists and uses it to populate a monthly review document.

I plan on expanding on the method above in future articles to explore how to access and use the data and how to perform similar tasks within a SharePoint page using JavaScript rather than Visual Basic.