SQL Server Link Database to Excel File not showing any tables but with no errors

I am using SQL Server 2017 on Windows 2022 and am creating a linked server to an excel table to import data.

I have ensured that I have installed the Microsoft.ACE.OLEDB.12.0 driver:

I have used the following script to add the linked server:

EXEC sp_addlinkedserver
     @server = N'test1',
     @srvproduct = N'Excel',
     @provider = N'Microsoft.ACE.OLEDB.12.0',
     @datasrc = N'C:\mytest\testexcel.xlsx',
     @provstr = N'Excel 12.0;IMEX=1;HDR=YES;';

I run the script: no errors.
I right click on the newly created linked server and test the connection: It connects OK

However, there is no Sheet1$ as expected.

I am left scratching my head as I am not getting any errors.

Found the issue:

Although having setup many instances of SQL server and installed the Microsoft.ACE.OLEDB.12.0 driver many times I do not recall having to modify the properties of the driver to allow it to work.

I am not sure if the defaults have changed but by investigations led me to an old server to compare the driver settings and I found that on our old server the "Allow inprocess" driver option was checked.

I went to our new server and found it was not checked, so did the following:

Right clicked on the Microsoft.ACE.OLEDB.12.0 driver

Then ensure the Allow inprocess is checked.

This seems to have cured the issue.

However, I did some searches for the usage of the Allow inprocess setting but can find no documentation, so even though it works, I don't know why.