Update SharePoint List Using Excel VBA: Person or Group Field Data Type Error

I have successfully written code in VBA that allows me to import/link to a SharePoint list and to synchronize changes. I also have code that allows me to append a new row/recordset one at a time. I’ve tested the code on SharePoint lists separate from the one I’m working on and both methods works.

The problem I’m having is centered around the “Person or Group” data type in SharePoint. Every time I try to either synchronize with SharePoint from Excel OR append a new row, it gives me a data type error.

So here is the question(s): Is there a way to explicitly cast a string into the correct data type? If not, is there a way to make SharePoint take a string in lieu of a “Person” object?

Thanks in advance! Here’s my code:


Sub link_edit_Mode()  Dim mySh As Worksheet Dim spSite As String  Set mySh = Sheets(1)  Dim src(0 To 1) As Variant  spSite = "https://mylinkhere" 'site name src(0) = spSite & "/_vti_bin"  src(1) = "{My GUID Here}" 'GUID  mySh.ListObjects.Add xlSrcExternal, src, True, xlYes, mySh.Range("A1")  End Sub  Sub SaveChanges()  Dim mySh As Worksheet Dim lstOBJ As ListObject  On Error GoTo errhdnler  Set mySh = Sheets(1) Set lstOBJ = mySh.ListObjects(1)  lstOBJ.UpdateChanges xlListConflictDialog  Set mySh = Nothing Set lstOBJ = Nothing  Exit Sub errhdnler:  Debug.Print Err.Description & Err.Number  End Sub 


Sub add_new_item()  Const SHEET_NAME As String = "Sheet1"  Dim cnt As ADODB.Connection Dim rst As ADODB.Recordset Dim mySQL As String Dim i As Long  'set connection Set cnt = New ADODB.Connection Set rst = New ADODB.Recordset  'sql select statement mySQL = "SELECT * FROM [database name]"  'read/write With cnt     .ConnectionString = _     "Provider=Microsoft.ACE.OLEDB.12.0;WSS;IMEX=0;RetrieveIds=Yes;DATABASE=https://mylinkhere/;LIST={MY GUID HERE};"     .Open End With  'open table / recordset rst.Open mySQL, cnt, adOpenDynamic, adLockOptimistic  i = 2  rst.AddNew     rst.Fields("Call_Rep") = Sheets(SHEET_NAME).Cells(i, 2)     rst.Fields("Role") = Sheets(SHEET_NAME).Cells(i, Application.WorksheetFunction.Match("Role", "1:1"))     rst.Fields("CallWeek") = Sheets(SHEET_NAME).Cells(i, Application.WorksheetFunction.Match("CallWeek", "1:1"))  rst.Update  'close recordset/connection and clean memory If CBool(rst.State And adStateOpen) = True Then rst.Close Set rst = Nothing If CBool(cnt.State And adStateOpen) = True Then cnt.Close Set cnt = Nothing 

End Sub