Tag: sharepoint

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

June 26th, 2014
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';"

&amp;nbsp;

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()

&amp;nbsp;

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

&amp;nbsp;
```
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.
Excel Geeking: Using VBA And ADO To Pull Data From SharePoint Lists

May 14th, 2014
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.

Tag: sharepoint

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

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