PDA

Click to See Complete Forum and Search --> : Export to Excel trouble

st3ady
07-05-2006, 05:28 PM
Here is the code I came up with today:

<%
Response.ContentType = "application/vnd.ms-excel"
Response.AddHeader "Content-Disposition","attachment;filename=SOSreport_ExcelExport.xls"

'Connecting to database here
Set cnn = Server.CreateObject("ADODB.Connection")
SName = Server.MapPath("SOSwCL_811.mdb")
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+SName+";"

District = request("District")
Region = request("Region")
MonthBy = request("MonthBy")

Export = "Export"

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>State of Service Report</TITLE>

</HEAD>

<BODY>

<%End IF


'***Begin table generation starts here***

Sql = "SELECT SVC_UN_NO, BR_SVC_UN_NO, MDS_SP_CD, "
IF MonthBy = "ALL" THEN
for i=2 to month(now())
Sql = Sql & " (SUM((ActualSOS) * abs((abs(SGN(ACCTG_MTH - " & i & "))-1))) / SUM(abs((abs(SGN(ACCTG_MTH - " & i & "))-1)))) as " & i & ","
next
ELSE
Sql = Sql & " (SUM((ActualSOS) * abs((abs(SGN(ACCTG_MTH - " & MonthBy & "))-1))) / SUM(abs((abs(SGN(ACCTG_MTH - " & MonthBy & "))-1)))) as CompareMonth,"
END IF

Sql = Sql & " AVG(ActualSOS) AS Fullavg"
Sql = Sql & " FROM 2006_sos_exp "
Sql = Sql & " WHERE acctg_yr = 2006"
IF NOT District = "" THEN 'if a district was passed in add criteria to only show that district
Sql = Sql & " and SVC_UN_NO = " & District
END IF

Sql = Sql & " GROUP BY SVC_UN_NO, BR_SVC_UN_NO, MDS_SP_CD ORDER BY SVC_UN_NO, BR_SVC_UN_NO, MDS_SP_CD"
Set SOSreport_rs = cnn.Execute(Sql)
IF SOSreport_rs.eof then StatusMessage = "No data found for selected criteria."

Else


%>
<P> <B>SOS Report For <%
If Region = "" then
response.write ("ALL Regions & Districts")
ElseIf Region <> "" and District="" then
response.write ("Region " & Region & ", ALL Districts")
ElseIf Region <> "" and District <> "" then
response.write ("Region " & Region & ", District " & District)
End If
%> for Month #<%=MonthBy%></B></td>
</tr> <!-- display status message if there is one. -->

<TABLE WIDTH="85%" CELLSPACING="1" CELLPADDING="1" BORDER="1">
<TR>
<TD BGCOLOR="#000000"><FONT FACE="verdana" COLOR="#FFFFFF" SIZE="2"><B>Region</B></FONT></TD>
<TD BGCOLOR="#000000"><FONT FACE="verdana" COLOR="#FFFFFF" SIZE="2"><B>District</B></FONT></TD>
<TD BGCOLOR="#000000"><FONT FACE="verdana" COLOR="#FFFFFF" SIZE="2"><B>
CallLoad</B></FONT></TD>
<TD BGCOLOR="#000000"><FONT FACE="verdana" COLOR="#FFFFFF" SIZE="2"><B>Category</B></FONT></TD>
<%IF MonthBy = "ALL" THEN
for i=2 to month(now())%>
<TD BGCOLOR="#000000"><FONT FACE="verdana" COLOR="#FFFFFF" SIZE="2"><B><%=monthname(i)%></B></FONT></TD>
<%next
ELSE%>
<TD BGCOLOR="#000000"><FONT FACE="verdana" COLOR="#FFFFFF" SIZE="2"><B><%=monthname(MonthBy)%> Average</B></FONT></TD>
<%END IF%>
<TD BGCOLOR="#000000"><FONT FACE="verdana" COLOR="#FFFFFF" SIZE="2"><B>Feb To <%=monthname(month(now()))%> Average</B></FONT></TD>
</TR>
<% While not SOSreport_rs.EOF
%>
<tr>
<TD><%=District%></TD>
<TD><%=SOSreport_rs.Fields("BR_SVC_UN_NO")%></TD>
<TD><%=SOSreport_rs.Fields("MDS_SP_CD")%></TD>
<%IF MonthBy = "ALL" THEN
for i=2 to month(now())%>
<TD><%=FormatNumber(SOSreport_rs.Fields(cstr(i)),2)%></TD>
<%next
ELSE%>
<TD><%=FormatNumber(SOSreport_rs.Fields("CompareMonth"),2)%></TD>
<%END IF%>
<TD><%=FormatNumber(SOSreport_rs.Fields("Fullavg"),2)%></TD>
</TR>
<%
SOSreport_rs.MoveNext
wend
'cleanup
SOSreport_rs.close
SET Cnn = Nothing
Set SOSreport_rs = Nothing
%>
</TABLE>
<%If Export <> "Export" Then%>

</BODY>

</HTML>
<%End IF%>

But I get the following error:
Error Type:
Microsoft VBScript compilation (0x800A03EA)
Syntax error
/SOS/SOSreport_ExcelExport.asp, line 55


Any idea as to why? or how to fix this? Im confused.
Terrorke
07-06-2006, 06:51 AM
First thing try this :

IF SOSreport_rs.eof then
StatusMessage = "No data found for selected criteria."
instead of

IF SOSreport_rs.eof then StatusMessage = "No data found for selected criteria."


The second thing is you have mixed up your If statements


Sql = Sql & " GROUP BY SVC_UN_NO, BR_SVC_UN_NO, MDS_SP_CD ORDER BY SVC_UN_NO, BR_SVC_UN_NO, MDS_SP_CD"
Set SOSreport_rs = cnn.Execute(Sql)
IF SOSreport_rs.eof then
StatusMessage = "No data found for selected criteria."
Else
...

The end if statement is missing for this IF structure.
st3ady
07-06-2006, 10:11 AM
wow, thank you very much, you are awesome! :) works perfectly.
I work here all alone, and due to the webfilter, I am unable to use any irc chat clients, and it can be frustrating when you are the only one looking over your work. I am glad that there are friendly message boards with helpful people like you :) keep up the good work
Terrorke
07-06-2006, 10:24 AM
Glad I could help :)