Louitbol
Louitbol

Reputation: 170

VBA, ADO and SharePoint list : retrieving columns of type Person

I am working with VBA and ADODB to retrieve the content of SharePoint lists. It works well but for some reason, I can't retrieve columns of type Person.

I am absolutely certain these exist in the list. But even if I select the field by its name instead of *, I still get no result (I explicitly search for it 'If fieldName = "ColumnOfTypePersonName"' but no dice...).

Does anyone here can explain why ?

Here is my code:

    Dim Conn As Object
    Dim Rec_Set As Object
    Dim Sql As String
    Dim i As Integer
    Dim fieldName As String
    Dim fieldValue As Variant
    
    Set Conn = CreateObject("ADODB.Connection")
    Set Rec_Set = CreateObject("ADODB.Recordset")
    Set resultTable = CreateObject("Scripting.Dictionary")
    
    With Conn
        .ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;WSS;IMEX=0;RetrieveIds=Yes;" & _
                            "DATABASE=" & "mySharePointURL;" & _
                            "LIST={listGuid};"
        .Open
    End With
    
    Sql = "SELECT * FROM [MyListName];"
    
    With Rec_Set
        If .State = 1 Then .Close
        
        .ActiveConnection = Conn
        .CursorType = adOpenDynamic
        .CursorLocation = adUseClient
        .LockType = adLockOptimistic
        .Source = Sql
        .Open
    
        If .RecordCount > 0 Then
            For i = 0 To .Fields.Count - 1
                fieldName = .Fields(i).Name
                fieldValue = .Fields(i).Value
                If fieldName = "ColumnOfTypePersonName" Then
                    MsgBox fieldValue 'Never hit!
                End If
                resultTable.Add fieldName, fieldValue
            Next i
        End If
    
        .Close
    End With
    
    Set Rec_Set = Nothing

Upvotes: 0

Views: 130

Answers (0)

Related Questions