OLE DB provider 'Microsoft.Jet.OLEDB.4.0' cannot be used for distributed queries
According to this thread,:
Microsoft.Jet.OLEDB.4.0 is not supported for 64-bit OS
Assuming you are running SQL Server 64-bit, you likely need the 64-bit Microsoft Access Database Engine 2010 Redistributable.
And be aware that there is a minor wrinkle when trying to install the software if the other version is already installed. In this case install the second version from the command line using the /passive switch. According to this thread:
Launching the install of a Microsoft ACE OLEDB Provider on a machine with an Office install other than the current one (e.g. 32 on 64) will cause the install to fail. To have it run properly you need to launch it from a command line with the “/passive” argument specified.
That is talking about an existing Office install but the same applies to coexisting database engine installations.
EDIT: Also make sure to use "Microsoft.ACE.OLEDB.12.0" not "Microsoft.Jet.OLEDB.4.0" for the provider string. (Props to @Rumi)
Use SQL like below:
SELECT * into temptable FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 12.0;Database=D:\new.xlsx','select * from [sheet1$]')