asked on

Export 3 SQL Server 2008 tables to Different Tabs in Excel using VB.net

I want to export to excel from 3 different tables into 3 different tabs corresponding tables in excel.

Tables are:
Table 1 : IDNo Year Period ReportDate

Table 2 : IDNo Year Period ReportDate Employee AvgHours

Table 3 : IDNo Year Period ReportDate Sales TotalHours

Please see Example Output what I would like to accomplish.

Thank you.
OutputFrom_3SQLServer2008Tables.xlsx

lcohan

You could do that via T-SQL pretty easy like in selects below (and you can execute them via VB.net if you really want that) but note the excel output will be on the SQL server where you execute these queries and make sure the Excel doc exists and the three sheets have the exact same structure as your SQL tables:

insert into OPENROWSET(
      'Microsoft.Jet.OLEDB.4.0',
      'Excel 8.0;Database=c:\export\sales.xls;;HDR=YES',
      'SELECT * FROM [Sheet1$]')
select * from Clients;

insert into OPENROWSET(
      'Microsoft.Jet.OLEDB.4.0',
      'Excel 8.0;Database=c:\export\sales.xls;;HDR=YES',
      'SELECT * FROM [Sheet2$]')
select * from Products;

insert into OPENROWSET(
      'Microsoft.Jet.OLEDB.4.0',
      'Excel 8.0;Database=c:\export\sales.xls;;HDR=YES',
      'SELECT * FROM [Sheet3$]')
select * from Sales;

Queennie L

ASKER

I have an error:
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.

ASKER CERTIFIED SOLUTION

lcohan

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Martin Liss

This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.