kevin,
You should never attempt to open MS Excel or any other office
application directly from your web application on your server. This
will completely bog down your application. Instead, Microsoft has
create Office Web Components (OWC) specifically for generating Excel
worksheets and charts on the server which can then be viewed in the
browser as Excel. You can download a reference for using OWC from the
microsoft site at
http://msdn.microsoft.com/office/und...c/default.aspx. As
long as you install MS Office on the server, you will have use of OWC.
I don't use ASP any more, but here's a snippet of code I once used to
generate an Excel worksheet with OWC:
<%
Sub CreateSpreadsheet(objRS)
'Create the spreadsheet
Set ss1=CreateObject("OWC.Spreadsheet")
Set c = ss1.Constants
Set ws=ss1.ActiveSheet
'ws.range("A1").VAlignment=2
' Set the row height and column width
'ws.Columns.ColumnWidth=322
'ws.Rows.RowHeight=25
'Place the values into the cells
i=0
Do until objRS.EOF
i=i+1
With ws
'.cells(i,1).font.size=8
.cells(i,1).value=i
.cells(i,2).value=myvalue4(objRS.fields("CustomerI D"))
.cells(i,3).value=myvalue4(objRS.fields("LastName" ))
.cells(i,4).value=myvalue4(objRS.fields("FirstName "))
.cells(i,5).value=myvalue4(objRS.fields("ClassLeve l"))
.cells(i,6).value=myvalue3(objRS.fields("AvgOfTabl eResult"))
.cells(i,7).value=myvalue3(objRS.fields("AvgOfTheo Value"))
.cells(i,8).value=myvalue3(objRS.fields("CountOfCu stomerID"))
End With
objRS.MoveNext
Loop
'Export the spreadsheet
mypath=server.MapPath("theovalueexport.xls")
ws.export mypath, c.ssExportActionNone
Set c=Nothing
Set ws=Nothing
Set ss1=Nothing
End Sub
%>
OWC is OK but the fastest way to create the report is to generate a
text file and save it as a CSV file, which has already been mentioned.
Once saved you can have the user open it with a link. Even better, I
think that you can stream the output directly to the browser and have
it opened as Excel by setting Response.ContentType =
"application/vnd.ms-excel"
Here's some code to make the same report using this method:
Sub CreateTextFile(objRS)
nr=0
Set fso=Server.CreateObject("Scripting.FileSystemObjec t")
mypath=server.MapPath("theovalueexport.txt")
Set mytextfile=fso.CreateTextFile(mypath,True)
mytextfile.writeline
"#,CustomerID,LastName,FirstName,ClassLevel,Actual Value365,TheoValue365,Voyages365"
Do until objRS.EOF
nr=nr+1
mytext=nr & "," & myvalue4(objRS.fields("CustomerID")) & "," &
myvalue4(objRS.fields("LastName")) & ","
mytext = mytext & myvalue4(objRS.fields("FirstName")) & "," &
myvalue4(objRS.fields("ClassLevel")) & ","
mytext = mytext & myvalue3(objRS.fields("AvgOfTableResult")) & "," &
myvalue3(objRS.fields("AvgOfTheoValue")) & ","
mytext = mytext & myvalue3(objRS.fields("CountOfCustomerID"))
mytextfile.writeline mytext
objRS.MoveNext
Loop
mytextfile.close
Set mytextfile=Nothing
Set fso=Nothing
End Sub
%>
I hope this helps you.
Bill E
Hollywood, FL
ctkevin wrote:
Hey Trevor - thanks for the commentary.... :)
Guess I should've mentioned that I used Dreamweaver to build an
application around this database using ASP and VB. I'm just not
aware of any method for allowing a user to take a pre-built query or report
and downloading it as an Excel file. If this can be done using ASP,
can you direct me to an appropriate resource for reading? I have
several books on ASP I just don't know where to begin looking.
