quote:
I am trying to figure out how to export the results of a query to Excel with some specific formatting. Is this possible to do without using DTS?
One method could be
-- Inputs:File name of the excel document.
-- Returns:The query result, written directly in Excel document!
DECLARE @err_desc varchar(255)
DECLARE @i int
DECLARE @xls_cell varchar(100)
DECLARE @OProp varchar(100)
DECLARE @O_XLS int
DECLARE @ret_val int
DECLARE @xls int
DECLARE @file varchar(255)
SET @i=0
EXEC @ret_val = sp_OACreate 'Excel.Application', @O_XLS OUTPUT, 4
IF @ret_val <> 0 or @@Error <> 0 GOTO com_handler
EXEC @ret_val = sp_OAMethod @O_XLS, 'Workbooks.Open', @xls OUTPUT,'C:\SUCCESS.xls'
-- in case you are creating new Excel file change the upper row with the following
--EXEC @ret_val = sp_OAMethod @O_XLS, 'Workbooks.Add', @xls OUTPUT
IF @ret_val <> 0 or @@Error <> 0 GOTO com_handler
SET @file = 'C:\SUCCESS.xls'
DECLARE authors_cursor CURSOR FOR
SELECT au_lname FROM authors
order by au_lname asc
OPEN authors_cursor
-- Perform the first fetch.
FETCH NEXT FROM authors_cursor
-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
-- This is executed as long as the previous fetch succeeds.
set @i=@i+1
set @OProp='Application.ActiveWorkbook.Sheets(1).Cells(' + cast(@i as varchar(2)) + ', 1).value'
-- genaration of correct property addressing
EXEC @ret_val = sp_OASetProperty @O_XLS, @OProp, @xls_cell
-- set the value of the property
FETCH NEXT FROM authors_cursor into @xls_cell
--IF @ret_val <> 0 or @@Error <> 0 GOTO com_handler
END
CLOSE authors_cursor
DEALLOCATE authors_cursor
------------------------------êðàé íà ðàáîòàòà ñ êóðñîðà
EXEC @ret_val = sp_OAMethod @O_XLS, 'Application.ActiveWorkbook.Save',Null
-- in case you are creating new Excel file change the upper row with the following
--EXEC @ret_val = sp_OAMethod @O_XLS,'Application.ActiveWorkbook.SaveAs',Null, 'c:\success.xls'
IF @ret_val <> 0 or @@Error <> 0 GOTO com_handler
EXEC @ret_val = sp_OAMethod @O_XLS, 'Quit'
-- I've commented the following line deliberately. SEE NOTE AT THE END
--EXEC sp_OADestroy @O_XLS
GOTO Done
com_handler:
CLOSE authors_cursor
DEALLOCATE authors_cursor
EXEC sp_oageterrorinfo @O_XLS, @ret_val out, @err_desc out
-- the last parameter gives small textual description of the error. It was very usefull!!
select @ret_val, @err_desc
EXEC @ret_val = sp_OAMethod @O_XLS, 'Quit'
EXEC sp_OADestroy @O_XLS
GOTO Done
Done:
-- I've commented the last line deliberately: It was causing the whole automation process in SQL to stop
-- so no futher automation instances could be created until the server was restarted. I read in MSDN that the instance
-- should be destroyed right after the SQL batch is completed, that is why I consider the last row not neccessary. BUT
-- you should check it yourself
--EXEC sp_OAStop
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]