how to export sql table data into excel worksheet?

Question

I have this table that I want to export its data into my excel file. so far I've tried every topic here but nothing worked. I've tried these 2:

insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
    'Excel 8.0;Database=D:\excel\testing.xls;', 
    'SELECT * FROM [newStart$]') select * from OutputResult


insert into OPENROWSET('Microsoft.ACE.OLEDB.12.0',
    'Excel 8.0;Database=D:\excel\testing.xls;', 
    'SELECT * FROM [newStart$]') select * from OutputResult

when I run it with jet I'll get this error:

OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "The Microsoft Jet database engine could not find the object 'newStart$'.  Make sure the object exists and that you spell its name and the path name correctly.".
 Msg 7350, Level 16, State 2, Line 1 Cannot get the column information from OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".

and when I ran the ACE.OLEDB I get this one:

OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "The Microsoft Office Access database engine could not find the object 'newStart$'.  Make sure the object exists and that you spell its name and the path name correctly.".
Msg 7350, Level 16, State 2, Line 1
Cannot get the column information from OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

I give my sql account full control permission as well. plus I run these two as well:

USE [master]
GO
EXEC master . dbo. sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'AllowInProcess' , 1
GO

EXEC master . dbo. sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'DynamicParameters' , 1
GO

2:

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

I would appreciate it if anyone could point me into the right direction.

You can use SSIS to do this, if that is an option you have – Drishya1 Aug 10 '16 at 13:43 — Drishya1, Aug 10 '16 at 13:43

score 0 · Answer 1 · answered Aug 10 '16 at 07:36

0

strSqlQry = "Place SQL Qry Here"
vFilePath = "Database Path"

'Create Objects
Set conn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")

'Open Connection
conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & vFilePath & ";" & _
 "Extended Properties=""Excel 8.0;HDR=Yes;"";"

'Execute Qry
rs.Open strSqlQry, conn, adOpenStatic, adLockOptimistic

'Place recorset into excel worksheet
ActiveSheet.Range("A1").CopyFromRecordset (NewRS)

rs.Close

Set rs = Nothing
Set conn = Nothing

answered Aug 10 '16 at 07:36

Takdir Madhavi

39
3

I'm new with the concept of sql now, should I put this in my store procedure? – erfan Aug 10 '16 at 07:42
cause I want it to generate with sql query. – erfan Aug 10 '16 at 07:43

score 0 · Answer 2 · edited May 23 '17 at 10:28

0

You can use way written in this topic How do you transfer or export SQL Server 2005 data to Excel

In excel using External data menu connect to database

edited May 23 '17 at 10:28

Community

1
1

answered Aug 10 '16 at 07:40

whoismaikl

304
1
3
11

before I ask the question I went through all of these topics. but thanks anyway. – erfan Aug 10 '16 at 07:48

how to export sql table data into excel worksheet?

2 Answers2