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 C Lyerly 
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.
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.
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!
How did you build the SQL for your sharepoint List? What should be the table name if I am retrieving from a sharepoint list?
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.
Thank you for your article, tremendous help!
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
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!
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 🙂
How to find the general sharepoint site ?
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.
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).
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.
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.
That should say comment, not common.
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
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
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.
Yan,
I’m interested in your class module. Can you reply with a link to a copy of the module or post here?
Hi Yan,
I’m interested in your class module.
Where can I get it?
kind regards,
Georg
Sorry for the loooong delay. I have made a blog where the 1st post contains a link to a workbook that contains all the modules.
http://excelwithyan.blogspot.com.br/
Sorry for the loooong delay. I have made a blog where the 1st post contains a link to a workbook that contains all the modules.
http://excelwithyan.blogspot.com.br/
Hi All,
I just tried to explain about Way to Access SharePoint List Data using Excel VBA Programming.
Might it will help you.
Way to Access SharePoint List Data using Excel VBA Programming
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!!
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
Have you tried something like SELECT tbl.[Last Name] FROM [Construction Contracts] as tbl?
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.
Below is the path : http://infonet/aviva/WNSInsurance_MIS/ORG_Structure/Forms/AllItems.aspx?RootFolder=%2Faviva%2FWNSInsurance_MIS%2FORG_Structure%2FCMIS_Tool&FolderCTID=0x012000C5221E06108F224FB10CF279D66A78A4&View=%7BFA81BF18-C346-4A8E-9ECC-5A09F56FAD3E%7D
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?
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 + “””;”
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”))
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
Hi Henry,
Were you able to resolve the issue?
Hi Henry,
I have the same issue with only two of my clients. One uses Win 10 64 bit + Office 2019 64 bit and the other uses Win 10 32 bit + Office 2013 32 bit.
Did you find a solution for that?
Installing the Access Database Engine 2010 (https://www.microsoft.com/en-us/download/details.aspx?id=13255) which many web sites are claiming it as the solution and any other things didn’t work for me.
How have you dealt with the issue until now?
CC: Scott – Haven’t you had this issue at all until now?
Thanks,
Mike
I have the same issue works with Excel 365 but not with Excel 2019
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
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.
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
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?
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
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?
Im not terribly familiar with that approach but could research it.
It’s another type of SQL statement that adds records to a table. Give that a shot, see if it works.
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?