I’ve been developing a SharePoint team site for my area which will house all kinds of information about the process and tools that I and my team support. I’ve been tinkering with the team site now for about two weeks. I’ll say this about SharePoint: moving around it is definitely not intuitive, and you can build some unbelievably ugly sites if you don’t know what you’re doing. But once you get the hang of it, and if you have an eye for design and layout, it can be an incredibly useful place to house, well, just about everything related to your area.
That said, I began to wonder whether I could pull data directly from a SharePoint list using ADO. It turns out, you can.
Before I jump into the “how”, I’ll offer a little background on the “why”.
I’ve got a lot of lists of data stored in an Excel file. These lists are nothing more than support data. They are used by other applications (also spreadsheets). Because Microsoft Access is not a supported architecture where I work, we’ve relied on storing data in centralized lists in Excel and using SQL in distributed templates to pull in and refresh the data.
Since I can’t use Access, and since using an Excel workbook housed in a public directory (where anything and everything can happen to it) keeps me up at night, I’ve been thinking about better ways to control these lists. Enter SharePoint.
Okay, so now comes the big caveat: SharePoint is not a database. Yes, you can house lists of data in it. Yes, you centralize the control of it. Yes, you can control permissions to it. No, it is not a proper database. So using for anything other then simple lists of data is probably a really horrible idea. (And if you want even more info on the difference between using SharePoint versus a database, read this article on msdn.)
I specialize in really horrible ideas. Which is why I dug into whether I could pull data out of SharePoint and into Excel using VBA and ADO.
That’s the background. Here’s the code:
' Some public constants, but they can be procedure level
' if you don't think you're going to need them elsewhere.
Public Const sDEMAND_ROLE_GUID As String = "{8D4E11A0-5AAD-4214-B10F-CEA5194787D2}"
Public Const sSHAREPOINT_SITE As String = "https://yoursharepointsite.sharepoint.com/agsinwhateveryoursiteis/"
Sub TestPullFromSharepoint()
' This pulls the data from a SharePoint list. Don't forget a reference
' to the MS ActiveX Objects in "References".
' Variable declarations.
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sConn As String
Dim sSQL As String
' Build the connection string: use the ACE engine, the database is the URL if your site
' and the GUID of your SharePoint list is the LIST.
sConn = "Provider=Microsoft.ACE.OLEDB.12.0;WSS;IMEX=1;RetrieveIds=Yes;" & _
"DATABASE=" & sSHAREPOINT_SITE & ";" & _
"LIST=" & sDEMAND_ROLE_GUID & ";"
' Create some new objects.
Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
' Open the connection.
With cn
.ConnectionString = sConn
.Open
End With
' Build your SQL the way you would any other. You can add a WHERE clause
' if you need to filter on some criteria.
sSQL = "SELECT tbl.[Last Name] FROM [Employees] as tbl;"
' Open up the recordset.
rs.Open sSQL, cn, adOpenStatic, adLockOptimistic
' Copy the recordset data to a worksheet.
ThisWorkbook.Worksheets("Sheet1").Range("A2").CopyFromRecordset rs
End Sub
I have to give two shout out links, without which this code would never have materialized. The first is how to find a SharePoint list’s GUID from Nick Grattan’s SharePoint blog. The second is the Connection Strings website, where I figured out how to cobble together the connection string for this post. Everything else is ADO 101.
Scott Lyerly 