Reading a SharePoint list using Excel VBA

[RogueTemplat]

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?

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

[mancubus]

after googling;

(1)
https://social.msdn.microsoft.com/Fo...=officegeneral

The issue "could not find installable ISAM" using the "Microsoft.ACE.OLEDB.12.0" data provider is caused by "ISAM drivers are used by Access to update file formats other than its own. If the path of the ISAM driver in your Windows Registry is not valid, or if the ISAM driver does not exist, the problem may occur."

(2)
https://www.etl-tools.com/articles/imex1.html


i am not familiar with querying sp lists.
maybe above links will give you an idea.
the second link is about registries.

[RogueTemplat]

Hi

Thanks for those. I had seen the first one before but it hadn't helped. The second one is new but relates to Excel rather than SP as the source. Not sure editng the registry is a good way to go, especially as I won't be allowed to for some of the potential users.

I did find a workaround, removed 2016 and went to full 365 and the error stopped. Again, not an ideal solution as may not always be in my control but I am pretty certain the vast bulk of the users will have full 365.

[Tigmaster]

If I remember correctly I had a similar issue.
My code worked fine with users who were on 2007, 2013 or 2016 versions of Excel.
But the code failed on 2010 Excel versions.
the ACE.ADO on the 2010 version doesnt have the capability to talk to the internet.
you will need to install the 2007 Office System Driver: Data Connectivity Components or upgrade to a later version

[rossrankins1]

Hi

Thanks for those. I had seen the first one before but it hadn't helped. The second one is new but relates to Excel rather than SP as the source. Not sure editng the registry is a good way to go, especially as I won't be allowed to for some of the potential users.

I did find a workaround, removed 2016 and went to full 365 and the error stopped. Again, not an ideal solution as may not always be in my control but I am pretty certain the vast bulk of the users will have full 365.

If I remember properly, I experienced a similar problem. Reinstalling helped me too.

[rossrankins1]

If I remember correctly I had a similar issue.
My code worked fine with users who were on 2007, 2013 or 2016 versions of Excel.
But the code failed on 2010 Excel versions.
the ACE.ADO on the 2010 version doesnt have the capability to talk to the internet.
you will need to install the 2007 Office System Driver: Data Connectivity Components or upgrade to a later version

Yes, there is such a problem. I didn't bother and just updated to the latest version.

[abel1303]

If the above steps do not resolve the issue, it's recommended to reach out to your ITsupport or the technical team responsible for managing the machines.

[Aussiebear]

@abel1303, its not so much that in 10 mins after joining, you hijack three old threads that concerns me, but the fact that you have tried to attach spam links to your posts. Give me a reason why you should remain as a member here?

[Aussiebear]

On second thoughts..... There's no benefit in your remaining here.