ADODB Recordset Retrieving Data from a SharePoint Excel File

I am trying to keep a database on sharepoint so multiple sites can update one database instead of collaborating them together every month for reporting. The macro file, which each site can house on their own computer, is using ADODB Recordsets to access the information from sharepoint. My connection to the sharepoint file seems to generate an error at the xlRS.Open line. The sharepoint file is password protected (I have tested the file unprotected and the same error is generated) and the error excel generates states: "Cannot connect to the SharePoint site, Try again later."

Could someone please help resolve why my connection is failing when opening the recordset?

Please Login or Register  to view this content.

Yes, xlTable holds my DB Table's name, but changing the SQL string results in the same error.

Already been through all of the different types and no such luck with any of them.

I'm pretty sure you can't use ADO over HTTP, you need to map the drive and use that ( Suspect that this will work) or take the file off sharepoint and put it on a network share, or just use a Sharepoint list rather than an Excel document

My company is requiring the use of SharePoint and I was using our network share during the initial programming (where everything was working fine) but international sites cannot have access to our network. I have mapped the drive but how do you access and update the data using VBA when the location is still web based? Could you please enlighten me on what a SharePoint list is and how to read and write to it with VBA?

Bump. This is very important and I would greatly appreciate any additional advice.

Have you google'd sharepoint lists? They're a pretty standard part of sharepoint - there's loads out there on them and you can query them with ADO.

@Arkadi, no, you can't ado to a file on a website

I'm happy to be wrong, would you mind pointing me in the direction of a working example?

It's possible for Access on sharepoint and sharepoint lists - but I very much doubt files

There isn't a working example because you can't do it, the links that Arkardi posted refer to sharepoint lists, not documents on sharepoint. Linking lists is dead easy, there's even a button on the list to do it.

@arkadi, unfortunately, they're just lists as you've noticed

No worries mate

For those wondering how to connect to a SharePoint List with Excel 2010 and have the capability for each data source to update the other, refer to this link: https://support.microsoft.com/en-us/kb/930006. This link does not answer my question regarding ADODB Recordsets but it gets the job done.

You can then use ListObjects to resize the table and synchronize it with sharepoint.

Please Login or Register  to view this content.

If anyone knows how to get data from a SharePoint List using Excel ADODB Recordsets instead of using ListObjects, I would still greatly appreciate seeing an example.