Unable to add records to Sharepoint List from Excel VBA using shared link-CodePudding

I'm trying to create an Excel user form that will add newrecords to a Sharepoint List. The problem is that this form needs to be available to all users in my organization, without their being specifically permissioned to the List.

The basic URL for the Sharepoint List looks something like this: https://myorg.sharepoint.com/personal/myname/;LIST=SubmissionsTest;

However, this link only works for me. If I try to Share the List with people in my company, Sharepoint provides me with a link that looks like this: https://myorg.sharepoint.com/:l:/g/personal/myname/arandom50charactertextstring

Test users have confirmed they can access the List via the second link, but not the first.

To push records into the List from Excel, I've created the following VBA code:

Dim cnt As ADODB.Connection
Dim rst As ADODB.Recordset
Dim mySQL As String

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

mySQL = "SELECT * FROM SubmissionsTest;"

With cnt
    .ConnectionString = _
    "Provider=Microsoft.ACE.OLEDB.12.0;WSS;IMEX=0;RetrieveIds=Yes;" _
    & "DATABASE=https://myorg.sharepoint.com/personal/myname/;LIST=SubmissionsTest;"
    .Open
End With
rst.Open mySQL, cnt, adOpenDynamic, adLockOptimistic

rst.AddNew
    rst.Fields("Title") = Int(Now() * 100000)
    rst.Fields("testEntry") = "This is a test submission"
    rst.Fields("testSubmitter") = Environ("UserName")
rst.Update

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

MsgBox "Your submission has been received."

This code works fine when I run it. As expected, it does not run for my test users. However, when I try substituting that second, Sharepoint-provided link into the Connection String, the code no longer works either for me or my test users. Instead, we receive errors telling us the SubmissionsTest object cannot be found.

I have yet to be able to figure out how I can tweak this code so that the Shareable link is recognized. Does anyone have any ideas on how this can be accomplished?

Thanks in advance for any suggestions.

CodePudding user response：

OK, I found the solution after studying this posting on the Microsoft forums: https://social.technet.microsoft.com/Forums/en-US/2a1b718a-e9a5-4a1d-96a9-97804ebef769/vba-to-insert-record-to-an-existing-sharepoint-online-list?forum=sharepointgeneral

As indicated in the code above, the Connection String I was trying to use was:

DATABASE=https://myorg.sharepoint.com/personal/myname/;LIST=SubmissionsTest;

Even after permissioning the Sharepoint List for anyone in the firm, Excel reported that it was unable to find the table "SubmissionsTest". On the recommendation of the post above, I looked up the GUID values for the List. That returned a string that looked something like this:

<LIST><VIEWGUID>alongstringofalphanumericcharacters</VIEWGUID>
<LISTNAME>anotherstringofcharacters</LISTNAME>
<LISTWEB>https://myorg.sharepoint.com/personal/myname/</LISTWEB>
<LISTSUBWEB></LISTSUBWEB><ROOTFOLDER></ROOTFOLDER></LIST>

Once I changed the Connection String to this:

DATABASE=https://myorg.sharepoint.com/personal/myname/;LIST={anotherstringofcharacters};

the code now works for everyone. My conclusion is that, on SharePoint O365, table names cannot necessarily be trusted. Sometimes, the GUID values will need to be referenced directly.