connecting to excel file

I have some reports that are generated as excel files off of an AS/400 system and then are uploaded to the webserver. From there the information is displayed as html in a table via asp. One of these files in particular is only 400 or so rows but for some reason even though i am using Do Until objRs.EOF / Loop it is going way past that and giving me another 2200 empty rows. Needless to say this is really slowing the display of this down. I Tried looking for 2 consecutive empty rows and then using exit Do but that didnt work either Any one else have an idea? here is what the code looks like now.


<%
Dim objConn, objRs, SQL, connString, strSearch, strState, i
strSearch = Request.Form("State")
connString = Server.MapPath("AgentList.xls") & ";"
'Response.Write connString & "<br>"
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open ("DRIVER={Microsoft Excel Driver (*.xls)}; DBQ=" & connString)
SQL = "SELECT DISTINCT Mid([#,state,city],6,2) AS state FROM [AgentList$]"
Set objRs = objConn.Execute(SQL)
%>
<form method="post" action="Agent_lists.asp" name="thisForm">
<table width="99%">
<tr>
<td>
To Narrow the Results, Select the State from the List
</td>
<td title="If you don't want to see all results, then select the state to see only those agents in that state">
<select name="state" onChange="NarrowResults();" class="select">
<option value="">Select</option>
<%Do Until objRs.EOF
strState = objRs("state")
If strState <> "" Then
%>
<option value="<%=strState%>" <%If strState = strSearch Then Response.Write "selected"%>><%=strState%></option>
<%
End If
strState = ""
objRs.MoveNext
Loop
%>
</select>
</td>
</tr>
</table>
</form>
<%
objRs.close
Set objRs = Nothing
If Len(strSearch) = 0 Then
SQL = "SELECT * FROM [AgentList$]"
Else
SQL = "SELECT * FROM [AgentList$] WHERE Mid([#,state,city],6,2) ='" & strSearch & "'"
End If
'Response.Write SQL
Set objRS = objConn.Execute(SQL)
%>
<center>
* Designates Agents with Terminal Pay
<hr width="90%">
</center>
<TABLE border=0 CellPadding=3 width="99%">
<TR align=left>
<TH class="hdr" width="25%"># State City</TH>
<TH class="hdr" width="25%">Contact</TH>
<TH class="hdr" width="20%">Equip</TH>
<TH class="hdr" width="30%">Phones</TH>
</TR>
<%
objRs.MoveFirst
Do While Not objRs.eof %>
<TR align=left>
<TD class="tblBody" width="25%"><% = objRs("#,State,City") %></TD>
<TD class="tblBody" width="25%"><% = objRs("Contact")%></TD>
<TD class="tblBody" width="20%"><% = objRs("equip") %></TD>
<TD class="tblBody" width="30%"><% = objRs("phones")%></TD>
</TR>
<%
objRs.MoveNext
If Len(objRs("#,State,City")) = 0 Then objRs.MoveNext
If Len(objRs("#,State,City")) = 0 Then Exit Do
Loop
%>

It's likely the spreadsheet has the empty rows in itself and you're getting them in the returned recordset object. Try changing the SQL statements that pull the data to limit the recordset to the records with actual data.

SELECT * FROM [AgentList$] WHERE Len([#,state,city]) > 0 -- may work.

That did the trick. Thank you. it really sped up the loading.