SCOTT LYERLY

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.

 

Scott

, ,
, , , , , , , , , , , , , ,

50 responses to “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.

    Reply

    • 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.

      Reply

  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!

    Reply

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

    Reply

    • 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.

      Reply

  6. 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

    Reply

    • 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!

      Reply

      • 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 🙂

  7. 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.

    Reply

  8. 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).

    Reply

    • Cliff: I’d have to see the rest of the code to see what might be causing you issues. Feel free to post it here.

      Reply

      • 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).

      • 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.

      • 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.

      • Hello Scott,

        I use the same code but i am getting the error : Could not connect the sharepoint site, however when i use the path manually i can open the folder where i have kept access database.

        below it code i have used : Getting error on “‘ Open up the recordset.”

        Actual share point path is — http://infonet/aviva/WNSInsurance_MIS/ORG_Structure/Forms/AllItems.aspx?RootFolder=%2faviva%2fWNSInsurance_MIS%2fORG_Structure%2fCMIS_Tool&FolderCTID=0x012000C5221E06108F224FB10CF279D66A78A4

        Public Const sSHAREPOINT_SITE As String = “http://infonet/aviva/WNSInsurance_MIS/ORG_Structure/CMIS_Tool/”
        Public Const sDEMAND_ROLE_GUID As String = “{81BF18-C346-4A8E-9ECC-5A09F56FAD3E}”

        ‘Public Const sSHAREPOINT_SITE As String = “https://infonet/aviva/WNSInsurance_MIS/ORG_Structure/CMIS_Tool/Master_Data.accdb”
        ‘”https://infonet/aviva/WNSInsurance_MIS/ORG_Structure/CMIS_Tool/Master_Data.accdb”
        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 & “Jet OLEDB:Database Password=misinwns@1;”

        ‘ 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 Raw_Data.[Date],Raw_Data.[User_Name] from Raw_Data”

        ‘ Open up the recordset.
        rs.Open sSQL, cn, adOpenStatic, adLockOptimistic

        ‘ Copy the recordset data to a worksheet.
        ThisWorkbook.Worksheets(“Sheet3”).Range(“A2”).CopyFromRecordset rs

        End Sub

  9. 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

    Reply

  10. 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.

    Reply

  13. 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!!

    Reply

  14. 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

    Reply

  15. 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?

    Reply

  16. 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 + “””;”

    Reply

  17. 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”))

    Reply

  18. 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

    Reply

  19. Scott,

    I’m not sure if you monitor this thread any more, but I could use some help.

    I had this working yesterday, but today I’ve been getting the runtime error -2147467259 (80004005) and I’m not sure why?

    it is hanging up on the .open of the ADODB connection
    Any and all help would be greatly appreciated.
    Thank you

    Reply

    • Well, I’d start with the easiest stuff to check. Is the SharePoint list you we’re pulling from still there? Maybe an admin moved it by accident (or on purpose) and now your code can’t find it?

      Also, have you tried rebooting? Sometimes I’ll have been working Excel, running code so much, something gets stuck in the cache and only rebooting to clear it flushes the issue out.

      Try those first, see if they fix it.

      Reply

      • I appreciate you taking some time to help me out.

        The site is still there. Rebooting didn’t seem to help. I got it to work one time, and maybe I changed something along the way. Here is the code (up to where it has the error):

        Sub addSPListItem(rar As Variant, lnme, guid)

        Dim arr, lguid As String, spurl As String, lname As String, uitem As Object

        lguid = guid
        lname = lnme

        spurl = “https://..my sharepoint site..”

        Dim cnt As ADODB.Connection
        Dim rst As ADODB.Recordset ‘tb

        Dim mySQL As String

        Set cnt = New ADODB.Connection
        Set rst = New ADODB.Recordset

        mySQL = “SELECT * FROM [” & lname & “];”

        With cnt
        .ConnectionString = _
        “Provider=Microsoft.ACE.OLEDB.12.0;WSS;IMEX=0;RetrieveIds=Yes;” & “DATABASE=” & spurl & “;LIST=” & lguid & “;”
        .Open
        End With

        rst.Open mySQL, cnt, adOpenDynamic, adLockOptimistic

        There is more, but I believe it is not relevant. My goal is to add new items to the sharepoint list from excel and this was the best thing I found online.
        Thanks again

  21. Any idea how to perform a pull on a Person or Group field? Also, is it me or bringing up this topic anywhere on the web either scares people to not respond, the intellectuals respond with solutions totally out of scope of straight VBA/SQL, an MS Access solution is provided with a lack of detail on adding people nonexistent in the site collection, or some secret MS coding group knows the answer they just don’t want anyone to know?

    Reply

  22. I keep receiving a Row Handle Invalid @ rst.Update, is there a root common cause for this error? I’ve made some privacy edits on the below.

    Option Explicit
    Sub AddNew_ITEM()
    Dim cnt As ADODB.Connection
    Dim rst As ADODB.Recordset
    Dim mySQL As String

    Set cnt = New ADODB.Connection
    Set rst = New ADODB.Recordset

    mySQL = “SELECT * FROM [Allitems];”

    With cnt
    .ConnectionString = _
    “Provider=Microsoft.ACE.OLEDB.12.0;WSS;IMEX=2;RetrieveIds=Yes;DATABASE=https://sharepoint.com/sales/;List={BD126FC5-3180-4DBA-83DA-B6256B06F502}”
    .Open
    End With

    ‘open table / recordset
    rst.Open mySQL, cnt, adOpenDynamic, adLockOptimistic

    ‘rst.Open mySQL, cnt, adOpenForwardOnly, adLockReadOnly

    rst.AddNew
    rst.Fields(“Hotel Contact”) = Sheets(“Overview”).Range(“D3”).Text

    rst.Update

    If CBool(rst.State And adStateOpen) = True Then
    rst.Close
    End If
    Set rst = Nothing
    If CBool(cnt.State And adStateOpen) = True Then
    cnt.Close
    End If

    Set cnt = Nothing

    End Sub

    Reply

    • There’s doubtlessly a root cause for it, but I haven’t played with this code in a VERY long time, so I can’t really say for sure. But, alternatively, have you thought about trying “INSERT INTO” instead of AddNew?

      Reply

  23. Hi Scott, even 6 years later your code is proving invaluable. I even made a little improvement to place the recordset to memory, so as to manipulate before placing into worksheet. Used ‘vRecordSet = oRS.GetRows’ where vRecordSet declared as a variant. However, I have a question about the data that comes across, and this maybe an SQL issue at that. On the SharePoint list some fields are populated by a dropdown, and what comes across is a reference number(?) and not the text value selected.
    sample:
    Title Start Time End Time
    62 15/10/2020 17:30 15/10/2020 20:30
    70 14/10/2020 14:25 14/10/2020 15:25
    ‘Title’ should be a text string

    I use this as the SQL ‘sSQL = “SELECT * FROM [sTABLE_NAME];”‘ Is there a reference I can go to to see if the SQL needs to be improved to bring across the value?

    Reply

  24. Thank you so much. it works

    it might be better to edit the 2 lines for better testing

    original :
    Public Const sDEMAND_ROLE_GUID As String = “{8D4E11A0-5AAD-4214-B10F-CEA5194787D2}”
    to:
    Public Const TableName As String = “Your Table Name Here”

    original :
    ThisWorkbook.Worksheets(“Sheet1”).Range(“A2”).CopyFromRecordset rs
    to:
    ThisWorkbook.Worksheets(1).Range(“A2”).CopyFromRecordset rs
    or:
    AcitveSheet.Range(“A2”).CopyFromRecordset rs

    Reply

Leave a reply to Chuck Cancel reply