Oledb and SharePoint connection issues

[EmmaBurns]

Hi All,

I'm trying to connect to a SharePoint Excel file (not a list) via oledb so I can pull the data in as a collection, without having to first download the file to a local drive, but I seem to be cycling through a multitude of errors. (Mapping the SharePoint as a drive also isn't an option!)

The oledb driver I use is Microsoft.ACE.OLEDB.12.0

Errors I've encountered so far are;
Invalid Argument
Could not find an installable ISAM
Failure creating file
IErrorInfo.GetDescription failed with E_FAIL

Could someone please help and advise what to do. Thanks

[harish.m]

Hi Emma Burns,
 I would check whether all these prerequisites are met Sharepoint OLEDB
 Are you able to share the connection string so that it  is helpful
1) "Could not find an installable ISAM" - 

"This issue occurs if the workbook was previously opened and refreshed in a newer version of Excel."

https://support.microsoft.com/en-us/topic/-could-not-find-installable-isam-error-when-you-refresh-a-data-table-in-excel-f9753fad-d2ae-1776-336b-feedd46ca4e2
2) IErrorInfo.GetDescription failed with E_FAIL -
Check any reserved words in your query based on the Access mode  you are using
https://stackoverflow.com/questions/15534284/what-is-causing-my-oledbexception-ierrorinfo-getdescription-failed-with-e-fail

[EmmaBurns]

I have tried various different connections strings - but it's variations of:

Provider=Microsoft.ACE.OLEDB.16.0; Data Source = [tSharepointFileURL.xlsx]; Extended Properties=Excel 16.0 XML; HDR=Yes; IMEX=1;

I can get around the IErrorInfo.GetDescription failed with E_FAIL error by removing the IMEX=1 statement

All versions of Excel used are 16.0, but I can get around the Could not find an installable ISAM error by using this connection string Provider=Microsoft.ACE.OLEDB.16.0; WSS; Database = [tSharepointFileURL.xlsx]; Extended Properties=Excel 16.0 XML; HDR=Yes; IMEX=1; 

But then I'm met with Cannot connect to the SharePoint site '[tSharepointFileURL.xlsx]'. Try again later.

EDIT 

I've since been able to move on from the error Cannot connect to the SharePoint site '[tSharepointFileURL.xlsx]'. Try again later. by replacing my /'s with \'s - but have moved onto another issue now.

The Microsoft Access database engine could not fine the object 'Sheet1$'. Make sure the object exists and that you spell its name and the path name correctly.

The sheet name is Sheet1 so no idea what is going on.