VBA Excel writing to SharePoint ADO

[JBakker]

Hello all, I have a coding solution that worked successfully for a number of months but then stopped. I'm self-taught / Google/Youtube taught so you may have to dumb it down for me. The code takes a few fields from Excel and writes it to a SharePoint list.

The code still works fine for me and most of my co-workers (70+) but it doesn't work for a critical few (10). It is failing when opening the connection string. Since it is not failing for everyone, is there a setting that would effect this? The error received is :

"Run-time error '3706' Provider cannot be found. It may not be properly installed. "

Sub NewNIFdb()
    
    Dim con         As ADODB.Connection
    Dim rs          As ADODB.Recordset
    Dim SQL         As String
    
    Set con = New ADODB.Connection
    Set rs = New ADODB.Recordset
    SQL = "select * from [NIFdb] ;"
    
    
        With con
            .ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;WSS;IMEX=2;RetrieveIds=Yes;DATABASE=http://connect.sharepoint.com/;LIST={6A33CBFF-87BF-4C7B-9D0C-B264E85ADE42};"
            .Open
        End With
        
        rs.Open SQL, con, adOpenDynamic, adLockOptimistic
        
        rs.AddNew
                        rs.Fields("Project Type") = Sheets("Requestor Phase 1").Range("b24")
                        rs.Fields("Project Name") = Sheets("Requestor Phase 1").Range("b25")
                        rs.Fields("MaterialCode") = Sheets("Master Data").Range("b6")
                        rs.Fields("Business Unit") = Sheets("Regular with or without Pouch").Range("fa2")
                 rs.Update
                 rs.Close
                 
        
        If CBool(rs.State And adStateOpen) = True Then rs.Close
        Set rs = Nothing
        If CBool(con.State And adStateOpen) = True Then con.Close
        Set con = Nothing
         
End Sub

Thanks for your help and patience

 

[Norie]

Have the people who are having the problems had any changes/upgrades to their systems?

 

[JBakker]

Some are on Windows 10, most are on Windows 7 Ent. We all recently upgraded to Office 16. I'm currently running Win. 7 64 bit and Office '16 and it works for me. It doesn't work for others with the same combination.

 

[JBakker]

Anyone have suggestion?

 

[JBakker]

Last call? Any VBA experts out there?