Add new record to SharePoint Server 2019 list from VBA

Question

Several of my clients use SharePoint 2019 on premises for their communication strategy. These are all public facing, anonymously accessed environments that are extremely effective as a communications platform between the residents of the town or municipality and the thousands of staff.

The security policies of many of these clients often dictates that the minimum number of software applications are loaded on the staff machines.

In other words, I ONLY have access to SharePoint and Office products to work with. I cannot put a Power Automate gateway onto the server. I HAVE to use VBA because it comes with Outlook, Excel and Word.

The clients typically use Forms Based Authentication or Claims Based authentication, allowing for residents of their town or municipality to stay abreast of all of the latest information, notices and news.

Data is gathered via emails and WhatsApp message and other social media platforms.

Typically I will get an instruction to "shred" or "scrape" data stored as tables in an email message and append this data into a SharePoint list.

Sub AddItem()
'
'   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 <myList>;"

    With cnt 
'Microsoft.Office.List.OLEDB.2.0;
'Microsoft.ACE.OLEDB.12.0;

.ConnectionString = _
      "Provider=Microsoft.Office.List.OLEDB.2.0;WSS;IMEX=0;RetrieveIds=Yes;DATABASE=https://<ClientSite/sites/entinvest;LIST={AAAAAA-C40A-4DA8-AB74-BFF176880042};"
        .Open
    End With

    rst.Open mySQL, cnt, adOpenDynamic, adLockOptimistic

    rst.AddNew
        rst.Fields("FirstName") = "rRRRRR"
        rst.Fields("Surname") = "SRA"
        rst.Fields("MobileNumber") = "02134567890"
    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

I have scraped this code from the following link: https://social.msdn.microsoft.com/Forums/en-US/2a1b718a-e9a5-4a1d-96a9-97804ebef769/vba-to-insert-record-to-an-existing-sharepoint-online-list?forum=sharepointgeneral

The problem is the authentication. I cannot store the username and password in the code. All access to the information within the list is based on a pop-up form that requests a login process. I need to use that existing connection.

I can download a list to Excel and use that connection string.

The problem is that I am getting an error saying "Cannot find an installable ISAM" on the .OPEN line of the code above.

Can someone please help me with the correct connection string with the correct authentication PLEASE?

Answer 1

Hi @Séan Achim

Since the issue is about VBA. Microsoft has a dedicated forum to discuss about VBA. You can get some more professional advice in the following forum.

https://techcommunity.microsoft.com/t5/forums/filteredbylabelpage/board-id/ExcelGeneral/label-name/macros%20and%20vba

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.