html - VBA 到 Sharepoint 如何添加人员或组

Question

我目前正在使用一个 VBA 脚本，它接受 excel 输入并将它们上传到共享点。

我遇到的唯一问题是上传“人物”或“群组”，因为我得到类型不匹配，可能是因为它试图获取链接？

这是代码：

Sub AddItems()
'
' Requires a reference to "Microsoft ActiveX Data Object 6.0 Libray" to insert a record into a sharepoint list "AccessLog"
'
    Dim cnt As ADODB.Connection
    Dim rst As ADODB.Recordset
    Dim mySQL As String

    Set cnt = New ADODB.Connection
    Set rst = New ADODB.Recordset
    Dim LastRow As Long, i As Long

    mySQL = "SELECT * FROM [TestingSharepoint];"

    With cnt ' See https://www.connectionstrings.com/sharepoint/

.ConnectionString = _
        "Provider=Microsoft.ACE.OLEDB.12.0;WSS;IMEX=0;RetrieveIds=Yes;DATABASE=https://share.amazon.com/sites/TestingIPVABO Sharepoint;List={F1BDB92A-349A-4056-9EF4-F6C5AFC3CE9F};"
        .Open
    End With
    LastRow = Cells(Rows.Count, "A").End(xlUp).Row

    rst.Open mySQL, cnt, adOpenDynamic, adLockOptimistic
    
    Set ColumnToCheck = Worksheets("Audits_10d").Range("A1:A2000")

    For Counter = 11 To LastRow
       
       ValueToCheck = Sheets("Translation").Cells(Counter, 3).Value

       If IsError(Application.Match(ValueToCheck, ColumnToCheck, 0)) Then
            rst.AddNew
            rst.Fields("Task ID") = Sheets("Translation").Cells(Counter, 3).Value
            rst.Fields("Seller ID") = Sheets("Translation").Cells(Counter, 4).Value
            rst.Fields("Site") = Sheets("Translation").Cells(Counter, 5).Value
            rst.Fields("Mktpl") = Sheets("Translation").Cells(Counter, 6).Value
            rst.Fields("Country") = Sheets("Translation").Cells(Counter, 7).Value
            rst.Fields("Inv_Date") = Sheets("Translation").Cells(Counter, 8).Value
            rst.Fields("Associate_Login") = Sheets("Translation").Cells(Counter, 9).Value
            rst.Fields("Manager_Login") = Sheets("Translation").Cells(Counter, 10).Value
           
            
      End If
    Next Counter
            
            
            
            
    rst.Update ' commit changes to SP list

    If CBool(rst.State And adStateOpen) = True Then rst.Close
    If CBool(cnt.State And adStateOpen) = True Then cnt.Close
End Sub

因为输入看起来像这样：

有人知道如何解决它吗？

标签： htmlexcelvbasharepointsharepoint-list