Reading a SharePoint list using Excel VBA
I am having an issue pinning down why one machine can access a SharePoint list using the code below but another cannot.
The code below works fine on my PC and laptop but not on a collegue's laptop. The error returned is ...
"Run-time error: '-2147467259 (80004005)';
"Could not find installable ISAM"
Googling has told me to try ...
- Adding "Extended properties=" and single speech marks around the list of items after Provider (as seen in the code below) = Same error occurred.
- Installing the Microsoft Access Database Engine 2010 Redistributable (have tried both 32bit and 64bit) = Neither made a difference.
- Going to the SharePoint site via Internet Explorer and logging in (to establish credentials) = Can get to the site but doesn't help the Excel code to run.
The machines where it works are Microsoft 365 (Business version). Latest version that Update can find.
The machine where it doesn't work is Microsoft Office 2016 for Home and Business. 32 bit installation.
Is there anything else I can try?
Code:
sConn = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Extended Properties='WSS;IMEX=1;RetrieveIds=Yes;" & _
"DATABASE=" & sSHAREPOINT_SITE & ";" & _
"LIST=" & sDEMAND_ROLE_GUID & "';"
' Create some new objects.
Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
' Open the connection.
With cn
.ConnectionString = sConn
.Open
End With
Thanks
Simon
