Tested with the Microsoft Access Database Engine 2016 Redistributable with SQL Server 2016 on Windows Server 2016. Have not yet tested this from a SQL Server Agent job, which was the intention.

Prerequisites:

1. The Excel file should already exist.
2. The Excel file should already contain the exact number of required columns.

Microsoft Access Database Engine Redistributables:

• Microsoft Download Center - Microsoft Access Database Engine 2016 Redistributable
• Microsoft Download Center - Microsoft Access Database Engine 2010 Redistributable
• Microsoft Download Center - 2007 Office System Driver: Data Connectivity Components

Steps:

1. Install the Microsoft Access Database Engine 2016 Redistributable.
2. Enabling Ad Hoc Distributed Queries is required:

   sp_configure 'show advanced options', 1;  
   RECONFIGURE;
   GO 
   sp_configure 'Ad Hoc Distributed Queries', 1;  
   RECONFIGURE;  
   GO

3. Use the following query:

   INSERT INTO OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;HDR=NO;IMEX=1;Database=C:\Map1.xlsx;','SELECT * FROM [Sheet1$]')
   SELECT * FROM sysobjects

   1. HDR=NO: the first row does not contain column headers.
   2. IMEX=1: "a safer way to retrieve data for mixed data columns"
   3. Both HDR and IMEX are not mandatory parameters.

Sources:

• ConnectionStrings.com - Microsoft ACE OLEDB 12.0 connection strings
• ConnectionStrings.com - Excel connection strings
• Stack Overflow - What is the default value of IMEX in OLEDB?
• ETL-Tools.com - IMEX=1
• Stack Overflow - What is IMEX in the OLEDB connection string?
• Microsoft Docs - Microsoft OLE DB Provider for Microsoft Jet Overview
• Stack Overflow - Specification of Extended Properties in OleDb connection string?
• Microsoft Docs - ADO Provider Properties and Settings
• Microsoft Developer Network - Is there documentation for Microsoft.ACE.OLEDB.12.0?
• VM Blogs - How to solve Microsoft.ACE.OLEDB.12.0 error "Unspecified error"
• Microsoft SQL Server Forums - OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "Unspecified error"
• Microsoft SQL Server Forums - Quering Excel
• Stack Overflow - The OLE DB provider “Microsoft.ACE.OLEDB.12.0” for linked server “(null)”
• Microsoft Docs - ad hoc distributed queries Server Configuration Option
• Microsoft Docs - OPENROWSET (Transact-SQL)
• SQL BI / Data Access Technologies - “Unexpected error from external database driver (1). (Microsoft JET Database Engine)” after applying October security updates.
• Stack Overflow - Accessing 'Microsoft.ACE.OLEDB.12.0' from SQL Job Agent
• GranadaCoder C# Tidbits - Bug in Documentation : Microsoft Access Database Engine 2010 Redistributable
• SQL Authority - SQL SERVER – FIX – Export Error – ‘Microsoft.ACE.OLEDB.12.0’ provider is not registered on the local machine
• www.Excel-SQL-Server.com - Excel Import to SQL Server using Linked Servers
• Stack Overflow - OLE DB provider 'Microsoft.Jet.OLEDB.4.0' cannot be used for distributed queries
• Stack Overflow - How to Extract the Query result to directly to .XLSx File