I have a DTS that exports a view to an excel file, is it possible to append
data to an excel file, it seems if it sees that the sheet is available it
creates a new sheet,
I have a logo in my excel file and I want to append the rows below it.JIM
create procedure file_write_text(@.file_name varchar(1000),
@.text varchar(8000),
@.append bit = 0)
as
/*
exec file_write_text 'c:\TEST.XLS','hello'
exec file_write_text 'c:\TEST.XLS',' world',1
*/
declare @.fso int
declare @.ts int
declare @.rv int
exec @.rv = sp_oacreate "scripting.filesystemobject", @.fso OUTPUT, 1
if @.rv <> 0 goto eh
if @.append = 1
begin
--open the text stream for append, will fail if the file doesn't exist
exec @.rv = sp_oamethod @.fso,"opentextfile", @.ts OUTPUT, @.file_name, 8
if @.rv <> 0 goto eh
end
else
begin
--create a new text file, overwriing if necessary
exec @.rv = sp_oamethod @.fso,"createtextfile", @.ts OUTPUT, @.file_name, -1
if @.rv <> 0 goto eh
end
exec @.rv = sp_oamethod @.ts,"write",null ,@.text
if @.rv <> 0 goto eh
exec @.rv = sp_oamethod @.ts,"close"
if @.rv <> 0 goto eh
exec sp_oadestroy @.ts
exec sp_oadestroy @.fso
return 0
eh:
declare @.es varchar(512)
declare @.ed varchar(512)
exec sp_oageterrorinfo null, @.es OUTPUT, @.ed OUTPUT
raiserror(@.ed,16,1)
exec sp_oadestroy @.ts
exec sp_oadestroy @.fso
return 1
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:AFE084C6-4A5F-433D-B693-A3CCEAC70596@.microsoft.com...
>I have a DTS that exports a view to an excel file, is it possible to append
> data to an excel file, it seems if it sees that the sheet is available it
> creates a new sheet,
> I have a logo in my excel file and I want to append the rows below it.
>|||Thanks for the reply Uri, is this for text file or Excel file (xls, not csv)
?
"Uri Dimant" wrote:
> JIM
> create procedure file_write_text(@.file_name varchar(1000),
> @.text varchar(8000),
> @.append bit = 0)
> as
> /*
> exec file_write_text 'c:\TEST.XLS','hello'
> exec file_write_text 'c:\TEST.XLS',' world',1
> */
> declare @.fso int
> declare @.ts int
> declare @.rv int
> exec @.rv = sp_oacreate "scripting.filesystemobject", @.fso OUTPUT, 1
> if @.rv <> 0 goto eh
> if @.append = 1
> begin
> --open the text stream for append, will fail if the file doesn't exist
> exec @.rv = sp_oamethod @.fso,"opentextfile", @.ts OUTPUT, @.file_name, 8
> if @.rv <> 0 goto eh
> end
> else
> begin
> --create a new text file, overwriing if necessary
> exec @.rv = sp_oamethod @.fso,"createtextfile", @.ts OUTPUT, @.file_name, -1
> if @.rv <> 0 goto eh
> end
> exec @.rv = sp_oamethod @.ts,"write",null ,@.text
> if @.rv <> 0 goto eh
> exec @.rv = sp_oamethod @.ts,"close"
> if @.rv <> 0 goto eh
> exec sp_oadestroy @.ts
> exec sp_oadestroy @.fso
> return 0
> eh:
> declare @.es varchar(512)
> declare @.ed varchar(512)
> exec sp_oageterrorinfo null, @.es OUTPUT, @.ed OUTPUT
> raiserror(@.ed,16,1)
> exec sp_oadestroy @.ts
> exec sp_oadestroy @.fso
> return 1
>
>
> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
> news:AFE084C6-4A5F-433D-B693-A3CCEAC70596@.microsoft.com...
>
>|||It should be doable for both types
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:D751008B-0C20-4F1E-A07C-FA6108F2CD08@.microsoft.com...[vbcol=seagreen]
> Thanks for the reply Uri, is this for text file or Excel file (xls, not
> csv)?
>
> "Uri Dimant" wrote:
>
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment