DB To Excel Problem

Results 1 to 2 of 2

Thread: DB To Excel Problem

  1. #1
    Join Date
    Dec 1969

    Default DB To Excel Problem

    I would like to export the entire table "Customers" to an excel spreadsheet. I can only retrieve 70% of the whole table and then the page is timeout. However, if I output it to a HTML page, I can get all records. How can I solve this problem ?<BR>Here&#039;s my code.<BR>&#060;%<BR> Set oConn = Server.CreateObject("ADODB.Connection")<BR> Set oRS = Server.CreateObject("ADODB.Recordset")<BR><BR> oRS.Open "select * from Customers", Session("ConnectionString")<BR> <BR> If Not oRS.EOF Then<BR><BR> Dim xlWorkSheet, xlApplication<BR> <BR> Set xlApplication = CreateObject("Excel.Application")<BR> xlApplication.Workbooks.Add<BR> Set xlWorksheet = xlApplication.Worksheets(1)<BR> row=2<BR> For i = 0 To oRS.Fields.Count - 1<BR> xlWorksheet.Cells(1, i + 1).Value = ors(i).name<BR> Next<BR> While Not oRS.EOF<BR> For i = 0 To oRS.Fields.Count - 1<BR> xlWorksheet.Cells(row, i + 1).Value = oRS(i)<BR> Next<BR> row = row + 1<BR> oRS.MoveNext<BR> Wend<BR> xlWorksheet.Cells(1, 1).EntireRow.HorizontalAlignment = 3<BR> xlWorksheet.Cells(1, 1).EntireRow.Font.Bold = True<BR> xlWorksheet.Cells.EntireColumn.AutoFit<BR><BR> xlWorksheet.SaveAs Server.MapPath(".") & "customers.xls"<BR> xlApplication.Quit<BR> Set xlWorksheet = Nothing<BR> Set xlApplication = Nothing<BR> oRS.Close<BR>%&#062;

  2. #2
    Join Date
    Dec 1969

    Default RE: DB To Excel Problem

    use either <BR><BR>Server.Scripttimeout = [seconds until script times out]<BR><BR>or <BR><BR>oConn.Commandtimeout = [seconds until command times out].<BR><BR>try both and see which one gives you the desired result.<BR><BR>(put the oConn.Commandtimeout line before you open the connection, it won&#039;t work on an open connection)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts