Accessing SharePoint Lists with Visual Basic for Applications

SharePoint LogoSharePoint 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 &amp; ", " &amp; 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.

47 Comments

Add a Comment
  1. Hi… Thanks for the post. I tried to implement this code. But when I check the Request.responseText, I am getting an access denied error. Could you please help?

    1. Just guessing, but such an error usually indicates a problem with the credentials used. Do they have access to the site/list that you’re attempting to access? Does the site allow direct access via HTTP (does it need HTTPS, for example, or is there an alternative security system in place such as SiteMinder)?

      “Access Denied” is usually just that: the system is saying I COULD do this, but I WON’T do it.

  2. Thanks for the quick reply. I am using sharepoint Office 365. Looks like this code does not work in this version. Thats what i could gather from this site. http://stackoverflow.com/questions/18092122/sharepoint-claims-authentication-cookies-c-and-excel-dll-tld
    Looks like claims based authentication is causing the problem. Have you tried this code in Sharepoint 2013?

    1. Sounds like the code should work if you can integrate the Claims based authentication. I don’t have an environment to test this in, but this article seems to have the method (along with a link to a JavaScript implementation):

      http://allthatjs.com/2012/03/28/remote-authentication-in-sharepoint-online/

      I don’t have access to a 2013 SP environment – this code was tested in 2007 and 2010, but my company hasn’t upgraded to 2013. I do have my own problems with that as of right now as we’ve integrated SiteMinder authentication into the mix (something I have yet to work around – it’s MUCH more complicated than what I’m seeing of Claims-based Auth).

    2. Where you able to get it working with SharePoint online?

  3. Hello,

    I think this is a wonderful guide. Thanks for it. However, I think I’m having problems with the credentials. Where exactly, within sharepoint, can I find this information (username, password, SOAP URL, etc.)?

    Thank you.

    1. Thanks!

      These are described/explained in the “Gathering your parameters” section. “SOAPURL_List” is going to be the base URL of your site followed by “/_vti_bin/Lists.asmx” – so “http://yoursite/_vti_bin/Lists.asmx”.

      This is the web service that you’d be calling – in this case “Lists” but there are many others available.

      The username and password (in most cases) are simply your network credentials – whatever you use to log into Windows. Just a plain-jane active-directory ID. You can create a special ID/password if you like, but this often depends on company policies and standards.

      The ID must have proper permissions for what you’re trying to do – Read to access data, write to update it, etc. Basically, anything you can do in SharePoint directly when logged in with the same ID, you can do via the services.

      Hope this clarifies things,

      Good luck!

      1. Ok, thank you! I just wasn’t too sure how many directories I should go down with the URL of the SP site.

        Also, just 2 more quick questions. 1.) Under “Creating the SOAP request” section, on lines 13 and 14, is the amp; symbol meant to be included within the SOAP message? Or are you using the & symbol to concatenate? VBA is throwing an error around those.

        2.) Could you possibly provide the reference link when building the SOAP message? I would like to learn more about this (ie – how did you know what parameters to put into SOAP messages?).

        Thank you!

        1. The “amps” on those lines were just plain typos – should be simple ampersands. I’ve fixed it. Thanks for spotting it!

          The reference links I gave in the first few paragraphs gives a good start, but MS is kind of scatter-brained on this stuff. They combine the service definition with the under lying implementation, both client and server and a whole bunch of other stuff.

          If you dig, you’ll find everything, but you do have to dig. For examples of the SOAP messages specifically tho’, the simplest way is to hit the URL of the service directly in a browser. It will return a list of methods and examples of the SOAP messages to use:

          http://yoursite/_vti_bin/Lists.asmx

          In the end, tho’, one of the main reasons I write up anything like this is to collect it so that I can remember it myself later. If the docs were great, I would never need to. ;^)

  4. excelent, I’m very new to visual basic, is it possible to this from a Visual studio application? how?}
    thanks

    1. I’ve not implemented, well… anything in Visual Studio, so I can’t say for sure, but I don’t see any reason why it wouldn’t work as long as the MSXML libraries are available. Obviously you’ll to output the results differently (not having access to the Office object model), but I doubt that’ll be very difficult.

      I’m not sure how much help I can be with this, but I’m definitely interested in hearing how it goes!

  5. I typed this code as posted, but get the following error when I run the code 404:Bad Request. It looks liek I am not getting any response bad from the SOAP call. I am new to VBA & SOAP, so please be very detailed in your response…hopefully I will get a response!

  6. So now i get a reponse back ,but the response is just the content of the _vti_bin page…I am running the code from a word macro enabled template…not sure why I am getting the results I am getting…its doesn’t return th elist items from share point…?

    1. Still trying to get this to work…it returns NO rows! the following command -> MsgBox Request.responseXML gives me this error- “object doesn’t support this property or method”

      1. It may actually be working. “responseXML” is an XML object, not text – it can’t be output in a message box. That’s exactly the error you’d see if the process worked.

        The best way to see the results is to set a breakpoint in the editor after the call is returned and view the “locals” window. You can also output string values (and only string values) directly using the “immediate” window, but the locals window will let you navigate the whole return.

        If rows are returned, there would be multiple “z:row” elements in the return. The last piece of example code in the article demonstrates how to loop over those rows and collect the IDs of each for display. Accessing any other property would be similar.

  7. Hi Kiwidust, thanks so much for replying! Actually what it returns in the immediate window is the source code from the list services page and when I examine the variables after setting a breakpoint they are empty:)

    1. _vti_bin/owssvr.dll?Cmd=Display&List command will retrieve the list data, but I cant update/add items to the list with this command… if you can help me out I would be GREATly appreciative

    2. I’m very confused about what’s going on… maybe if you described what you’re trying to do and how? Sample code?

      Running down your comments:

      If you’re retrieving the source code of the page, it seems like you’re not running a service call, but _are_ running an http request. Still – you should only see the “instructions” page if you’re running a “get” rather than “post” request or – maybe – if you’re not sending the correct headers?

      I have no idea where “_vti_bin/owssvr.dll?Cmd=Display&List ” came from – that’s a feature that’s normally used to provide data services to Excel and the like, but is completely outside the scope of this.

      Finally I’m not sure what you mean in the last comment by “VBA Page”? You are working in an application environment, right? Word or Excel or another VBA container? None of this code is applicable for direct usage on a SP page.

  8. It’s like it doesn’t execute the command, it just spits out the source code on the list services page

  9. If you are around today, any insight would be appreciated. Do I have to include a file in my VBA page to get the SOAP services to work…?

  10. Hi Kiwidust! So I am trying to write a macro that will take data from a word doc 2013 and send it to a sharepoint list, basically the user fills out the word doc and I capture the data and update the sharepoint list items. I created a new content type in sharepoint and mapped the appropriate fields, but the document has a repeating table so I need to send this data to a sharepoint list. So I created a list in sharepoint to hold this data and I populated the list with a few dummy data items, just to see if I can actually access the list from my word doc. So i am just trying to get the list data right now, but eventually will want to update the list with the user entered data. I entered your code exactly as you posted with the appropriate modification for my system. The only thing I do different is when I look at the request I am sending it to the immediate window using this code:

  11. continued:

    Dim doc As Object
    doc.body.innerHTML = Request.responseText
    Debug.Print doc.body.inneHTML

  12. Also, all the code is written in the VBA editor of the word 2013 doc. In the VBA editors ribbon on the “Tools” tab there is a dropdown to add references….that’s what I was asking about before. Do I need to add a “references” to make the SOAP call work…?
    Also, I am working on the document on my machine not the one I added to out sharepoint server. I can accesses the_vti_/bin/Lists.asmx so I am not sure why the SOAP call doesn’t actually execute the command.

  13. continued again:
    When I examine the variables the in the VBA debugger(local window) the responseXML ReturnedRow is empty, but the
    request.status= 200
    request.statusText= ok
    request.responseText= the html of the page
    and the responsBody=
    Expression Value Type
    responseBody(1) 13 byte
    through… … …..
    responseBody(9065)

  14. kiwidust- sorry about posting that in 4 different post, but the comment section wouldn’t take all of it at once! Thanks again for you help…I really would like to get this working!
    Dawn

    1. Just wanted to update you on my “progress”. So I changed THE SOAPURL_List variable to https, and now I am getting an error ” bad Request ” …any thoughts?

      1. Cheers! I am so excited to share that I finally got this code working!!!I changed the double qoutes to single qoutes and the soap call worked!!! THANKS So MUCH FOR POSTiNG THIS YOU ARE AWesome! This is huge!

        1. Great to hear! Apologies for being so quiet – family stuff has been getting in the way of everything else for the past couple of weeks.

          Glad to have helped!

  15. Sorry to hear about the family issues : (…If you have time, could you point me to some good documentation on mapping XML in word doc to SharePoint list. Thanks again you have been a HUGE help!

  16. I’m new on VBA,
    how can I get other columns in my list.
    if I change “ows_ID” for my column name, that throw a error.

    please help.

    1. There are two ways:

      1) Use a different view. The view used determines which columns are returned.

      2) Take a look at the documentation for the “GetListItems” method:

      https://msdn.microsoft.com/en-us/library/office/websvclists.lists.getlistitems(v=office.14).aspx

      You’re looking for the viewFields parameter. If this is passed, you don’t need to pass a view (SP will ignore it anyway). Follow the format in the documentation. You may also want to set the rowLimit parameter, which determines how many rows to return.

      Hope this helps!

      1. Hi Kiwi, Have you had any luck display SharePoint rich text metadata back to a word document using quick-parts? I can create a rich text column in Sharepoint Library, but it doesn’t show up in quick parts so I can’t add it to my word doc…?
        Also, I was trying to use quick parts to display picture in a word doc, but the look-up column type does NOT show up in the quick parts either…? Any help would be appreciated.
        I found this link but it looks like they never posted a solution- https://social.technet.microsoft.com/Forums/sharepoint/en-US/3e3994da-03ac-4e42-bf3c-2aee51006de6/how-to-use-rich-text-box-in-word-templates-using-quick-parts?forum=sharepointgeneralprevious

        Thanks for your help and TIME!

        1. Honestly I’ve never actually used the services to update a Word doc – only to send data from Word to SP.

          I had a similar problem in JavaScript tho’; presenting rich text from SP in a browser. I have no idea if the same kind of thing would work, but there I ended up having to paste the text into the document (which didn’t work), then pulling the text from the document – which parsed it “on the way”. It’s really weird and counter-intuitive, but it works in the browser at least.

          Here’s the meat of the routine (which loops over each row in a result set from the web service):

          // Create a Temporary Div to store the HTML
          var TempDiv = document.createElement(“div”);
          TempDiv.innerHTML = CurVal;
          // Pull the (now parsed) value from the div
          if ( TempDiv.innerText !== undefined ) {
          Rows[Cnt].setAttribute(ColName, TempDiv.innerText); // IE
          } else {
          Rows[Cnt].setAttribute(ColName, TempDiv.textContent); // FF
          };

          As for using a picture… that’s a stumper…

          I did some searching on my own and while I didn’t find a solution, I did find a discussion on how this outright crashes in some versions:

          http://answers.microsoft.com/en-us/office/forum/office_2010-word/inserting-multi-line-text-field-quick-part-from/d9d274b4-f0db-4567-8d37-131786b2e5c3

          Apparently a patch was issued.

  17. Thanks for the input, I am not sure how to implement in VBA….everything I have read says DON”T try and parse html it will only cause you to have headaches. All the crazy tags word drops into a rich text field is CRAZY! You wold think there would be some conversion tool that can be used with all languages that will strip out the word tags…? I am not experienced enough to do that!

  18. Very nice place for noobs like me on trying to manipulate Sharepoint from Excel VBA!
    I am trying to attach a file to a list item using AddAttachment but it looks like it has specific challenges with the 64 bit encoded array. It looks like the ones looking to do this are very few so is difficult to find examples just googling it.
    It is possible to share an example of how to implement it?

    1. Honestly I’ve never done it myself. I did poke around a little in the past with doing this with JavaScript, but I never spent enough time to get a working model up.

      If I get a chance in the next few days, I’ll poke around a bit and see what I can see…

  19. Thank you!! This has given me a on-hands guide towards what I hope is a solution. I was able to successfully run through your code and access a SP Calendar from Access. (I can do this directly with built-in mechanics I know), but the problem I running into is that I need to pull the Recurring Data from a Calendar event with the fRecurrence bit set (I need to produce a report that shows the actual number of events and ‘reserved’ time for a calendar for a given time period). By default, the views presented to Access only has the fRecurrence field, the RecurrenceData which my understanding is all XML, assuming generated form the SP database on-the-fly. Because of this, you only see 1 occurance for a recurring event with start date of the first occurance and end date of the last… therefore I do not have enough info to determine the total reserved time and number of reservations.

    I see a lot out there talking about using CAML to get at the data I want, but it is never accompanied with exactly how to implement it. Would I be correct in assuming the CAML would be what you have specified as the SOAPMessage ?
    Ex. of the proper CAML query is:
    Query =

    @”

    “,

    ViewFields = ”

    “,

    ExpandRecurrence = true,

    The only other action I need to figure out is now to call your code without needing the ID password, rather let the current logged on credentials be used which have access to the view.

    Again, appreciate you taking time to detail simple SOAP call from VB!

    1. some of the query code was filtered, here’s the link that describes it better: http://sharepointserved.blogspot.com/2015/09/retrieve-recurrence-data.html

  20. Is there anyway you can get the values of a list to populate a dropdown list in word (using VBA) to allow a user to pick an item in that list as the value of the underlying lookup field. I am looking for something as follows:

    I am a lawyer (with an undergraduate degree in computer science) and for “fun” am creating a Document Management System in Sharepoint. Each document is saved with a variety of information, including, importantly for this question, client name and matter name. After including client and matter as metadata fields (columns), I would like to be able to set them from a new document macro in word. However, if I use lookup columns as the source of the values for these fields, which seems logical from an design standpoint, I cannot get them to populate, for example, a dropdown list. I have been pulling my hair out and would be forever thankful.

  21. Hi,

    I just tried to explain about Way to Access SharePoint List Data using Excel VBA Programming.

    Might it will help you.

  22. “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”

    Have you made that article yet? Or where can see the reference to parsing the XML response?

    More importantly, how would I be able to create a list item from an excel macro?

    1. Unfortunately, no – I had thought the project that I was using this in would be extended, but it wasn’t; I never actually got around to building the display end. Still, accessing the XML isn’t too difficult – here’s a snippet from something I had lying around:

      ' 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")

      ... Whatever processing you want ...

      Next

      A few “gotchas” to watch out for:

      • Each row returned is in a “z:row” element – so doing a”for each” over that element will loop over all returned rows.
      • Each column of data is represented as an attribute within that z:row element.
      • Each column name is prepended with “ows_”, thus in the example the “ID” column is referenced as “ows_ID”.

      Once you’re looping over the rows of data, you can do or use them for whatever you like.

      As for updating a list, that’s handled with the “UpdateListItems” method. Here’s a bit of framework based on the code from the article:

      ' Create SOAPForUpdate
      Dim SOAPForUpdate As String
      SOAPForUpdate = SOAPEnvelope_Pre & _
      " <UpdateListItems xmlns=""http://schemas.microsoft.com/sharepoint/soap/"">" & _
      " <listName>" & SOAPListName & "</listName>" & _
      " <updates>" & _
      " <Batch OnError=""Continue"">" & _
      SOAPBody & _
      " </Batch>" & _
      " </updates>" & _
      " </UpdateListItems>" & _
      SOAPEnvelope_Pst

      ' Update List
      Request.Open "POST", SOAPURL_List, False, CurUserName, CurPassword
      Request.setRequestHeader "Content-Type", "text/xml; charset=utf-8"
      Request.setRequestHeader "SOAPAction", SOAPAction_ForUpdate
      Request.Send (SOAPForUpdate)

      Here’s the MS docs with some example XMLs:

      https://msdn.microsoft.com/en-us/library/lists.lists.updatelistitems(v=office.12).aspx

      Hope this helps!

      1. Hey! Thanks so much for answering! I did eventually get it working. One thing I changed though is I’m using the MSXML library to build the xml to send. It’s a little easier to add nodes dynamically that way.

        Two questions though. First, when I get a response the getElementsByTagName method doesn’t work on any of the nodes except z:row. Any idea why? I think it might have something to do with the namespace but I don’t really know xml at all so I’m not sure. For instance:

        If i say xml.getElementsByTagName(“Field”) it will return as empty. Only two it works with are soap:Envalope and soap:Body.
        So to get to Field I need to say soapEnvalopeNode.ChildNodes(0).ChildNodes(0).ChildNodes(0) and so on. Any idea why?

        Secondly, sort of unrelated but do you have any experience with doing this in VB.net instead? I want to distribute this to others which might be easier in VB.net, and it might be easier to interface with SharePoint? I did some research but honestly haven’t found a simple tutorial like this one anywhere that uses the .net client API for SharePoint. Any suggestions by any chance?

      2. Sorry, after the “For instance:” I posted some XML but it didn’t show up… See below:

      3. Okay I have no idea how to post code in these comments… and I can’t edit. So.. imagine an excel file with nodes like:
        soap:Body
        soap:Envalope
        GetListAndViewResponse
        GetListAndViewResult
        ListAndView
        List
        Fields
        Field
        Field

Leave a Reply

DepressedPress.com © 2014 Frontier Theme