16-05-23 04:01 PM
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
16-05-23 05:09 PM
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
17-05-23 09:58 AM
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.