Slip into another excel file

[vmgodfrey]

Here is my program that creates an excel spreadsheet from SQL, but I don't know how to program it so that if the section name changes within one excel document to create a second excel spreadsheet with the 2nd section name.


CREATE PROCEDURE sp_PIG_CreateNewSheet
@Location varchar(4),
@FileName varchar(100),
@SectionName VARCHAR(100)
AS

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
, @WKS_Name varchar(128) -- Name of the XLS Worksheet (table)
, @ServerName nvarchar(128) -- Linked Server name for XLS
, @DDL varchar(8000) -- Jet4 DDL for the XLS WKS table creation
, @SQL varchar(8000)
, @Recs int -- Number of records added to XLS
, @Log bit -- Whether to log process detail

SELECT @Recs = 0
-- %%% 1 = Verbose output detail, helps find problems, 0 = minimal output detail
, @Log = 1
SET @Path = 'D:\Local Property Inventory\Converted\'+ ltrim(rtrim(@Location)) + '\'+ @SectionName +'.xls'
SET @Connect = 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source='+@Path+';Extended Properties=Excel 8.0'
SET @ServerName = 'Property_Convert'
SET @WKS_Name = 'Sheet1'

SET @DDL = 'CREATE TABLE '+@WKS_Name+' (Loc_Num text ,'
SET @DDL = @DDL + 'Item_nbr text ,'
SET @DDL = @DDL + 'Section_name text ,'
SET @DDL = @DDL + 'Property_type text ,'
SET @DDL = @DDL + 'Construction_Status text ,'
SET @DDL = @DDL + 'Row_nbr NUMBER ,'
SET @DDL = @DDL + 'Row_Alpha text ,'
SET @DDL = @DDL + 'Lot_nbr NUMBER ,'
SET @DDL = @DDL + 'Lot_Alpha text ,'
SET @DDL = @DDL + 'Space_nbr NUMBER ,'
SET @DDL = @DDL + 'Space_Alpha text ,'
SET @DDL = @DDL + 'Sale_status text ,'
SET @DDL = @DDL + 'Depth text ,'
SET @DDL = @DDL + 'Multispace_selling_unit text ,'
SET @DDL = @DDL + 'Square_footage NUMBER,'
SET @DDL = @DDL + 'Comments text ,'
SET @DDL = @DDL + 'Sold float NULL ,'
SET @DDL = @DDL + 'Available float NULL ,'
SET @DDL = @DDL + 'Unusable float NULL ,'
SET @DDL = @DDL + 'Reserved float NULL ,'
SET @DDL = @DDL + 'Total float NULL ,'
SET @DDL = @DDL + 'Contract_Num text ,'
SET @DDL = @DDL + 'Sale_Date smalldatetime NULL ,'
SET @DDL = @DDL + 'Owners_Last_Name text ,'
SET @DDL = @DDL + 'Owners_First_Name text ,'
SET @DDL = @DDL + 'Owners_Middle_Initial text ,'
SET @DDL = @DDL + 'Owners_Street_Address text ,'
SET @DDL = @DDL + 'Owners_City text ,'
SET @DDL = @DDL + 'Owners_State text ,'
SET @DDL = @DDL + 'Owners_Zip text ,'
SET @DDL = @DDL + 'Purchasers_Last_Name text ,'
SET @DDL = @DDL + 'Purchasers_First_Name text ,'
SET @DDL = @DDL + 'Purchasers_Middle_Initial text ,'
SET @DDL = @DDL + 'Interment_Date text ,'
SET @DDL = @DDL + '2nd_Right_Interment text ,'
SET @DDL = @DDL + 'Deceased_Last_Name text ,'
SET @DDL = @DDL + 'Deceased_First_Name text ,'
SET @DDL = @DDL + 'Deceased_Middle_Initial text ,'
SET @DDL = @DDL + 'PMA_Cost_Allocation text ,'
SET @DDL = @DDL + 'Sl_No NUMBER,'
SET @DDL = @DDL + 'HMIS_LOC text )'

SET @SQL = 'INSERT INTO '+@ServerName+'...'+@WKS_Name+' ( Loc_Num, Item_nbr, Section_name, Property_type, Construction_Status, Row_nbr, Row_Alpha, Lot_nbr, Lot_Alpha, Space_nbr, Space_Alpha, Sale_status, Depth, [2nd_Right_Interment], Multispace_selling_unit, Square_footage, Comments, Sl_No ) '
SET @SQL = @SQL +'SELECT '''+@Location+''' as loc_num,'
SET @SQL = @SQL + 'CAST(CAST(f1 AS BIGINT) AS VARCHAR(10)) as item_nbr , '
SET @SQL = @SQL + 'f2 as [section namE] , '
SET @SQL = @SQL + 'f3 as [property type], '
SET @SQL = @SQL + 'f4 as [Construction status], '
SET @SQL = @SQL + 'CAST(f5 AS VARCHAR) as Row_nbr, '
SET @SQL = @SQL + 'f6 as Row_Alpha, '
SET @SQL = @SQL + 'CAST(F7 AS VARCHAR) as Lot_nbr, '
SET @SQL = @SQL + 'f8 as Lot_Alpha, '
SET @SQL = @SQL + 'CAST(f9 AS VARCHAR) as Space_nbr , '
SET @SQL = @SQL + 'f10 as Space_Alpha, '
SET @SQL = @SQL + 'f11 as Sales_Status, '
SET @SQL = @SQL + 'f12 as Depth, '
SET @SQL = @SQL + 'f13 as [2nd_right], '
SET @SQL = @SQL + 'f14 as Multispace, '
SET @SQL = @SQL + 'CAST(f15 AS VARCHAR) as square_foot, '
SET @SQL = @SQL + 'f16 as comments,'
SET @SQL = @SQL + 'Sl_No as Sl_No'
SET @SQL = @SQL+' FROM tmp_Property_id'

IF @Log = 1 PRINT 'Created OLE ADODB.Connection object'
-- Create the Conn object
EXEC @hr = sp_OACreate 'ADODB.Connection', @Conn OUT
IF @hr <> 0 -- have to use <> as OLE / ADO can return negative error numbers
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)+'Assigned ConnectionString property'
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'
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

IF @Log = 1 PRINT char(9)+'Execute DDL to create '''+@WKS_Name+''' worksheet'
EXEC @hr = sp_OAMethod @Conn, 'Execute', NULL, @DDL, NULL, 129 -- adCmdText + adExecuteNoRecords

-- 0x80040E14 for table exists in ADO
IF @hr = 0x80040E14
OR @hr = 0x80042732
BEGIN
-- Trap these OLE Errors
IF @hr = 0x80040E14
BEGIN
PRINT char(9)+''''+@WKS_Name+''' Worksheet exists for append'
SET @WKS_Created = 0
END
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'
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'
-- no login name or password are required to connect to the Jet4 ISAM linked server
EXEC sp_addlinkedsrvlogin @ServerName, 'false'
END

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

--DROP TABLE tmp_Property

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