Update a SharePoint List from VBA after URL change

[raymundus]

Hi there!
I have had this code up and running for years. Our company had a SharePoint update and the URLs are now https vs http. The list and view GUIDs have not changed. After updating the URL in the Code, it breaks at "objListObj.Refresh"

What the Macros do: the first sub accesses a SharePoint List>View where the view contains a filtered subset of list items and refreshes a table on a sheet. The second sub pushes the data back to the list after we make edits on the sheet.

I am assuming that the connection to the table is somehow broken, because I think the vba is fine. Any advice would be greatly appreciated.

Code:

Sub Linkup()

'Run this to start. It creates a link to the SharePoint site

With Application
    .ScreenUpdating = False
    .EnableEvents = False
    .CutCopyMode = False
End With

Dim ws  As Worksheet
    Set ws = Sheets("owssvr")
    Dim src(0 To 2) As Variant
 
 
    src(0) = "https://sharepoint4.company.com/sites/Name/collection/_vti_bin"
    src(1) = "{F72Some3-17E0-4255-9309-10Numbers836}" 'List GUID
    src(2) = "{A73More2-73DA-497C-9CA4-7CNumbers560}" 'View GUID
 
If ws.ListObjects.Count = 0 Then
    ws.ListObjects.Add xlSrcExternal, src, True, xlYes, ws.Range("A2")
Else
    Set objListObj = ws.ListObjects(1)
    objListObj.Refresh
End If

With Application
    .ScreenUpdating = True
    .EnableEvents = True
    .CutCopyMode = True
End With

End Sub


Sub UPDATESP()

'Updates changes to list items

With Application
    .ScreenUpdating = False
    .EnableEvents = False
    .CutCopyMode = False
End With

Dim ws As Worksheet
Dim objListObj As ListObject

    Set ws = ActiveWorkbook.Worksheets("owssvr")
    Set objListObj = ws.ListObjects(1)

    objListObj.UpdateChanges xlListConflictRetryAllConflicts

With Application
    .ScreenUpdating = True
    .EnableEvents = True
    .CutCopyMode = True
End With

End Sub

 

[raymundus]

Update: I scrapped the workbook with the broken connection. Exported a set of data from the SharePoint List>View. Pasted the code into the editor and now the first sub works. I can retrieve data from a view.

However, now when I make changes in the table, and execute the second sub, it gets stuck at the objListObj.UpdateChanges xlListConflictRetryAllConflicts line.

On the old workbook there was a column, Column A called ID. But ID is not selected in the view in SharePoint. And running the first sub didn't overwrite column A. Column A enumerated any items beginning with 1, every time. It was as if that column was used as a unique ID for the item. Does anybody have any insights on how that worked? How does Column A appear in the table if it is not a column in SharePoint?