Excel Geeking: Using VBA And ADO To Change Data to SharePoint Lists

I don’t get a ton of hits on this blog. But for the hits I do get, most them are focused on the Excel stuff. And of the Excel stuff, one of the most popular is Using VBA and ADO To Pull Data From SharePoint Lists. Because of this, I felt the need for a follow-up post, a sequel as it were. In the first post, I laid out the basic code to pull data from SharePoint using VBA. Could I do the same this to push data to SharePoint?

The short answer is no.

This proved to be an interesting topic to research. There actually isn’t a lot of material written on how to use SQL, ADO, and VBA to change data in a SharePoint list. Plenty of posts on pulling data, it’s a pretty basic SELECT statement. Fair less on things like INSERT, UPDATE, and DELETE.

I started off taking the VBA from the previous post and modifying it so that it would become one of the other three. As a matter of public disclosure, I should note that I only tried this with INSERT and DELETE. I didn’t try it with UPDATE, largely because I’ve never been a big fan of UDPATE. I find it less troublesome to use DELETE/INSERT with databases, with the DELETE containing criteria managed by a WHERE clause, than I do trying to find the right keys to isolate the correct record and then fire off an UPDATE query. This probably makes me a lazy coder, but given that I’m not a DBA, when it comes to databases I’m usually flying by the seat of my pants and using Google as my flight instruction manual.

Okay, so here is the code I tried:

 

Public Const sDEMAND_ROLE_GUID As String = "InsertYourGuidHere"

Public Const sSHAREPOINT_SITE As String = "https://yoursharepointsite.sharepoint.com/agsinwhateveryoursiteis/"


Sub TestDeleteFromSharepoint()


Dim cn As ADODB.Connection

Dim sConn As String

Dim sSQL As String


sConn = "Provider=Microsoft.ACE.OLEDB.12.0;WSS;IMEX=1;RetrieveIds=Yes;" & _

" DATABASE=" & sSHAREPOINT_SITE & ";" & _

"LIST=" & sDEMAND_ROLE_GUID & " ;"


Set cn = New ADODB.Connection


sSQL = "DELETE * FROM [Demand Role] as tbl WHERE tbl.[Role]='BA';"

 

With cn

.ConnectionString = sConn

.Open

.Execute sSQL, , adCmdText

.Close

End With

Set cn = Nothing

End Sub

And the result:

What about INSERT INTO?

Sub TestInsetIntoSharepoint()

 

Dim cn As ADODB.Connection

Dim sConn As String

Dim sSQL As String


sConn = "Provider=Microsoft.ACE.OLEDB.12.0;WSS;IMEX=1;RetrieveIds=Yes;" & _

" DATABASE=" & sSHAREPOINT_SITE & ";" & _

"LIST=" & sDEMAND_ROLE_GUID & " ;"

Set cn = New ADODB.Connection


sSQL = "INSERT INTO [Demand Role] ([Role]) VALUES ('BA');"


With cn

.ConnectionString = sConn

.Open

.Execute sSQL, , adCmdText

.Close

End With

Set cn = Nothing

End Sub

 

And the result:

Based on the results above, you would think the long answer to this is idea of changing SharePoint data using VBA would be a flat no as well, right? Well, it’s kind of no. But kind of yes.

Here’s the thing with SharePoint lists: you link them to Access as tables. It’s incredibly simple to do. In Access, you go to External Data, use the drop down to choose SharePoint Lists, plug in your SharePoint site and click Next. Access will go out to SharePoint, get the available lists, and present them to you in a dialog box where you can choose whichever table you want. Once you choose the table and finish out the connection, you have a linked table in Access from SharePoint in which you can update data.

I’ll say that again. You can update your data in Access, and it updates in the SharePoint list.

Which makes the long answer less yes and no and more “kinda”. Because, while you can’t update the SharePoint list in SharePoint directly with VBA, you can certainly update the linked table in Access using VBA. But this begs the question: why? Why would you have your data resident in a SharePoint list and linked into an Access database? Why not just have it in Access? This is a question I can’t answer, maybe somebody out there wiser than I can. But for now, without going into all the nitty-gritty details about how to do it, this is the setup you need to have in order to update a SharePoint list using Excel VBA and ADO.