VBA and Sharepoint

[BSR]

Can anyone assist on VBA and Sharepoint.
I have a table on sharepoint and Excel with Roll no as unique column. Suppose, if a new column (city) is added in Excel file. I need a VBA code which will update city info in sharepoint. Your help will be appreciated. Thanks.

 

[nguyendang]

Can anyone assist on VBA and Sharepoint.
I have a table on sharepoint and Excel with Roll no as unique column. Suppose, if a new column (city) is added in Excel file. I need a VBA code which will update city info in sharepoint. Your help will be appreciated. Thanks.

The following VBA macro will update the values of columns based on a unique column (Roll no).
You can replace boilerplate text with necessary information to suit your need.
Set the reference to Microsoft ActiveX Data Objects 6.1 Library before running this macro.

Public Sub UpdateSharePointList()
    Dim objCnn As ADODB.Connection
    Dim objRs As ADODB.Recordset
    Set objCnn = New ADODB.Connection
    With objCnn
        .ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;WSS;IMEX=0;RetrieveIds=Yes;DATABASE=<sharepoint site url>;LIST=<ListId>;"
        .Open
    End With
    Set objRs = New ADODB.Recordset
    Dim arrFields(1) As Variant
    arrFields(0) = "Column1"
    arrFields(1) = "Column2"
    Dim arrValues(1) As Variant
    arrValues(0) = "ValueOfColumn1"
    arrValues(1) = "ValueOfColumn2"
    objRs.Open "SELECT * FROM [NameOfList] WHERE [UniqueColumn] = 'Value';", objCnn, adOpenKeyset, adLockOptimistic
    objRs.Update arrFields, arrValues
    If objRs.State = adStateOpen Then objRs.Close
    If objCnn.State = adStateOpen Then objCnn.Close
End Sub