Add New List Item With Attachment Via Excel VBA

Question

I have users that have a schedule template in Excel (2010). I'm wondering how I can code via Excel VBA to create a new list item and attach the Excel worksheet they are using that contains the schedules into a SharePoint 2010 list.

There are only six fields that are needed for a new item and I could have VBA set those values.

Answer 1

You can use SharePoint web services.

Below is the snippet I copied from https://stackoverflow.com/questions/22450717/add-and-update-single-item-in-sharepoint-list-via-vba

Sub Add_Item(ListName As String, SharepointUrl As String, ValueVar As String, FieldNameVar As String)

Dim objXMLHTTP As MSXML2.XMLHTTP

Dim strListNameOrGuid As String
Dim strBatchXml As String
Dim strSoapBody As String

Set objXMLHTTP = New MSXML2.XMLHTTP

strListNameOrGuid = ListName


'Add New Item'
strBatchXml = "<Batch OnError='Continue'><Method ID='3' Cmd='New'><Field Name='ID'>New</Field><Field Name=" + FieldNameVar + ">" + ValueVar + "</Field></Method></Batch>"


objXMLHTTP.Open "POST", SharepointUrl + "_vti_bin/Lists.asmx", False
objXMLHTTP.setRequestHeader "Content-Type", "text/xml; charset=""UTF-8"""
objXMLHTTP.setRequestHeader "SOAPAction", "http://schemas.microsoft.com/sharepoint/soap/UpdateListItems"

strSoapBody = "<soap:Envelope xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance' " _  
 & "xmlns:xsd='http://www.w3.org/2001/XMLSchema' " _
 & "xmlns:soap='http://schemas.xmlsoap.org/soap/envelope/'><soap:Body><UpdateListItems " _
 & "xmlns='http://schemas.microsoft.com/sharepoint/soap/'><listName>" & strListNameOrGuid _
 & "</listName><updates>" & strBatchXml & "</updates></UpdateListItems></soap:Body></soap:Envelope>"

 objXMLHTTP.send strSoapBody

If objXMLHTTP.Status = 200 Then
'   Do something with response
End If

Set objXMLHTTP = Nothing

End Sub

Answer 2

I know it isn't super pretty, but I have a solution... Make sure that you replace YOURSHAREPOINTSITE with the url of your site.

The beauty of my solution, is that the code allows for:

1. Creation of new SP list
2. Addition of list items with all original column of the list
3. Addition of list items with any number of columns of the list (as long as all required columns are represented)
4. No link required for the addition of new data (does create a link when you use #1 but not a syncing link)

Limitations:

1. Column validation will cause a failed run if you pass data that shouldn't go in that column (text to number column)
2. Absent required columns cause a failed run
3. Untested with lookup, people/group, or other record related column types... but it would cause invalid data, potentially a failed run unless you input the ID of the lookup value... which you probably don't have.
4. It does require correct typing of column names and list name in input boxes...

Public Sub PushSPList()
    Dim lname As String, guid As String
    Dim arr, arrr
    Dim NewList As ListObject
    Dim L As ListObjects
    ' Get the collection of lists for the active sheet
    Set L = ThisWorkbook.ActiveSheet.ListObjects

    ' Add a new list
    If MsgBox("Have you selected the new data?", vbYesNo) = vbNo Then
        Exit Sub
    Else

        If MsgBox("New?", vbYesNo) = vbYes Then
            lname = InputBox("What is the name of your new list?")

            Set NewList = L.Add(xlSrcRange, Selection, , xlYes, True)
            NewList.Name = lname

            ' Publish it to a SharePoint site
            NewList.Publish Array("https://YOURSHAREPOINTSITE", lname), False

        Else
            arr = getSPitems
            lname = arr(2)
            guid = arr(1)

            Set NewList = L(1)
            Set arrr = Selection
            Call addSPListItem(arrr, lname, guid)

        End If
    End If

    End Sub

    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://YOURSHAREPOINTSITE"

    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


    Dim fld As Object
    Dim arrr()
    i = -1
    For Each fld In rst.Fields
    i = i + 1
    ReDim Preserve arrr(0 To i)
    arrr(i) = rst.Fields(i).Name

    Next

    Dim clmns
    clmns = Split(InputBox("Select columns, separated by commas, no spaces after commas...    " & Join(arrr, ", ")), ",")

    Dim Colmns As Object
    Set Colmns = CreateObject("Scripting.Dictionary")
    For i = 0 To UBound(clmns)
        Colmns(i) = clmns(i)
    Next

    jj = 1
    Do While rar(jj, 1)  ""
    rst.AddNew
        For kk = 0 To UBound(clmns)
        rst.Fields(Colmns(kk)) = rar(jj, kk + 1)
        Next
    jj = jj + 1
        Loop
    rst.Update

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

Answer 3

VBA A2Z has put out a series on Youtube that shows the VBA to add items to SharePoint lists and much more. Really excellent tutorials that I'm using at my company.

https://www.youtube.com/watch?v=LLxM7cekToU

Good luck.