Cannot query open Excel file from SQL Server

I have an excel spreadsheet that I need to query from t-sql.  To do this I am using:
SELECT * 
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
  'Excel 12.0 Xml;
   Database=C:\user\test.xls',
   'SELECT * FROM [Database$]'); 

Open in new window


This works fine, however, when that spreadsheet is open for use, I get the following error:
Msg 7399, Level 16, State 1, Line 3
The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7303, Level 16, State 1, Line 3
Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

Is there any way to stil query this excel spreadsheet when it is open and in use?
maverick0728Asked:
Who is Participating?
 
QlemoConnect With a Mentor Batchelor, Developer and EE Topic AdvisorCommented:
Not really. It might work if you allow collaboration (enabling sharing the workbook), but I never tried.
If only read-only access is needed, you might be able to create a copy of the workbook prior to trying to read content.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.