This is a multi-part message in MIME format.



------=_NextPart_000_0007_01C1A1A3.AB367AA0

Content-Type: text/plain;

	charset="iso-8859-1"

Content-Transfer-Encoding: quoted-printable



I know how to create one excel worksheet from asp using the 

response.contenttype=3D"application/x-msexcel" method and I know how to 

write the rows and columns using the response.write method.  However, I 

need to create five worksheets in a workbook.  I've tried a couple of 

different approaches but none are working.  Any assistance would be 

appreciated. 



Thank you,

Beverly

This is a multi-part message in MIME format.



------=_NextPart_000_0058_01C1A1D1.FDE27C80

Content-Type: text/plain;

	charset="iso-8859-1"

Content-Transfer-Encoding: quoted-printable



Beverly,



could you put here a piece of code showing how to create the excel 

worksheet using the response method?



Thanks,



Alfredo

  Beverly Schochet <beverlyschochet@h...> escribi=F3 en el 

mensaje de noticias 137690@a..._web_howto...

  I know how to create one excel worksheet from asp using the 

response.contenttype=3D"application/x-msexcel" method and I know how to 

write the rows and columns using the response.write method.  However, I 

need to create five worksheets in a workbook.  I've tried a couple of 

different approaches but none are working.  Any assistance would be 

appreciated. 



  Thank you,

  Beverly

here's the code I use to write to excel:

<% @language='vbscript' %>

<%option explicit%>

<%

response.buffer = true 

response.contenttype="application/x-msexcel"

response.write "<Table border=1>"

'the table cells

response.write "<TR>"

response.write "<td>"

response.write "</td>"

response.write "<td colspan=4>"

response.write "Practice Name:"

response.write "</td>"

response.write "</TR>"

'row2

response.write "<TR>"

response.write "<td>"

response.write "</td>"

response.write "<td colspan=4>"

response.write "Financial Benchmarking Report"

response.write "</td>"

response.write "</TR>"

'row3

response.write "<tr>"

response.write "</tr>"

'row4

response.write "<tr>"

response.write "<td>"

response.write "</td>"

response.write "<td>"

response.write "</td>"

response.write "<td>"

response.write "Year End"

response.write "</td>"

response.write "<td>"

response.write "Year End"

response.write "</td>"

response.write "<td>"

response.write "Year End"

response.write "</td>"

response.write "</tr>"

'row 5

contains formulas based on cell locations

That's amazing! Every day you learn something new...

Once I created a VisualBasic for Applications (access) application, to

programmatically export some data to an excel worksheet.  I had to create

the excel application object, and then populate it with excel application

methods. Painfully slow. But, I think that is s what you have to do to

create a workbook with several sheets, but excel application must be

installled on server. I've spent all morning learning thos, because I think

is quite important, it can be used to dump an entire web database into a

easy user-editable format, then upload again back to the server, and then

re-populated in the SQL database using ADO methods... that's another story.



I'm sorry to say that I couldn't test this code myself, because I don't have

office installed in a NT server... I just use my PWS an public NT servers...

the showed code doesn't work with it, and I don't know how to set

AspAllowOutOfProcComponents in the PWS metabase   >:(



I really hope this can help you.



Now, here is the code. Notes on the end:



<%Response.buffer = TRUE





   ' Get the IIsWebService Admin object

   Set oWebService = GetObject("IIS://LocalHost/W3svc")



   ' Enable AspAllowOutOfProcComponents

   oWebService.Put "AspAllowOutOfProcComponents", True



   ' Save the changed value to the metabase

   oWebService.SetInfo







Set xlwbk = server.CreateObject("Excel.WorkBook")

Set Sheet1 = xlwbk.Worksheets.Add

Sheet1.Activate

Sheet1.Cells(1, 1) = "Sheet 1"

Sheet1.Cells(2, 1) = "Hello"

Sheet1.Cells(2, 2) = "World!"



Set Sheet2 = xlwbk.Worksheets.Add

Sheet2.Activate

Sheet2.Cells(1, 1) = "Sheet 2"

Sheet2.Cells(2, 1) = "That's "

Sheet2.Cells(2, 2) = "All!"



xlwbk.SaveAs("myExcelBook")

xlwbk.Close

Set xlwbk = nothing



'==========================================================

'That's for the Excel file. Now, to present it.

'==========================================================



%>

<!--METADATA TYPE="typelib"

      UUID="00000205-0000-0010-8000-00AA006D2EA4"

     NAME="ADODB Type Library"

-->

<%



'Create a stream object

Dim objStream

Set objStream = Server.CreateObject("ADODB.Stream")



'Open a GIF file

objStream.Type = adTypeBinary

objStream.Open

objStream.LoadFromFile "C:\Inetpub\wwwroot\myExcelBook"



'Output the contents of the stream object

Response.ContentType = "application/x-msexcel"

Response.BinaryWrite objStream.Read



'Clean up....

objStream.Close

Set objStream = Nothing

%>



Notes

1. The first lines about AspAllowOutOfProcComponents are there because in

IIS4 you can't use ActiveX exe components.

2. The excel objects to create and populate. You can format, change, etc,

using the objects (see excel help for programming)

3. Metadata allow you to use constants in the ADODB type library (you need

at least ADO 2.5 )

4. Stream object is to dump the binary info to customer's explorer. You

can't use FileSystemObject 'cause it works only for text data.

5. Finally a bit of warning: Microsoft does not encourages you to use

automation for office components:





Beverly Schochet <beverlyschochet@h...> escribió en el mensaje de

noticias 137719@a..._web_howto...

>

> here's the code I use to write to excel:

> <% @language='vbscript' %>

> <%option explicit%>

> <%

> response.buffer = true

> response.contenttype="application/x-msexcel"

> response.write "<Table border=1>"

> 'the table cells

> response.write "<TR>"

> response.write "<td>"

> response.write "</td>"

> response.write "<td colspan=4>"

> response.write "Practice Name:"

> response.write "</td>"

> response.write "</TR>"

> 'row2

> response.write "<TR>"

> response.write "<td>"

> response.write "</td>"

> response.write "<td colspan=4>"

> response.write "Financial Benchmarking Report"

> response.write "</td>"

> response.write "</TR>"

> 'row3

> response.write "<tr>"

> response.write "</tr>"

> 'row4

> response.write "<tr>"

> response.write "<td>"

> response.write "</td>"

> response.write "<td>"

> response.write "</td>"

> response.write "<td>"

> response.write "Year End"

> response.write "</td>"

> response.write "<td>"

> response.write "Year End"

> response.write "</td>"

> response.write "<td>"

> response.write "Year End"

> response.write "</td>"

> response.write "</tr>"

> 'row 5

> contains formulas based on cell locations

>

>