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 need the “SOAPAction” to perform, which will depend on the service being used and the action being performed.You need the URL of the list service itself.The view used will determine which columns are returned in your request. You need the unique identifiers for the list and view that you’ll access.You must have permission to access the site and list, so you’ll need a valid username and password.First, you’ll need to gather some information required by the process: 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.
#ACCESS MICROSOFT VISUAL BASIC FOR EXCEL CODE#
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.
#ACCESS MICROSOFT VISUAL BASIC FOR EXCEL HOW TO#
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.