Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

How do I improve speed when reading from Excel

Status
Not open for further replies.

mellenburg

Programmer
Aug 27, 2001
77
US
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?

Code:
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
 
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).
 
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?

 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top