Excel Geeking: Using VBA And ADO To Pull Data From SharePoint Lists

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.

 

Advertisements

32 thoughts on “Excel Geeking: Using VBA And ADO To Pull Data From SharePoint Lists

  1. This is timely for me. I’m looking at how to store some information that might change occasionally to support a spreadsheet I’m developing. One thought was to put it in another spreadsheet. I would like the data to be available outside our network, but I haven’t had the best luck with a spreadsheet data source when Excel thinks it is working across the internet (using MSQUERY and ODBC). Putting Access into the mix adds another layer, plus I’m not that comfortable with it yet. I also would prefer that a non-technical person could make adjustments to the data. I can just put the data directly in the application, but then we will eventually get multiple pricing versions.

    So that leaves a Sharepoint list. What I have in mind has simple enough data that it should work. It also has an advantage that if this application is useful, I’d like to port it to a web based front end rather than a macro driven spreadsheet. Sharepoint might work to host it, provided we can access the site with tablets.

    I have a couple questions. I’d like my application to run the query on opening the file. What happens if our Sharepoint server isn’t available at that moment? Do I have to trap any errors?

    Does this same technique work in reverse? Can I set up a Sharepoint list to store data from my spreadsheet? That would solve a second dilemma I’ve been working through.

    1. Scott

      Hi Omar-
      Regarding unavailable SharePoint servers, I’d say yes, you definitely want to trap the errors. It always good to trap errors anyway. I don’t know what error you’ll get if the server is unavailable, but it would probably be pretty easy to test it. But, yes, trap ’em.

      Regarding other SQL statements, I honestly don’t know. I would think they might, but I’d have to test it out. My feeling when I first wrote the above code was that applications would only ever need to draw data out of a SharePoint list. Any data entry into the list would be done in SharePoint itself by someone with rights to do so. However, if you do decide to push data in, you’d have to test it out and see if it bombs on you. That said, if you think you might be doing a lot of pushing of data, it’s probably better to learn a bit more about Access and us it instead. It will be a little more stable and perform like a database should, rather than trying to use SharePoint for a use it clearly wasn’t intended.

  2. Thanks for the input, Scott.

    I’ll look into my options for trapping the errors. I think the issue will only come into play if the user doesn’t have internet access at the time. If I go with uploading the records to Sharepoint, then we must have the access to even work. This is new territory for me, so I’ll have to do some thinking about whether I really want to do this.

    The low number of records and simplicity of what I want to accomplish (storage wise) may well work in a Sharepoint environment. Since I can offer access to the data outside of our firewall, that is a big advantage for me. Your post will be a big leap in my ability to test this. Otherwise, I would have had so much time invested that I would almost have had to go that path, just because!

  3. Pingback: Excel Roundup 20140519 | Contextures Blog

  4. Cain

    How did you build the SQL for your sharepoint List? What should be the table name if I am retrieving from a sharepoint list?

    1. Scott

      Hi Cain-
      The table name will be whatever the name of the SharePoint list is. So if you created a custom list of conference rooms and called it “Conference Rooms”, then the name of the table is going to be [Conference Rooms]. It should be that simple for the SQL statement.

      What you need to make sure of is that you use the GUID of the SharePoint list in the connection string for the ADODB.Connection. If you don’t get the GUID right, then even using the correct table name in the SQL won’t return anything.

  5. Shreya

    Thanks very much Scott. Yours is one of the very few articles that deals with this issue.
    Not sure if you ran across this as well but I’ve been getting the runtime error -2147467259 (80004005) and I’m not sure why? I did get the correct GUID for the list that I’m using in sharepoint and using both 2010 versions for excel and sharepoint. Any and all help would be greatly appreciated.
    Thank you

    1. Shreya

      Solved it! Apparently I needed to just reference the general sharepoint site and not the URL for the list in the sharepoint site. Works perfectly now. Thank you very much again Scott!

      1. Johannes Otte

        Hey Shreya, I also Encounter the very same error. What do you mean with General SharePoint site ?
        Anyhow, thank you guys for making all this public. I think it can help me a lot πŸ™‚

  6. Andrew Gaskell

    This worked for me first time. How unusual πŸ˜‰ I have an excel workbook tool in which I wanted to implement some version checking. I can now compare the version of the tool with the version in the sharepoint list and warn the user if there is a newer one. Nice.
    Thanks for sharing this Scott.

  7. Cliff

    I’m having an issue when I hit the SQL statement. Very basically I’m trying “SELECT * FROM [CheckLists];” to see if I can get it to open a record set, but it can’t find my list (CheckLists).

      1. Cliff Smith

        I actually got the first part to work after posting this. It can find the list but I can’t add anything to the list.

        Sub SharePointTest2()

        Dim cnn As New ADODB.Connection
        Dim rst As New ADODB.Recordset
        Dim strConn As String
        Dim strSQL As String

        strConn = “Provider=Microsoft.ACE.OLEDB.12.0;WSS;IMEX=2;RetrieveIds=Yes;” & _
        “DATABASE=http://ntr.nordam.net/distributedmacros/;LIST={D008B15E-06C1-4849-A93F-B69A7E8481DF};”

        cnn.Open ConnectionString:=strConn
        strSQL = “SELECT * FROM [Checklists];”

        rst.Open sSQL, cnn, adOpenStatic, adLockOptimistic
        rst.MoveFirst

        MsgBox rst![PartNumber] & ” ” & rst![chkPassed]

        rst.AddNew [PartNumber, chkPassed], [“myTest”, “I updated this!”]

        rst.Close
        cnn.Close

        End Sub

        That’s the code, the first part (messageboxing what is in the table works) but adding to the list does not. I can’t give you the error message at the moment because it’s completely different from what I was getting yesterday. Trying to figure out if I screwed something out or if IT somehow locked out my access to the sharepoint site (I suspect the latter).

      2. Scott

        Cliff: Looks to me like you’re trying to add something to a SharePoint list using VBA, which is actually a no-no. I wrote about how to change SharePoint lists via ADO and VBA in this post: https://scottlyerly.wordpress.com/2014/06/26/excel-geeking-using-vba-and-ado-to-change-data-to-sharepoint-lists/.

        What’s interesting, though, is that you’re trying to do it using the AddNew method in the recordset object, which admittedly I have not tried. I have read that in order for AddNew to work, you have to follow it with the Update method (so, think rs.AddNew, then rs.Update) otherwise the update won’t hold. I don’t see Update in your code above. If you add that to your routine, does it then work? I suspect it still won’t but it’s worth a try.

      3. Cliff Smith

        I tried out the link you posted and while your conclusion was that it couldn’t be done the only common on that page is to set your IMEX to 0 instead of 2. I tried this out and the ADO statements worked fine to insert a record into the list. I haven’t tried it yet, but I’m assuming update will work as well now.

  8. Andy

    I want to do it backwards, I have my excel and I want to upload the list into the SP, how do it do it ? is there any code

  9. Thank you for the answer!

    I actually SOLVED the issue: I was using the incorrect site name and I figured out something intresting:

    When we do a read query (e.g. SELECT), the connection uses the GUID to address the table, and not its name. You can test this by putting anything on the table name and you will see that the select query will still work – but the table name can not be empty, otherwise you get a sythax error.

    When we perform a write command, the connection uses the table name just as when we do a regular sql query and ignores the GUID. So the table name must be the name of a existing list on the site.

    So my mistake was that the site i was using was actually a subsite, so when I performed a write, I used a table name that did not exist in the parent site, because it was on the subsite.

    BTW: I have created an exportable class module for this kind of operations that really simplifies the process, so if you guys have any interest o it I could share it. Just tell me how to do it.

    Thank you again for the answers.

  10. Karen

    I know it’s been awhile on this post – but I’ll ask anyways! One of the fields in my list is a link to the users (LDAP) and it’s not pulling through the person’s name. Any way to accomplish this?

    Thanks!!

  11. Michael

    Scott, need help please on modifying the line: sSQL = “SELECT tbl.[Last Name] FROM [Employees] as tbl;”

    I’m getting the run-time error of “The Microsoft Access database engine could not find the object ‘tblContract_numbers’

    My References in Excel include Microsoft ActiveX Data Objects 2.0 Library.

    My SharePoint list is at:
    https://worksites.ad1.sfwater.org/dept/embcss/Lists/tblContract_Numbers/Allitemsg.aspx

    After creating the List, I renamed it to: Construction Contracts (Typically Design-Bid-Build)

    I’d like to get the values in the Column named: Contract Number

      1. Michael

        Yes, I tried various combinations after “SELECT” and “FROM”. In the column “Contract Number” I have values (rows) such as “HH-902” and “HH-903” and “WW-641”

        I last tried this
        sSQL = “SELECT tbl.[Contract Number] FROM [tblContract_Numbers] as tbl;”

        but the table/List object can’t be found. The error message also says “If ‘tblContract_Numbers’ is not a local object, check your network connection or contact the server administrator. Maybe I need to send my Windows login and password for this to work, which I’d rather not do.

  12. Ganesh

    Hello Scott, Thanks a lot for the post, very useful!. Like many others I am getting the error “Could not find the object ” where Object Name is the table name. I eactly used your code with no modifications, except for the sharepoint site name and list name. One quick question though, does this work on Sharepoint Online? secondly, how does the authentication work for this scenario?

  13. Ganesh

    Hi Scott, I am able to get the values but I have 2 problems.
    1. Even when the sharepoint list changes, the values are not getting updated
    2. My where query is not returning anything.

    My code is exactly same as yours except change in query, can you help?
    sSQL = “SELECT tbl.[VettingReqNo],tbl.[VesselName],tbl.[VesselImoNumber],tbl.[DischargePort1],tbl.[LoadPortRange] FROM [VettingClearanceRequests] as tbl WHERE tbl.[VettingReqNo] = “”” + ThisWorkbook.Worksheets(“Sheet1”).Range(“A1”).Value + “””;”

  14. Michael

    I got it to work (did this from my desktop logged into Windows so no password needed) with this modified code from a different website (can I share link?):
    Dim objMyList As ListObject
    Dim objWksheet As Worksheet
    Dim strSPServer As String
    Const SERVER As String = “worksites.ad1.sfwater.org/dept/embcss/”
    Const LISTNAME As String = “{4F36BC9D-A195-48C1-AF5A-977A278FE87F}”
    Const VIEWNAME As String = “{BD0D60AF-C510-4B8D-B45E-98E0962C80D4}”
    ‘ The SharePoint server URL pointing to
    ‘ the SharePoint list to import into Excel.
    strSPServer = “https://” & SERVER & “/_vti_bin”
    ‘ Add a new worksheet to the active workbook.
    Set objWksheet = Worksheets.Add
    ‘ Add a list range to the newly created worksheet
    ‘ and populated it with the data from the SharePoint list.
    Set objMyList = objWksheet.ListObjects.Add(xlSrcExternal, _
    Array(strSPServer, LISTNAME, VIEWNAME), False, , Range(“A1”))

  15. Henry

    Hi Scott,

    Thanks for the post, it will be very useful for a project I am working on. I have encountered one problem. I keep getting the error: Could not find installable ISAM. I have searched the web and found many references to it but none of them to date have resolved the issue. Do you (or anyone) have ideas/suggestions?

    Thanks,
    Henry

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s