Guys (and girls),

   How do I create a Multisheet Excel file using ASP, retrieving the data from a SQL Server? I already know how to do it, but only works for single-sheet files


   thanks in advance,
     Carlos:D

Carlos Manuel Colina
Junior Web Developer
Multipuerta, CA
http://www.multipuerta.net
ccolina@multipuerta.com
phone: +58 212 2066314
SMS/mobile: +58 412 6139001

check out http://www.greggriffiths.org/webdev/both/excel/ which covers automating Excel via ASP, it should provide some tips and links for you.

You can create new worksheets in an excel file through ASP. You can treat a worksheet as a table. You can run a create table statement with connection object to create new excel sheets. Here is a sample script.

Dim cnn_xls
Set cnn_xls = Server.CreateObject("ADODB.Connection")
With cnn_xls
    .Provider = "Microsoft.Jet.OLEDB.4.0"
    .ConnectionString = "Data Source=" & Server.MapPath(".") & "\sample.xls" & ";" & _
            "Extended Properties=""Excel 8.0;"""
    .Open
End With
dim qry_create_sheet
qry_create_sheet="CREATE TABLE [Report_sheet] (Col1 text(10), Col2 text(10), Col3 text(30), Col4 Number)"
cnn_xls.Execute qry_create_sheet
Response.Write("New worksheet created")

To run this :

Put an excel file by name sample.xls in the same directory as the script.

After running, you can see that one worksheet in the name Report_sheet is created in that excel file.

Running this for a second time will create an error as there is already a worksheet with name Report_sheet.

Best wishes

Madhu