Help with Sql to Excel - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Help with Sql to Excel

Author

Topic

uplate
Starting Member

10 Posts

Posted - 2007-11-28 : 15:44:00

Hello all,
I hope someone can help. I am trying to get the data from an sql table and insert it into excel into a new sheet. This works great going from access to excel but I can’t seem to get it to work
for sql & excel. Excel version is 97.

I know about all the other ways (openrowset,dts,ect) but I would really like to get the select into to work.
I searched the net but can’t seem to locate any info on this. I know someone in this forum has done it.
Thanks for any help.
Larry

Below is the source:

pathxls = "C:\test\thexlfile.xls"
wrksht = "newsheet"
Sql = "Select * INto [EXCEL 8.0 ; DATABASE="
Sql = Sql & pathxls & "].[" & wrksht & "]" & " from sql_table_name "

Dim cn As New ADODB.Connection
If cn.State <> adStateClosed Then cn.Close

server_source = "sqlServer"
t1 = "SQLOLEDB.1;Integrated Security = SSPI;"
t1 = t1 & "Persist Security Info=False;"
t1 = t1 & "Initial Catalog = sqldata;Data Source = " & server_source & ";"

cn.Provider = t1
cn.Open

Dim rst As New ADODB.Recordset
Set rst = cn.Execute(Sql, rcdssql)
MsgBox rst.Fields(0)

anonymous1
Posting Yak Master

185 Posts

Posted - 2007-11-28 : 16:06:36

take a look at this topic...
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926

uplate
Starting Member

10 Posts

Posted - 2007-11-28 : 16:15:25

Thanks for your quick response. The problem with that link is that it uses an insert not a select into. This means the excel sheet needs to exists and the col headers in the sheet. My need is to create the new sheet on the fly and col headers may be different each time. I have a work around that creates a new sheet in excel using the sheet.add then paste col headers that I copy from a template, then I use the insert into.

What I still don't understand is why the Jet engine works fine with access. Did MS leave this out of the sql provider or am I missing something. I don't have much experience with sql.
Thanks

anonymous1
Posting Yak Master

185 Posts

Posted - 2007-11-28 : 16:40:06

don't recall ever seeing select into openrowset, nothing found with quick search of BOL & google.

assuming the resultset does not change frequently, usually a template excel file is created as empty. then copy the file with name change (maybe add datetime) and finally populate. is that not a workable solution for you?

uplate
Starting Member

10 Posts

Posted - 2007-11-28 : 16:48:36

Thanks for the input, it works and is one of my work arounds but would like to get select into t save a lot of conversion time.There has to be a way.
Thanks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-11-29 : 01:25:08

Give this a try http://weblogs.sqlteam.com/mladenp/archive/2006/07/25/10771.aspx

Madhivanan

Failing to plan is Planning to fail

uplate
Starting Member

10 Posts

Posted - 2007-11-29 : 11:56:47

Thanks, that was good stuff.
I am going to use the openrowset.
Thanks for your help
Larry

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources