We are customizing Report Manager and have been able to get the
reportingservices.css style sheet working well.
As we will be calling reports from within Report Manager, we would like to
apply the HTMLviewer.css file to every report called through Report Manger.
This would enable us to get the same look and feel in the Report Manager as
well as the parameter area / HTML viewer.
Does anyone know if it is possible to implement this?
Thanks
DarrenHi!
Really, you have to snag the report call inside the report manager and to
replace it with your url (a call to report server which differs from the one
generated by report manager only in that it the name of css file as a
parameter - rs:StylSheet it seems to me). It's not very difficult, but
requires concentration. I can recommend a book - Hitchiker's guide to
Reporting Services by Peter Blackburn. It can be very helpful.
Regards, Ailil
"Darren" wrote:
> We are customizing Report Manager and have been able to get the
> reportingservices.css style sheet working well.
> As we will be calling reports from within Report Manager, we would like to
> apply the HTMLviewer.css file to every report called through Report Manger.
> This would enable us to get the same look and feel in the Report Manager as
> well as the parameter area / HTML viewer.
> Does anyone know if it is possible to implement this?
> Thanks
> Darren
>|||Great,
Thanks Ailil - so it can be done! I've actually got that book, but not
knowing Java, got a bit lost. I'll get our Java person to have a look at it
- hopefully it will be possible to pass the report string as a variable and
simply add the stylesheet command to the end.
Darren
"Ailil" wrote:
> Hi!
> Really, you have to snag the report call inside the report manager and to
> replace it with your url (a call to report server which differs from the one
> generated by report manager only in that it the name of css file as a
> parameter - rs:StylSheet it seems to me). It's not very difficult, but
> requires concentration. I can recommend a book - Hitchiker's guide to
> Reporting Services by Peter Blackburn. It can be very helpful.
> Regards, Ailil
> "Darren" wrote:
> > We are customizing Report Manager and have been able to get the
> > reportingservices.css style sheet working well.
> >
> > As we will be calling reports from within Report Manager, we would like to
> > apply the HTMLviewer.css file to every report called through Report Manger.
> > This would enable us to get the same look and feel in the Report Manager as
> > well as the parameter area / HTML viewer.
> >
> > Does anyone know if it is possible to implement this?
> >
> > Thanks
> > Darren
> >
> >|||Hello!
Now I'm a bit lost ;). Why Java? Where are you going to use Java in the
Report Manager? It can be quite ok without java :), particularly if you
already have the book.
Regards, Ailil
"Darren" wrote:
> Great,
> Thanks Ailil - so it can be done! I've actually got that book, but not
> knowing Java, got a bit lost. I'll get our Java person to have a look at it
> - hopefully it will be possible to pass the report string as a variable and
> simply add the stylesheet command to the end.
> Darren
> "Ailil" wrote:
> > Hi!
> >
> > Really, you have to snag the report call inside the report manager and to
> > replace it with your url (a call to report server which differs from the one
> > generated by report manager only in that it the name of css file as a
> > parameter - rs:StylSheet it seems to me). It's not very difficult, but
> > requires concentration. I can recommend a book - Hitchiker's guide to
> > Reporting Services by Peter Blackburn. It can be very helpful.
> >
> > Regards, Ailil
> >
> > "Darren" wrote:
> >
> > > We are customizing Report Manager and have been able to get the
> > > reportingservices.css style sheet working well.
> > >
> > > As we will be calling reports from within Report Manager, we would like to
> > > apply the HTMLviewer.css file to every report called through Report Manger.
> > > This would enable us to get the same look and feel in the Report Manager as
> > > well as the parameter area / HTML viewer.
> > >
> > > Does anyone know if it is possible to implement this?
> > >
> > > Thanks
> > > Darren
> > >
> > >|||Hi Ailil,
I figured that we would have to intercept the call to the Report Server as
shown on page 533, to replace the standard URL call generated by Report
Manager, with a URL where the parameter 'rc:Stylesheet = htmlviewer' is added
to the end.
Thanks
Darren
"Ailil" wrote:
> Hello!
> Now I'm a bit lost ;). Why Java? Where are you going to use Java in the
> Report Manager? It can be quite ok without java :), particularly if you
> already have the book.
> Regards, Ailil
> "Darren" wrote:
> > Great,
> >
> > Thanks Ailil - so it can be done! I've actually got that book, but not
> > knowing Java, got a bit lost. I'll get our Java person to have a look at it
> > - hopefully it will be possible to pass the report string as a variable and
> > simply add the stylesheet command to the end.
> >
> > Darren
> >
> > "Ailil" wrote:
> >
> > > Hi!
> > >
> > > Really, you have to snag the report call inside the report manager and to
> > > replace it with your url (a call to report server which differs from the one
> > > generated by report manager only in that it the name of css file as a
> > > parameter - rs:StylSheet it seems to me). It's not very difficult, but
> > > requires concentration. I can recommend a book - Hitchiker's guide to
> > > Reporting Services by Peter Blackburn. It can be very helpful.
> > >
> > > Regards, Ailil
> > >
> > > "Darren" wrote:
> > >
> > > > We are customizing Report Manager and have been able to get the
> > > > reportingservices.css style sheet working well.
> > > >
> > > > As we will be calling reports from within Report Manager, we would like to
> > > > apply the HTMLviewer.css file to every report called through Report Manger.
> > > > This would enable us to get the same look and feel in the Report Manager as
> > > > well as the parameter area / HTML viewer.
> > > >
> > > > Does anyone know if it is possible to implement this?
> > > >
> > > > Thanks
> > > > Darren
> > > >
> > > >|||Hi!
Hey :). You mean JavaScript :). It's a bit different thing )
Regards, Ailil
"Darren" wrote:
> Hi Ailil,
> I figured that we would have to intercept the call to the Report Server as
> shown on page 533, to replace the standard URL call generated by Report
> Manager, with a URL where the parameter 'rc:Stylesheet = htmlviewer' is added
> to the end.
> Thanks
> Darren
> "Ailil" wrote:
> > Hello!
> > Now I'm a bit lost ;). Why Java? Where are you going to use Java in the
> > Report Manager? It can be quite ok without java :), particularly if you
> > already have the book.
> >
> > Regards, Ailil
> >
> > "Darren" wrote:
> >
> > > Great,
> > >
> > > Thanks Ailil - so it can be done! I've actually got that book, but not
> > > knowing Java, got a bit lost. I'll get our Java person to have a look at it
> > > - hopefully it will be possible to pass the report string as a variable and
> > > simply add the stylesheet command to the end.
> > >
> > > Darren
> > >
> > > "Ailil" wrote:
> > >
> > > > Hi!
> > > >
> > > > Really, you have to snag the report call inside the report manager and to
> > > > replace it with your url (a call to report server which differs from the one
> > > > generated by report manager only in that it the name of css file as a
> > > > parameter - rs:StylSheet it seems to me). It's not very difficult, but
> > > > requires concentration. I can recommend a book - Hitchiker's guide to
> > > > Reporting Services by Peter Blackburn. It can be very helpful.
> > > >
> > > > Regards, Ailil
> > > >
> > > > "Darren" wrote:
> > > >
> > > > > We are customizing Report Manager and have been able to get the
> > > > > reportingservices.css style sheet working well.
> > > > >
> > > > > As we will be calling reports from within Report Manager, we would like to
> > > > > apply the HTMLviewer.css file to every report called through Report Manger.
> > > > > This would enable us to get the same look and feel in the Report Manager as
> > > > > well as the parameter area / HTML viewer.
> > > > >
> > > > > Does anyone know if it is possible to implement this?
> > > > >
> > > > > Thanks
> > > > > Darren
> > > > >
> > > > >|||Yeah - meant JavaScript not Java.
I've posted another message re changing the default htmlviewer style sheet
as the SP2 readme file says that there is a config setting where you can
change the default style sheet.
Thanks for your help.
Darren
"Ailil" wrote:
> Hi!
> Hey :). You mean JavaScript :). It's a bit different thing )
> Regards, Ailil
> "Darren" wrote:
> > Hi Ailil,
> >
> > I figured that we would have to intercept the call to the Report Server as
> > shown on page 533, to replace the standard URL call generated by Report
> > Manager, with a URL where the parameter 'rc:Stylesheet = htmlviewer' is added
> > to the end.
> >
> > Thanks
> > Darren
> >
> > "Ailil" wrote:
> >
> > > Hello!
> > > Now I'm a bit lost ;). Why Java? Where are you going to use Java in the
> > > Report Manager? It can be quite ok without java :), particularly if you
> > > already have the book.
> > >
> > > Regards, Ailil
> > >
> > > "Darren" wrote:
> > >
> > > > Great,
> > > >
> > > > Thanks Ailil - so it can be done! I've actually got that book, but not
> > > > knowing Java, got a bit lost. I'll get our Java person to have a look at it
> > > > - hopefully it will be possible to pass the report string as a variable and
> > > > simply add the stylesheet command to the end.
> > > >
> > > > Darren
> > > >
> > > > "Ailil" wrote:
> > > >
> > > > > Hi!
> > > > >
> > > > > Really, you have to snag the report call inside the report manager and to
> > > > > replace it with your url (a call to report server which differs from the one
> > > > > generated by report manager only in that it the name of css file as a
> > > > > parameter - rs:StylSheet it seems to me). It's not very difficult, but
> > > > > requires concentration. I can recommend a book - Hitchiker's guide to
> > > > > Reporting Services by Peter Blackburn. It can be very helpful.
> > > > >
> > > > > Regards, Ailil
> > > > >
> > > > > "Darren" wrote:
> > > > >
> > > > > > We are customizing Report Manager and have been able to get the
> > > > > > reportingservices.css style sheet working well.
> > > > > >
> > > > > > As we will be calling reports from within Report Manager, we would like to
> > > > > > apply the HTMLviewer.css file to every report called through Report Manger.
> > > > > > This would enable us to get the same look and feel in the Report Manager as
> > > > > > well as the parameter area / HTML viewer.
> > > > > >
> > > > > > Does anyone know if it is possible to implement this?
> > > > > >
> > > > > > Thanks
> > > > > > Darren
> > > > > >
> > > > > >
Showing posts with label sheet. Show all posts
Showing posts with label sheet. Show all posts
Saturday, February 25, 2012
Thursday, February 16, 2012
append data to an excel file,
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:
>
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:
>
append data to an excel file,
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...
> >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.
> >
>
>|||It should be doable for both types
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:D751008B-0C20-4F1E-A07C-FA6108F2CD08@.microsoft.com...
> 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...
>> >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.
>> >
>>
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...
> >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.
> >
>
>|||It should be doable for both types
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:D751008B-0C20-4F1E-A07C-FA6108F2CD08@.microsoft.com...
> 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...
>> >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.
>> >
>>
append data to an excel file
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.
If you're looking to populate a Worksheet with data at a specific location I think you will probably need to use Excel automation instead of a batch or data access method. See the following for more info:
How to transfer data to an Excel workbook by using Visual Basic .NET
Subscribe to:
Posts (Atom)