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:

could not delete

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:

opertation must be

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.

 

Advertisements

13 thoughts on “Excel Geeking: Using VBA And ADO To Change Data to SharePoint Lists

Add yours

    1. I made an oData query builder in Word 2013 for reporting purposes on SharePoint and Project Online data collections. Part of the solutions is saving the querystring in a SharePoint list. With the IMEX setting writing to the Sharepiont list works like a charm. Thanks!

  1. I just put together a quick test, changing the IMEX setting to 0. (Not that I don’t believe what CS said, but I need to see these things for myself.) It did indeed work, at least for a DELETE. I can assume, based on the other comments here, that it works for INSERT and UPDATE as well.

    Which means I really need to update this post…

    Thanks for the feedback guys!

  2. Actually you can do it , it is using one of your codes (To pull info from the SP) backwards, and I can do it 1 by 1 , but I cant do it with a list, lets share info !

    Const sDEMAND_ROLE_GUID As String =

    Const sSHAREPOINT_SITE As String =

    Dim cn As ADODB.Connection

    Dim sConn As String

    Dim sSQL As String

    Dim V1, V2, V3, V4, V5, V6 As String ‘columns to be affected

    Dim Fila As String

    If Listado = Empty Then
    MsgBox “Please complete Mandatory Spaces”

    Else
    sConn = “Provider=Microsoft.ACE.OLEDB.12.0;WSS;IMEX=0;RetrieveIds=Yes;” & “DATABASE=” & sSHAREPOINT_SITE & “;” & “LIST=” & sDEMAND_ROLE_GUID & ” ;”

    Set cn = New ADODB.Connection

    INFO = NAME OF COLUMN’ EXAMPLE “name.value”

    V1 = “‘” & INFO & “‘”
    V2 = “‘” & INFO . & “‘”
    V3 = “‘” & INFO & “‘”
    V4 = “‘” & INFO & “‘”
    V5 = “‘” & INFO & “‘”
    V6 = “‘” & INFO & “‘”

    sSQL = “INSERT INTO (Place the info)
    VALUES (” & V1 & “,” & V2 & “,” & V3 & “,” & V4 & “,” & V5 & “,” & V6 & “);”

    With cn

    .ConnectionString = sConn

    .Open

    .Execute sSQL, , adCmdText

    .Close

    End With

  3. Hi!

    I’m able to perform a select from excel VBA to sharepoint 2013. It works just fine.

    But when I try to make an update, it returns the following error:
    _________________
    The Microsoft Access database engine could not find the object ”. Make sure the object exists and that you spell its name and the path name correctly. If ” is not a local object, check your network connection or contact the server administrator.
    _________________
    The 1st thing to notice is that the object is empty!, but the parameter in the query exists… The list name is [IP Addresses] and I pass it as a parameter.

    Let me show some of my code:

    Connection string:

    connectionString = “Provider=Microsoft.ACE.OLEDB.12.0;WSS;IMEX=0;RetrieveIds=Yes;” & _
    “DATABASE=” & Me.URL & “;” & _
    “LIST=” & Me.GUID & “;”

    Query:

    “UPDATE [IP Addresses] SET [IP Addresses].[IN USE] = ‘YES’ AND [IP Addresses].[HOSTNAME] = ‘A00001PWEB1’ WHERE [IP Addresses].[IP] = ‘1.1.1.1’;”

    As you can see, the IMEX is set as zero, so it should work but it doesn`t…

    Can anyone give me a hand?

  4. Hi, guys!

    I`ve tried everything to do insert data on the sharepoint list from vba. I`ll show some of my coding here so, if I`m lucky, you guys could check it out and find out the issue.

    ————————
    Connection:

    Set sharepointConnection = New ADODB.Connection
    connectionString = “Provider=Microsoft.ACE.OLEDB.12.0;” & _
    “WSS;” & _
    “IMEX=0;” & _
    “RetrieveIds=Yes;” & _
    “DATABASE=” & Me.Url & “;” & _
    “LIST=” & Me.GUID & “;”
    LOG_MODULE.LOG_PRINT function_name, “Opening Connection”

    With sharepointConnection
    .connectionString = connectionString
    .Open
    End With
    —————————-
    Record_Set:

    Set sharepointConnection = Connect()
    Set sharepointRecordSet = sharepointConnection.Execute(Query) ‘ The error happens here!!!!
    —————————–
    Query:

    “UPDATE [IP Addresses] as tbl SET tbl.[IP] = ‘2.3.4.5’ where tbl.[HOSTNAME] = ‘AAAA’;”
    —————————
    Error:

    The Microsoft Access database engine could not find the object ”. Make sure the object exists and that you spell its name and the path name correctly. If ” is not a local object, check your network connection or contact the server administrator.

    BTW: I’ve tried other ways to connect- using the ADODB.Connection methods -, but none worked as well. The .Execute method works fine for the “SELECT” query.

    1. If you’re getting an error that says Microsoft Access engine couldn’t find the object, my guess, and it’s only a guess, is that there is an error somewhere in the SQL statement itself. That somewhere in the SQL you have a bad name for the table perhaps…

  5. Hi by using IMEX = 0 am able to insert data into sharepoint site but there is one issue for which am not able to find the workaround solution.If any of you experts can check and comment on the same then that would be really great.

    I have a field [MemberName] (type = Person or Group) in my Sharepoint List [Member Tracker] which is referring field [ID] from another table [MemberInfo] . [MemberName] is a dropdown list in the sharepoint containing all the member names and the list is quite huge which is why it is using lookup. I was trying to pass the name ‘Chan,Steve’ from excel but it is giving a data type error since the field MemberName contains unique ID for each name and not the actual name. I tried below query but it doesn’t work. I don’t know how to establish connection for the lookup table MemberInfo. Can somebody please help ?

    sSQL = “INSERT INTO [Member Tracker]([Member Name]) Values (Select ID from MemberInfo where Name = ‘Chan,Steve’);”

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

Blog at WordPress.com.

Up ↑

%d bloggers like this: