Update a SharePoint 2010 List via Excel 2010

[richh]

I have a program that updates my SP list upon save. It worked on my previous list, but I'm attempting to make it work on a new list.

I can pull the data down automatically, overwrite the data on the list, but I get an error when I attempt to save the changes.

Public Function spEdit(wb As Workbook)
    Dim ws  As Worksheet
    Dim src(0 To 2) As Variant
    Dim guiid   As String
    
    Set ws = wb.Worksheets("Data")
    
    src(0) = " 'tableSource
    src(1) = "{}" 'tableID
    src(2) = "{}" 'GUID
    
    ws.ListObjects.Add xlSrcExternal, src, True, xlYes, ws.Range("A1")
    
End Function

 

[richh]

Globals

Public ws           As Worksheet
Public lists        As Worksheet
Public nAssignments As Integer
Public wb3          As Workbook
Public ws3          As Worksheet
Public xlapp2       As Object

UForm Initialize

Private Sub UserForm_Initialize()
    Set xlapp2 = CreateObject("excel.application")
    Set wb3 = xlapp2.Workbooks.Add

    Set ws3 = wb3.Sheets.Add
    ws3.Name = "Data"
    Call spEdit(wb3)
    
    xlapp2.Visible = True
End Sub

Update Function

Public Function updateSP(uform As UserForm)
    Dim sVal        As Integer
    Dim aName       As String
    Dim lRow        As Integer
    Dim val         As Variant
    Dim lRow2       As Integer
    Dim tbl         As ListObject
    
    Set tbl = ws3.ListObjects(1)

With uform
    
        With .Controls("results")
            sVal = .list(.ListIndex, 5)
        End With

        If .Controls("prNum") <> ws3.Cells(sVal, 12).Value Then
            uans = MsgBox("Commit this change?" & vbNewLine & vbNewLine & _
            "PR Num: " & ws3.Cells(sVal, 12).Value & vbNewLine & _
            "Changed to: " & .prNum.Value & vbNewLine, vbYesNo)
            
            If uans = vbYes Then
                With tbl
                    .ListRows(sVal - 1).Range(12).Value = uform.Controls("prNum").Value
                End With
            End If
        End If

    End With

End Function

Save function

Public Function saveSP(wb As Workbook)
    Dim ws      As Worksheet
    Dim splist  As ListObject
    
    On Error GoTo errH
    
    Set ws = wb3.Worksheets("Data")
    Set splist = ws3.ListObjects(1)
    
    splist.UpdateChanges xlListConflictDialog
    
    Set ws = Nothing
    Set splist = Nothing
Exit Function

errH:

Debug.Print Err.Description & Err.Number

End Function

 

[richh]

The error I encounter is during the saveSP function. I am able to get the information down from SP via the spEdit function and UForm initialization sub. I can write to every cell on the list via the updateSP function. I can see that the splist Object in the saveSP function is looking at the correct object, but it won't commit the changes to SP.

My previous version has little changed, other than it looks at a different list and I've tried to resort to using global variables rather than creating variables in each sub. You can see that the exception to this statement is in the saveSP function, where I've tried reverting back to the old version's syntax and passing a referece to the global variable (lol right?) and setting a local Worksheet var to the newly created sheet.