This stored procedure can be used to insert the result set of theparticular select statement into Excel file (c:\ImportToExcel.xls,by default).You can pass the server name, user name, user password, the selectstatement to execute, and the file name to store the results set,as in the example below:

EXEC ExportToExcel @server = '.', @uname = 'sa', @QueryText = 'SELECT au_fname FROM pubs..authors', @filename = 'c:\ImportToExcel.xls'

/*Version: SQL Server 7.0/2000Created by: Alexander Chigrik- all about MS SQL(SQL Server Articles, FAQ, Scripts, Tips and Test Exams).

This stored procedure can be used to insert the result set of theparticular select statement into Excel file (c:\ImportToExcel.xls,by default).You can pass the server name, user name, user password, the selectstatement to execute, and the file name to store the results set,as in the example below:

EXEC ExportToExcel @server = '.', @uname = 'sa', @QueryText = 'SELECT au_fname FROM pubs..authors', @filename = 'c:\ImportToExcel.xls'*/

IF OBJECT_ID('ExportToExcel') IS NOT NULL DROP PROC ExportToExcelGO

CREATE PROCEDURE ExportToExcel ( @server sysname = null, @uname sysname = null, @pwd sysname = null, @QueryText varchar(200) = null, @filename varchar(200) = 'c:\ImportToExcel.xls')ASDECLARE @SQLServer int, @QueryResults int, @CurrentResultSet int, @object int, @WorkBooks int, @WorkBook int, @Range int, @hr int, @Columns int, @Rows int, @indColumn int, @indRow int, @off_Column int, @off_Row int, @code_str varchar(100), @result_str varchar(255)

IF @QueryText IS NULL BEGIN PRINT 'Set the query string' RETURN END

-- Sets the server to the local serverIF @server IS NULL SELECT @server = @@servername

-- Sets the username to the current user nameIF @uname IS NULL SELECT @uname = SYSTEM_USER

SET NOCOUNT ON

EXEC @hr = sp_OACreate 'SQLDMO.SQLServer', @SQLServer OUTIF @hr <> 0BEGIN PRINT 'error create SQLDMO.SQLServer' RETURNEND

-- Connect to the SQL ServerIF @pwd IS NULL BEGIN EXEC @hr = sp_OAMethod @SQLServer, 'Connect', null, @server, @uname IF @hr <> 0 BEGIN PRINT 'error Connect' RETURN END ENDELSE BEGIN EXEC @hr = sp_OAMethod @SQLServer, 'Connect', null, @server, @uname, @pwd IF @hr <> 0 BEGIN PRINT 'error Connect' RETURN END END

SELECT @result_str = 'ExecuteWithResults("' + @QueryText + '")'EXEC @hr = sp_OAMethod @SQLServer, @result_str, @QueryResults OUTIF @hr <> 0BEGIN PRINT 'error with method ExecuteWithResults' RETURNEND

EXEC @hr = sp_OAMethod @QueryResults, 'CurrentResultSet', @CurrentResultSet OUTIF @hr <> 0BEGIN PRINT 'error get CurrentResultSet' RETURNEND

EXEC @hr = sp_OAMethod @QueryResults, 'Columns', @Columns OUTIF @hr <> 0BEGIN PRINT 'error get Columns' RETURNEND

EXEC @hr = sp_OAMethod @QueryResults, 'Rows', @Rows OUTIF @hr <> 0BEGIN PRINT 'error get Rows' RETURNEND

EXEC @hr = sp_OACreate 'Excel.Application', @object OUTIF @hr <> 0BEGIN PRINT 'error create Excel.Application' RETURNEND

EXEC @hr = sp_OAGetProperty @object, 'WorkBooks', @WorkBooks OUTIF @hr <> 0BEGIN PRINT 'error create WorkBooks' RETURNEND

EXEC @hr = sp_OAGetProperty @WorkBooks, 'Add', @WorkBook OUTIF @hr <> 0BEGIN PRINT 'error with method Add' RETURNEND

EXEC @hr = sp_OAGetProperty @object, 'Range("A1")', @Range OUTIF @hr <> 0BEGIN PRINT 'error create Range' RETURNEND

SELECT @indRow = 1SELECT @off_Row = 0SELECT @off_Column = 1

WHILE (@indRow <= @Rows)BEGINSELECT @indColumn = 1

WHILE (@indColumn <= @Columns)BEGIN

EXEC @hr = sp_OAMethod @QueryResults, 'GetColumnString', @result_str OUT, @indRow, @indColumnIF @hr <> 0BEGIN PRINT 'error get GetColumnString' RETURNEND

EXEC @hr = sp_OASetProperty @Range, 'value', @result_strIF @hr <> 0BEGIN PRINT 'error set value' RETURNEND

EXEC @hr = sp_OAGetProperty @Range, 'Offset', @Range OUT, @off_Row, @off_ColumnIF @hr <> 0BEGIN PRINT 'error get Offset' RETURNEND

SELECT @indColumn = @indColumn + 1

END

SELECT @indRow = @indRow + 1SELECT @code_str = 'Range("A' + LTRIM(str(@indRow)) + '")'EXEC @hr = sp_OAGetProperty @object, @code_str, @Range OUTIF @hr <> 0BEGIN PRINT 'error create Range' RETURNEND

END

SELECT @result_str = 'exec master..xp_cmdshell ''del ' + @filename + ''', no_output'EXEC(@result_str)SELECT @result_str = 'SaveAs("' + @filename + '")'EXEC @hr = sp_OAMethod @WorkBook, @result_strIF @hr <> 0BEGIN PRINT 'error with method SaveAs' RETURNEND

EXEC @hr = sp_OAMethod @WorkBook, 'Close'IF @hr <> 0BEGIN PRINT 'error with method Close' RETURNEND

EXEC @hr = sp_OADestroy @objectIF @hr <> 0BEGIN PRINT 'error destroy Excel.Application' RETURNEND

EXEC @hr = sp_OADestroy @SQLServerIF @hr <> 0BEGIN PRINT 'error destroy SQLDMO.SQLServer' RETURNENDGO