That’s it! If all has gone well, you will have accessed SharePoint data directly within your application! tRequestHeader "Content-Type", "text/xml charset=utf-8" Request.Open "POST", SOAPURL_List, False, CurUserName, CurPassword Set Request = CreateObject("MSXML2.ServerXMLHTTP.6.0") This is much simpler if you have the option, but will lock the interface while the request is in progress. 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). One sets the content type and the other provides the SOAPAction that you determined earlier. Two custom HTTP headers are set both are required. You’ll first create an instance of the object, populate it and then send it. There are many versions of this object, going back several years, but I’ve found this one to be stable and thread safe. We’ll be leveraging the HTTP request object available in the MSXML package (which should be available by default). Now that the SOAP packet is complete, we can finally make our actual HTTP call. 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:ĭim SOAPEnvelope_Pre As String, SOAPEnvelope_Pst As String In our case we’re using the “GetListItems” method to retrieve everything from the selected view. The body of the packet contains the action-specific information. I create “bookends” for the packet: a “Pre” and “Post” block that can be reused by any service call. This makes maintenance much easier to understand. I highly recommend that you take a divide and conquer approach and segment the message into smaller, reusable sections. As this is a standard web service call, well be constructing a SOAP message. Next you’ll need to prepare the request itself.
Once your parameters are set, you’ll create the actual SOAP packet representing the request. I recommend that you paramatize this information into variables like so:ĭim CurUserName As String, CurPassword As Stringĭim SOAPURL_List As String, SOAPListName As String, SOAPViewName As String We want to fetch data from a list, so will be using the “ GetListItems” action which is, in full: “ “
You’ll be started inside a new Sub procedure – the code samples below would be entered here. Type in a name for your macro and press the “Create” button.
These examples were developed against SharePoint 2010 and MS Office 2010, but should also work in the 20 versions.Ī 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”. 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. This method can be used in any application that supports VBA. For those, like me, that lack SharePoint Designer access, this is often the only method available. 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. 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.