Update SharePoint List from Excel using VBA

[raghuram.sta]

Hi,

I wish I could update the Share Point List From Excel using VBA as I feel more comfortable working on Excel than Share Point.

I use below code to import the share point list data to Excel, is working fine

Sub ImportSharePointData()

    Dim objWksheet As Worksheet
    Dim CheckListObject As ListObject
    Const strSPServer As String = "ServerName/ _vti_bin"
    Const LISTNAME As String = "{69D094F2-40AD-8225-26F4260A1DDB}"
    Const VIEWNAME As String = "{4C6620BC-422D-9363-642F332FBF7B}"
    
    ' Worksheet in which data imports.
    Set objWksheet = WksSharePointData
    objWksheet.Activate
    
    ' Delete old data list if exists
    Set CheckListObject = objWksheet.Range("A1").ListObject
        If CheckListObject Is Nothing Then
        Else
            objWksheet.Range("A1").ListObject.Delete
        End If
    
    ' Add a list range to the newly created worksheet and populated it with the data from the SharePoint list.
    Set objMyList = objWksheet.ListObjects.Add(xlSrcExternal, _
        Array(strSPServer, LISTNAME, VIEWNAME), False, xlGuess, Range("A1"))
End Sub

I have below peace of code to update the Share Point List, but unfortunately its not working at all

Sub UpdateSharePoint()
    Dim CheckListObject As ListObject
    Dim objWksheet As Worksheet
    Const strSPServer As String = "ServerName/ _vti_bin"
    Const LISTNAME As String = "{69D094F2-40AD-8225-26F4260A1DDB}"
    Dim retUrl As String

    ' Worksheet in which data imports.
    Set objWksheet = WksSharePointData
    objWksheet.Activate
    
    Set CheckListObject = objWksheet.Range("A1").ListObject
    ' Publish the table to share point
    retUrl = CheckListObject.Publish(Array(strSPServer, LISTNAME), False)
End Sub

When I execute the above code I get at last code line

"Run-time error'-2147467259 (80004005)':
An unexpected error has occurred. Changes to your data cannot be saved"

Can some one please help me in this. Thank you

[Vanidasan]

Dear Raghuram

I tried your code and it's working for me the only change I made was:

Const strSPServer As String = "ServerName/ _vti_bin"
Const strSPServer As String = "ServerName/"

Just removed the _vti_bin and it works, Hope this help others too.