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.