0

Below, I've got a little job to insert a record into a SharePoint list. The Requestor field is a "Person or Group" type field, and in the code, the...

rst![Project] = Sheets("Sheet2").Range("B2").Value

...statement wants a number. Through various testing, I found that this is not the ID from the User Information List. My user on the UIL has an ID of 94, but to get his name to work through these commands, I have to use 90 which I assume is buried down in SharePoint somewhere.

Anyone know what that Id Number is and how to retrieve that number out of SharePoint based on the User's Name?

Dim conn As ADODB.Connection
Dim cmd As ADODB.Command
Dim rst As ADODB.Recordset
Dim errADO As ADODB.Error

Set conn = New ADODB.Connection
Set rst = New ADODB.Recordset

Dim strSQL As String
Dim strErrorMessage As String   

conn.ConnectionString = _
    "Provider=Microsoft.ACE.OLEDB.12.0;WSS;IMEX=0;RetrieveIds=Yes;DATABASE=https://george.com/finance;LIST={AA7C1FFA-97EB-4028-B720-0BD8449265BB}" + _
    ";Use Display Names=true;"
conn.Open

strSQL = "SELECT * FROM [Capital Expenditure Projects]"

rst.Open strSQL, conn, adOpenDynamic, adLockOptimistic

rst.AddNew
    rst![Project] = Sheets("Sheet2").Range("B2").Value
    rst![Requestor] = Sheets("Sheet2").Range("B3").Value
rst.Update

2 Answers 2

0

I am also looking for a VBA solution and have tried an approach with JSON without success. I want to clarify something that I have observed with SharePoint. The User Id seems to change when you move from site to site and/or subsite. The best way to verify is to use the URL /_api/web/currentuser to look at details. Another approach, the URL to pull user information is /_api/web/siteusers(@v)?@v='i:0#.w|domain\username'. This did not work for me unless the string was converted: /_api/web/siteusers(@v)?@v=%27i%3A0%23.w%7Cdomain%5Cusername%27. I still have more ideas to try to use this string to return the USER ID.

0

I had the same issue. To solve this you need to specify in your connection string property that RetrieveIds=No.

conn.ConnectionString = _
    "Provider=Microsoft.ACE.OLEDB.12.0;WSS;IMEX=0;RetrieveIds=No;DATABASE=https://george.com/finance;LIST={AA7C1FFA-97EB-4028-B720-0BD8449265BB}" + _
    ";Use Display Names=true;"

After that you should be able to retrieve the user name.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.