You'll need to enable 'Ad Hoc Distributed Queries' as a server configuration if it is not already enabled to perform this task using T-SQL.
EXEC sp_reconfigure 'show advanced options', 1';
EXEC sp_configure 'Ad Hoc Distributed Queries', 1;
Once enabled, you'll need to have the Excel sheet already created with the column names from the table you're wishing to export. This is one of the downsides it seems from trying to perform this task, as we'd typically like to create the Excel file dynamically. You should then be able to use the following code to export the data to your created Excel file with the appropriate provider. In this case, the ACE.OLEDB provider.
INSERT INTO OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'SELECT col1, col2, col3 FROM [Sheet1$]')
SELECT col1, col2, col3
I found a few resources along the way that might explain this with some additional context: