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 CreateSpreadshe et(objRS)
'Create the spreadsheet
Set ss1=CreateObjec t("OWC.Spreadsh eet")
Set c = ss1.Constants
Set ws=ss1.ActiveSh eet
'ws.range("A1") .VAlignment=2
' Set the row height and column width
'ws.Columns.Col umnWidth=322
'ws.Rows.RowHei ght=25
'Place the values into the cells
i=0
Do until objRS.EOF
i=i+1
With ws
'.cells(i,1).fo nt.size=8
.cells(i,1).val ue=i
.cells(i,2).val ue=myvalue4(obj RS.fields("Cust omerID"))
.cells(i,3).val ue=myvalue4(obj RS.fields("Last Name"))
.cells(i,4).val ue=myvalue4(obj RS.fields("Firs tName"))
.cells(i,5).val ue=myvalue4(obj RS.fields("Clas sLevel"))
.cells(i,6).val ue=myvalue3(obj RS.fields("AvgO fTableResult"))
.cells(i,7).val ue=myvalue3(obj RS.fields("AvgO fTheoValue"))
.cells(i,8).val ue=myvalue3(obj RS.fields("Coun tOfCustomerID") )
End With
objRS.MoveNext
Loop
'Export the spreadsheet
mypath=server.M apPath("theoval ueexport.xls")
ws.export mypath, c.ssExportActio nNone
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.Conten tType =
"applicatio n/vnd.ms-excel"
Here's some code to make the same report using this method:
Sub CreateTextFile( objRS)
nr=0
Set fso=Server.Crea teObject("Scrip ting.FileSystem Object")
mypath=server.M apPath("theoval ueexport.txt")
Set mytextfile=fso. CreateTextFile( mypath,True)
mytextfile.writ eline
"#,CustomerID,L astName,FirstNa me,ClassLevel,A ctualValue365,T heoValue365,Voy ages365"
Do until objRS.EOF
nr=nr+1
mytext=nr & "," & myvalue4(objRS. fields("Custome rID")) & "," &
myvalue4(objRS. fields("LastNam e")) & ","
mytext = mytext & myvalue4(objRS. fields("FirstNa me")) & "," &
myvalue4(objRS. fields("ClassLe vel")) & ","
mytext = mytext & myvalue3(objRS. fields("AvgOfTa bleResult")) & "," &
myvalue3(objRS. fields("AvgOfTh eoValue")) & ","
mytext = mytext & myvalue3(objRS. fields("CountOf CustomerID"))
mytextfile.writ eline mytext
objRS.MoveNext
Loop
mytextfile.clos e
Set mytextfile=Noth ing
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.
