Export query to Excel indicate the sheetname

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)

Export query to Excel indicate the sheetname

Author

Topic

andryi
Starting Member

17 Posts

Posted - 2010-03-15 : 23:51:42

Hi all, I've got a question, how can i to export data for example :
select * from user to an excel file with your sheet, for example, Book1, SheetUsers.

I read this topic http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926

but I got a problem If I use the option 1, I can create a file , ok, but If I got 10 registries, and I run once the procedure I got in the excel 10 registries, but If I run 5 times, I've 50 registries when I only want 10 registries, the option 4 (for now, for me , is the best) I run 5 times and I've got 10 registries so is Ok, but I want to export an only one excel file so If I got 2 querys , for example

select * from user ---> to SheetUser (sheetname)
select * from city ---> to SheetCity (sheetname)

I don't know how can I indicate the name of sheet.

and the option 5 , is good, but the same I don't know how can I indicate the name of the sheet, can anybody help me please

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-16 : 00:48:46

isntead of SheetName$ in example use your actual sheet name

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

andryi
Starting Member

17 Posts

Posted - 2010-03-16 : 01:04:22

ok, I run query1 and is saved in the sheet1, and after I run query2 to the same excel file, is saved to sheet2 but sheet1 not exists, so I think is because delete all the information and then create the new information

andryi
Starting Member

17 Posts

Posted - 2010-03-16 : 11:30:03

I found this post
http://topic.csdn.net/u/20090706/11/0940f1bb-a1d3-4402-a278-109a3560a5ae.html
and when I try I've got the excel file but I can't open the file and ever the size is 5kb

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-17 : 02:56:10

quote:
Originally posted by andryi

ok, I run query1 and is saved in the sheet1, and after I run query2 to the same excel file, is saved to sheet2 but sheet1 not exists, so I think is because delete all the information and then create the new information

That willnot happen when you correctly specify the sheetnames

Can you post the code you used?

Madhivanan

Failing to plan is Planning to fail

andryi
Starting Member

17 Posts

Posted - 2010-03-17 : 15:31:08

Hi madhivanan, well this is the query that I use:

EXEC sp_makewebtask
@outputfile='C:\REPORTS\SCOP\User.xls',
@query='SELECT * FROM User',
@colheaders =1,
--@FixedFont=0,
@lastupdated=0,
@resultstitle='List of User'

I don't know how to put the sheetname, and the other options for example option1 : it's necesary hace the file ande the name of columns, but if I run once ÇI've got 10 registries and I run again I've got 20, one more, 30 ... so that's the reason why I don't user the option1

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-18 : 03:22:09

Did you try using other points?

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources