Paula,
Below is code to dynamically create an XLS File. The file will be displayed on the end user's browser and they can save it to their system. There are even a few tips for formatting the data. Note this sample has predefined queries. In other versions of this code I pass the SQL statements via HTTPPost.
<%@ Language=VBScript %>
<% option explicit %>
<%
'Author: Dale A. Mitchell
'Company: MindCrafter Web Designs
'Created: 2/03/2001
'Modified: 9/24/2002
'Description: The following code allows for mulitple web data forms to generate XLS files for local access.
' The code will generate a simple Excel worksheet or can be used as the base for more complex output bu including Excel
' functions within individual data cells or by modifying the SQL statement accordingly.
'
'Associated Files: xls.gif; frmCreateXLS.htm
dim sSQL
' Check to see if the URL is correct
if isNumeric(Request.QueryString("FrmID")) then
' Set SQL Query Based ong the requesting web form id
select case CInt(Request.QueryString("FrmID"))
case 1
sSQL="SELECT FNAME, LNAME, TITLE, EMAIL, HomePhone, WorkPhone, Comments " & _
"FROM Users " & _
"ORDER BY LNAME ASC"
case 2
sSQL="SELECT Contact, Business, Address, City, State, Country, TITLE, EMAIL, HomePhone, WorkPhone, Comments " & _
"FROM Bus_Contacts " & _
"ORDER BY LNAME ASC"
case 3
sSQL=""
end select
GenerateXLS
else
DisplayErrMessage 0
end if
sub GenerateXLS()
dim nField
dim oConn,oRs
set oRs = server.CreateObject("ADODB.RECORDSET")
set oConn = server.createobject("ADODB.CONNECTION")
' Change to the correct connection string for your website
oConn.ConnectionString = Application("ReadOnly")
' If you get errors writing the XLS file set the buffer to false.
' This will allow the XLS file to open and display the ADO error.
Response.Buffer = TRUE
' Set the option to save the recordset as an XLS fiel.
' Note: The web user account cannot have write permissions on the directory where the file resides.
' If the web user account does have write permision, static html can be generated and passed to the client's browser.
' this is a known bug in IE and Excel.
Response.ContentType = "application/vnd.ms-excel"
oConn.open
oRs.Open sSQL,oConn,3,3
if oRs.EOF then ' Clean up ADO Objects and display error popup
oRs.Close
set oRs = nothing
oConn.Close
set oConn=nothing
DisplayErrMessage 1
else
' Create XLS File
' This code will dynamically create a table based on the number of fields returned by the SQL Query
Response.Write "<html><body>" & vbNewLine
Response.Write "<table border=" & chr(34) & "1" & chr(34) & " cellpadding=" & chr(34) & "0" & chr(34) & " cellspacing=" & chr(34) & "0" & chr(34) & "><tr>" & vbNewLine
' Create table "Header" cells
for nField = 0 to (oRs.Fields.count-1)
Response.Write "<td>" & oRs.Fields(nField).Name & "</td>"
next
Response.Write "</tr>" & vbNewLine
' Create data cells
' Formula example: <TD FORMULA="=Sum(A2:B2)">
' Number Formatting: <td STYLE="vnd.ms-excel.numberformat:$#.0000[semicolon]($#.0000)">
do until oRs.EOF
Response.Write "<tr>"
for nField = 0 to (oRs.Fields.count-1)
Response.Write "<td>" & oRs.Fields(nField).Value & "</td>"
next
Response.Write "</tr>" & vbNewLine
oRs.MoveNext
loop
Response.Write "</tr></table>" & vbNewLine
Response.Write "</body></html>" & vbNewLine
' Clean up ADO Objects
oRs.Close
set oRs = nothing
oConn.Close
set oConn=nothing
end if
end sub
sub DisplayErrMessage(nErrLvl)
dim sMsgText
select Case nErrLvl
case 0
sMsgText="Invalid Request!"
case 1
sMsgText="No data to display"
end select
' Note: The error message prompt does not have to be a javascript popup.
%>
<script language="javascript">
alert('<%= sMsgText %>')
window.close()
</script>
<%
Response.End
end sub
%>
Hope this helps. :cool: