Accessing an OPEN Excel table via linked server in SQL Server

Question

I am using Office 2016 32-bit and SQL Server 64-bit on a Windows 10 64-bit machine. I have installed the driver Microsoft Access Database Engine 2010 Redistributable.

The following code allows me to successfully read the rows from a sheet in a closed Excel workbook from SQL Server.

EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1
GO 
EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1
GO
EXEC sp_addlinkedserver
    @server = 'ExcelServer2',
    @srvproduct = 'Excel', 
    @provider = 'Microsoft.ACE.OLEDB.12.0',
    @datasrc = 'C:\Temp\book2.xlsx',
    @provstr = 'Excel 12.0;IMEX=1;HDR=YES;'
SELECT * FROM ExcelServer2...[Sheet1$]

However if the workbook is open then I get the following error:

Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "excelserver2" reported an error. The provider did not give any information about the error.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "excelserver2".

Is there any way of reading the rows from an open workbook?

Answer 1

This isn't really an answer, but it is a good workaround.

I have two copies of the workbook.

1. One copy is called "MyFile.xlsm" which I keep open the whole time - this I work on and update
2. One copy is called "MyFileCopy.xlsm" which I keep closed the whole time - this is connected to the database

I work on the open workbook, and when I want to, I just run the following macro which updates the closed workbook, without affecting my open workbook

Sub copyFile()
  ActiveWorkbook.SaveCopyAs "C:\MyDirectory\MyFileCopy.xlsm"
End Sub