st3ady
06-05-2006, 11:37 AM
Hey there, I'm having a difficult time figuring out why this export to excel won't work. When I click the Export to excel link on the previous page, it gives me the option of opening or saving but then when I click ok it gives me an error saying internet explorer cannot download. Any help is greatly appreciated, thanks!
<%
Server.ScriptTimeout = 1000
Response.ContentType = "application/vnd.ms-excel"
Response.AddHeader "Content-Disposition","attachment;filename=CustInquiryScreenScrape.xls"
export = "Export"
start_date = request.querystring("start_date")
end_date = request.querystring("end_date")
start_time = request.querystring("start_time")
end_time = request.querystring("end_time")
If start_time = "" Then
start_time = "6:00"
End If
If end_time = "" Then
end_time = "20:00"
End If
If start_date = "" or IsNull(start_date) Then
start_date = Date
End If
If end_date = "" or IsNull(end_date) Then
end_date = Date
End If
call_type = Request.QueryString("call_type")
If Export <> "Export" Then
%>
<HTML>
<HEAD>
<META HTTP-EQUIV="Content-Language" CONTENT="en-us">
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=windows-1252">
<META NAME="GENERATOR" CONTENT="Microsoft FrontPage 5.0">
<META NAME="ProgId" CONTENT="FrontPage.Editor.Document">
<TITLE>Customer Inquiry Summary Report</TITLE>
</HEAD>
<BODY>
<%End IF %>
<table width=100% cellspacing=1 cellpadding=1 border=1>
<tr>
<td bgcolor=#000000><font color=#FFFFFF>Date</font></td>
<td bgcolor=#000000><font color=#FFFFFF>Time</font></td>
<td bgcolor=#000000><font color=#FFFFFF>Associate ID</font></td>
<td bgcolor=#000000><font color=#FFFFFF>Unit</font></td>
<td bgcolor=#000000><font color=#FFFFFF>Cause Code</font></td>
<td bgcolor=#000000><font color=#FFFFFF>SO#</font></td>
<td bgcolor=#000000><font color=#FFFFFF>Closing Msg</font></td>
</tr>
<%
dim SQLA
Set cnn = Server.CreateObject("ADODB.Connection")
SName = Server.MapPath("ArcCustInquiry.mdb")
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+SName+";"
Set MyRSA = Server.CreateObject("ADODB.RecordSet")
If CStr(Request.QueryString("call_type")) = "A_Call" Then
SQLA="SELECT Distinct SONum FROM Cust_Inquiry_Closed where Cust_Inquiry_Closed.C_Date Between #" & start_date & "# And #" & end_date &"# and Cust_Inquiry_Closed.Time Between #"& start_time & "# And #" & end_time &"# "
End If
If CStr(Request.QueryString("call_type")) = "O_Call" Then
SQLA = "SELECT distinct( Cust_Inquiry_Closed.SONum) as SONum FROM Cust_Inquiry_Closed,Archive WHERE Cust_Inquiry_Closed.SONum=Archive.Svc_ord and Archive.TickDateTime is NOT null and Cust_Inquiry_Closed.C_Date Between #" & start_date & "# And #" & end_date &"# and Cust_Inquiry_Closed.Time Between #"& start_time & "# And #" & end_time & "# "
End if
If CStr(Request.QueryString("call_type")) = "C_Call" Then
SQLA = "SELECT distinct( Cust_Inquiry_Closed.SONum) as SONum FROM Cust_Inquiry_Closed,Archive WHERE Cust_Inquiry_Closed.SONum=Archive.Svc_ord and Archive.TickDateTime is null and Cust_Inquiry_Closed.C_Date Between #" & start_date & "# And #" & end_date &"# and Cust_Inquiry_Closed.Time Between #"& start_time & "# And #" & end_time & "# "
End if
MyRSA.Open SQLA, cnn, 1, 3
while not MyRSA.EOF
Set Cust_Inquiry_Closed_rs = cnn.Execute("Select * from Cust_Inquiry_Closed where SONum ="&MyRSA.Fields("SONum")&" and Cust_Inquiry_Closed.C_Date Between #" & start_date & "# And #" & end_date &"# and Cust_Inquiry_Closed.Time Between #"& start_time & "# And #" & end_time &"#")
if Cust_Inquiry_Closed_rs.EOF then
Cust_Inquiry_Closed_rs.close
MyRSA.MoveNext
else
DateClosed = Cust_Inquiry_Closed_rs.Fields("C_Date")
TimeClosed = Cust_Inquiry_Closed_rs.Fields("Time")
AssoID = Cust_Inquiry_Closed_rs.Fields("AssoID")
Unit = Cust_Inquiry_Closed_rs.Fields("Unit")
CauseCode = Cust_Inquiry_Closed_rs.Fields("C_CauseCode")
SONum = Cust_Inquiry_Closed_rs.Fields("SONum")
Msg = Cust_Inquiry_Closed_rs.Fields("Msg")
Cust_Inquiry_Closed_rs.close
%>
<tr>
<td><FONT FACE="verdana" SIZE="2"><%=DateClosed%></FONT> </td>
<td><FONT FACE="verdana" SIZE="2"><%=TimeClosed%></FONT> </td>
<td><FONT FACE="verdana" SIZE="2"><%=AssoID%></FONT> </td>
<td><FONT FACE="verdana" SIZE="2"><%=Unit%></FONT> </td>
<td><FONT FACE="verdana" SIZE="2"><%=CauseCode%></FONT> </td>
<td><FONT FACE="verdana" SIZE="2"><%=SONum%></FONT> </td>
<td><FONT FACE="verdana" SIZE="2"><%=Msg%></FONT> </td>
</tr>
<%MyRSA.MoveNext%>
<%end if%>
<%wend%>
</table>
<%
MyRSA.Close
cnn.Close
cnn2.Close
%> <%If Export <> "Export" Then%>
</body>
</html>
<%end if%>
<%
Server.ScriptTimeout = 1000
Response.ContentType = "application/vnd.ms-excel"
Response.AddHeader "Content-Disposition","attachment;filename=CustInquiryScreenScrape.xls"
export = "Export"
start_date = request.querystring("start_date")
end_date = request.querystring("end_date")
start_time = request.querystring("start_time")
end_time = request.querystring("end_time")
If start_time = "" Then
start_time = "6:00"
End If
If end_time = "" Then
end_time = "20:00"
End If
If start_date = "" or IsNull(start_date) Then
start_date = Date
End If
If end_date = "" or IsNull(end_date) Then
end_date = Date
End If
call_type = Request.QueryString("call_type")
If Export <> "Export" Then
%>
<HTML>
<HEAD>
<META HTTP-EQUIV="Content-Language" CONTENT="en-us">
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=windows-1252">
<META NAME="GENERATOR" CONTENT="Microsoft FrontPage 5.0">
<META NAME="ProgId" CONTENT="FrontPage.Editor.Document">
<TITLE>Customer Inquiry Summary Report</TITLE>
</HEAD>
<BODY>
<%End IF %>
<table width=100% cellspacing=1 cellpadding=1 border=1>
<tr>
<td bgcolor=#000000><font color=#FFFFFF>Date</font></td>
<td bgcolor=#000000><font color=#FFFFFF>Time</font></td>
<td bgcolor=#000000><font color=#FFFFFF>Associate ID</font></td>
<td bgcolor=#000000><font color=#FFFFFF>Unit</font></td>
<td bgcolor=#000000><font color=#FFFFFF>Cause Code</font></td>
<td bgcolor=#000000><font color=#FFFFFF>SO#</font></td>
<td bgcolor=#000000><font color=#FFFFFF>Closing Msg</font></td>
</tr>
<%
dim SQLA
Set cnn = Server.CreateObject("ADODB.Connection")
SName = Server.MapPath("ArcCustInquiry.mdb")
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+SName+";"
Set MyRSA = Server.CreateObject("ADODB.RecordSet")
If CStr(Request.QueryString("call_type")) = "A_Call" Then
SQLA="SELECT Distinct SONum FROM Cust_Inquiry_Closed where Cust_Inquiry_Closed.C_Date Between #" & start_date & "# And #" & end_date &"# and Cust_Inquiry_Closed.Time Between #"& start_time & "# And #" & end_time &"# "
End If
If CStr(Request.QueryString("call_type")) = "O_Call" Then
SQLA = "SELECT distinct( Cust_Inquiry_Closed.SONum) as SONum FROM Cust_Inquiry_Closed,Archive WHERE Cust_Inquiry_Closed.SONum=Archive.Svc_ord and Archive.TickDateTime is NOT null and Cust_Inquiry_Closed.C_Date Between #" & start_date & "# And #" & end_date &"# and Cust_Inquiry_Closed.Time Between #"& start_time & "# And #" & end_time & "# "
End if
If CStr(Request.QueryString("call_type")) = "C_Call" Then
SQLA = "SELECT distinct( Cust_Inquiry_Closed.SONum) as SONum FROM Cust_Inquiry_Closed,Archive WHERE Cust_Inquiry_Closed.SONum=Archive.Svc_ord and Archive.TickDateTime is null and Cust_Inquiry_Closed.C_Date Between #" & start_date & "# And #" & end_date &"# and Cust_Inquiry_Closed.Time Between #"& start_time & "# And #" & end_time & "# "
End if
MyRSA.Open SQLA, cnn, 1, 3
while not MyRSA.EOF
Set Cust_Inquiry_Closed_rs = cnn.Execute("Select * from Cust_Inquiry_Closed where SONum ="&MyRSA.Fields("SONum")&" and Cust_Inquiry_Closed.C_Date Between #" & start_date & "# And #" & end_date &"# and Cust_Inquiry_Closed.Time Between #"& start_time & "# And #" & end_time &"#")
if Cust_Inquiry_Closed_rs.EOF then
Cust_Inquiry_Closed_rs.close
MyRSA.MoveNext
else
DateClosed = Cust_Inquiry_Closed_rs.Fields("C_Date")
TimeClosed = Cust_Inquiry_Closed_rs.Fields("Time")
AssoID = Cust_Inquiry_Closed_rs.Fields("AssoID")
Unit = Cust_Inquiry_Closed_rs.Fields("Unit")
CauseCode = Cust_Inquiry_Closed_rs.Fields("C_CauseCode")
SONum = Cust_Inquiry_Closed_rs.Fields("SONum")
Msg = Cust_Inquiry_Closed_rs.Fields("Msg")
Cust_Inquiry_Closed_rs.close
%>
<tr>
<td><FONT FACE="verdana" SIZE="2"><%=DateClosed%></FONT> </td>
<td><FONT FACE="verdana" SIZE="2"><%=TimeClosed%></FONT> </td>
<td><FONT FACE="verdana" SIZE="2"><%=AssoID%></FONT> </td>
<td><FONT FACE="verdana" SIZE="2"><%=Unit%></FONT> </td>
<td><FONT FACE="verdana" SIZE="2"><%=CauseCode%></FONT> </td>
<td><FONT FACE="verdana" SIZE="2"><%=SONum%></FONT> </td>
<td><FONT FACE="verdana" SIZE="2"><%=Msg%></FONT> </td>
</tr>
<%MyRSA.MoveNext%>
<%end if%>
<%wend%>
</table>
<%
MyRSA.Close
cnn.Close
cnn2.Close
%> <%If Export <> "Export" Then%>
</body>
</html>
<%end if%>