How do I improve speed when reading from Excel

[mellenburg]

I am using the following script to read data from an Excel workbook. The data I'm reading contain about 50 rows and 20 columns. I'm only writing out select columns.

The script works, but it really slow and often times out. Is there something that can be done to improve the speed?

Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open(server.mappath("\") & "\[file].xls")
%>
<table cellpadding=5 cellspacing=0>
<%
intColumns = objExcel.ActiveWorkbook.Worksheets(1).UsedRange.Columns.Count
intRows = objExcel.ActiveWorkbook.Worksheets(1).UsedRange.Rows.Count
for intRow = 1 to intRows
  %>
  <tr>
  <%
  for intColumn = 2 to intColumns
    if intColumn = 2 or instr(strVars,objExcel.ActiveWorkbook.Worksheets(1).Cells(1,intColumn).Value)>0 then
      %>
      <td>
        <%=objExcel.ActiveWorkbook.Worksheets(1).Cells(intRow,intColumn).Value%>
      </td>
      <%
    end if
  next
  %>
  </tr>
  <%
next

 

[jet042]

Have you checked that the values of intColumns and intRows are what you expect them to be? If there is an error there, then it could run for a very long time indeed to get through all 65K rows in an Excel 2003 and older (over 3 million in 2007, IIRC).

 

[NWBeaver]

This appears to be part of an ASP application, if you haven't already done so you might want to post this in the ASP or ASP.net forum.

From what I know about ASP, server scripts are surrounded by the delimiters <% and %>. Perhaps what you have posted is not your complete script, but the delimiters don't look quite right, they have to be paired like so.

<%
response.write("Hello World!")
%>

Also, doesn't using HTML tags in the middle of the script actually make this appear to be many scripts, as the server would see each <% %> pair as a new script?