Send Excel Cell Value to SharePoint List column

user_15191265 0
Viewed 214

I want a tool (possibly VBA?) to select an existing Excel file (or even multiple Excel files from a folder at once) and put the value of fixed cells into columns of a SharePoint list (for example: put the value of A1 of the Excel file into column 1 of the Sharepoint list).

Is that even possible? And if so, would VBA be a logical option to do that?

sharepoint vba excel
1 Answers

You can use ADO for this - here's an example of adding a new record to a list.

You can get the list name if you go to the list settings and look at the URL: the list guid is after the List= querystring parameter.

https://yourCompany.sharepoint.com/sites/Site1/_layouts/15/metadatacolsettings.aspx?List={af83a2e4-a2e4-4890-1111-3431bde70e5e}

Sub SPListAdd()
    
    Const SERVERUrl As String = "https://yourCompany.sharepoint.com/sites/Site1/"
    Const ListName As String = "{af83a2e4-a2e4-4890-1111-3431bde70e5e}"
    
    Dim Conn As New ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim Sql As String
    Dim objWksheet  As Worksheet
    Dim f As ADODB.Field, i As Long
    
    Set objWksheet = ThisWorkbook.Worksheets("List Items")
    objWksheet.Cells.Clear
    
    On Error GoTo ErrHand
    
    ' Open the connection and submit the update
    With Conn
        .ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;WSS;IMEX=0;RetrieveIds=Yes;" & _
                            "DATABASE=" & SERVERUrl & ";" & _
                            "LIST=" & ListName & ";"
        .Open
    End With
    
    'inserting a new record....
    Sql = "SELECT * FROM [" & ListName & "] where false" 'get empty recordset
    Set rs = New ADODB.Recordset
    rs.CursorLocation = adUseClient
    rs.Open Sql, Conn, adOpenKeyset, adLockOptimistic
    rs.AddNew
    rs.Fields("Title") = "This is a title"
    rs.Update
    rs.Close
    
    'query all records for display
    Sql = "SELECT * FROM [" & ListName & "]"
    rs.Open Sql, Conn, adOpenStatic
    If Not rs.EOF Then
        For Each f In rs.Fields
            objWksheet.Range("A1").Offset(0, i).Value = f.Name
            i = i + 1
        Next f
        objWksheet.Range("A2").CopyFromRecordset rs
    End If
    
    rs.Close
    Conn.Close
    
ErrHand:
    Debug.Print Err.Number, Err.Description

End Sub
user_4788840
Related