Ccreate xls file based on the query passed

/******************************************************************************************
'            
' PROCEDURE:    dbo.HPSP_UT_ExcelReport
'
' Author:   Leah Kats
' Created Date:     05/25/2005
'This utility stored 
'*******************************************************************************************/
ALTER    PROC HPSP_UT_ExcelReport
 @FileNamevarchar(25)
,@SQLvarchar(8000)
,@WKS_Name varchar(100)
,@DDLvarchar(500)
,@FilePathvarchar(250)

AS
SET NOCOUNT ON

DECLARE @Conn int -- ADO Connection object to create XLS
, @hr int -- OLE return value
, @src varchar(255) -- OLE Error Source
, @desc varchar(255) -- OLE Error Description
, @Path varchar(255) -- Drive or UNC path for XLS
, @Connect varchar(255) -- OLE DB Connection string for Jet 4 Excel ISAM
, @WKS_Created bit -- Whether the XLS Worksheet exists
, @xlsDDLvarchar(1000)
, @LocalDDLvarchar(1000)
, @ServerName nvarchar(128) -- Linked Server name for XLS
, @Recs int -- Number of records added to XLS
, @Log bit -- Whether to log process detail

-- Init variables
SELECT @Recs = 0
, @Log = 1 
-------------------------------------------------------------------------------------------------------
--  assign a path and name for the XLS file
-------------------------------------------------------------------------------------------------------
SET @Path = @FilePath + @FileName  + CONVERT(varchar(10),GETDATE(),112)+'.xls'
-------------------------------------------------------------------------------------------------------
-- assign the ADO connection string for the XLS creation
-------------------------------------------------------------------------------------------------------
SET @Connect = 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source='+@Path+';Extended Properties=Excel 8.0'
-------------------------------------------------------------------------------------------------------
-- assign the Linked Server name for the XLS population
-------------------------------------------------------------------------------------------------------
SET @ServerName = 'EXCEL_TEST'

SET @xlsDDL = 'CREATE TABLE '+@WKS_Name+ ' (' +  @DDL + ')'

SET @LocalDDL = REPLACE(@DDL, 'text', ' ')
SET @SQL = 'INSERT INTO '+@ServerName+'...'+@WKS_Name +  ' (' +  @LocalDDL + ') ' +  @SQL

IF @Log = 1 PRINT 'Created OLE ADODB.Connection object'
-- Create the Conn object
EXEC @hr = sp_OACreate 'ADODB.Connection', @Conn OUT

IF @hr <> 0
BEGIN
-- Return OLE error
EXEC sp_OAGetErrorInfo @Conn, @src OUT, @desc OUT 
SELECT Error=convert(varbinary(4),@hr), Source=@src, Description=@desc
EXEC @hr = sp_OADestroy @Conn
RETURN
END

IF @Log = 1 PRINT char(9)+'Assigned ConnectionString property'
-- Set a the Conn object's ConnectionString property
--   Work-around for error using a variable parameter on the Open method
EXEC @hr = sp_OASetProperty @Conn, 'ConnectionString', @Connect

IF @hr <> 0
BEGIN
-- Return OLE error
EXEC sp_OAGetErrorInfo @Conn, @src OUT, @desc OUT 
SELECT Error=convert(varbinary(4),@hr), Source=@src, Description=@desc
RETURN
END

IF @Log = 1 PRINT char(9)+'Open Connection to XLS, for file Create or Append'

-- Call the Open method to create the XLS if it does not exist, can't use parameters
EXEC @hr = sp_OAMethod @Conn , 'Open'

IF @hr <> 0
BEGIN
-- Return OLE error
EXEC sp_OAGetErrorInfo @Conn, @src OUT, @desc OUT 
SELECT Error=convert(varbinary(4),@hr), Source=@src, Description=@desc
RETURN
END

EXEC @hr = sp_OAMethod @Conn, 'Execute', NULL, @xlsDDL, NULL, 129 -- adCmdText + adExecuteNoRecords

IF @hr = 0x80040E14 
BEGIN
PRINT char(9)+''''+@WKS_Name+''' Worksheet exists for append'
SET @WKS_Created = 0
END
BEGIN
SET @hr = 0 -- ignore these errors
END

IF @hr <> 0
BEGIN
-- Return OLE error
EXEC sp_OAGetErrorInfo @Conn, @src OUT, @desc OUT 
SELECT Error=convert(varbinary(4),@hr), Source=@src, Description=@desc
RETURN
END

IF @Log = 1 PRINT 'Destroyed OLE ADODB.Connection object'
--!!!!!!!!!!!!!! IMPORTANT !!!!!!!!!!!!!!!!!!!
EXEC @hr = sp_OADestroy @Conn
IF @hr <> 0
BEGIN
-- Return OLE error
EXEC sp_OAGetErrorInfo @Conn, @src OUT, @desc OUT 
SELECT Error=convert(varbinary(4),@hr), Source=@src, Description=@desc
RETURN
END

IF NOT EXISTS(SELECT srvname from master.dbo.sysservers where srvname = @ServerName)
BEGIN
IF @Log = 1 PRINT 'Created Linked Server '''+@ServerName+''' and Login'
EXEC sp_addlinkedserver @server = @ServerName
    , @srvproduct = 'Microsoft Excel Workbook'
    , @provider = 'Microsoft.Jet.OLEDB.4.0'
    , @datasrc = @Path
    , @provstr = 'Excel 8.0' 

EXEC sp_addlinkedsrvlogin @ServerName, 'false' 
END

EXEC (@SQL)
PRINT char(9)+'Populated '''+@WKS_Name+''' table with '+CONVERT(varchar,@@ROWCOUNT)+' Rows'

IF EXISTS(SELECT srvname from master.dbo.sysservers where srvname = @ServerName)
BEGIN
IF @Log = 1 PRINT 'Deleted Linked Server '''+@ServerName+''' and Login'
EXEC sp_dropserver @ServerName, 'droplogins'
END



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO