Using OPENROWSET to export dataset into specific range in Excel sheet

Question

I would like to export data from sql server into an Excel sheet. The sheet is structured and has more than one table.

Is there a way to specify an export range or is it limited to sheet only?

insert into opendatasource(
    'Microsoft.ACE.OLEDB.12.0', 
    'Data Source=Mypath\MyFile.xlsx;Extended Properties=Excel 12.0')...[MySheet$]

And I need specify something like:

[MySheet$].MySpecificTable or
MySheet$MySpecificTable or
MySheet$I4:M8

I don't want use the export wizard - this task should be automated. I know how to do this in C# or SSIS or VBA, but I want know how to do this via OPENDATASOURCE or OPENROWSET.

You need to use C# for this. Any specific reason why you want to achieve this via openrowset ? — singhswat
I'm only interested (because this should be handled by users withou security context to access server side - without deploys, but with access to sql server databases). In SSIS syntax MySheet$I4:M8 works fine, so i'm looking for similar solution.. — Deadsheep39

Deadsheep39 Deadsheep39 · Accepted Answer · 2017-05-31T08:29:47

There is no way how to do this via opendatasource/openrowset...

Opendatasource is half-unproduced functionality. It's probably not done to work with data but only for ad-hoc connections.

Using OPENROWSET to export dataset into specific range in Excel sheet

1 Answers