VB.Net app creates report, then downloading to Excel it takes fore

[Tom]

We have: Excel 2003 11.6355.6360 SP1
Studio 2005 8.0.5, .NET Framework 2.0.50727

We have an application in vb.net. It creates a report and then we give them
the option of downloading it to an Excel spreadsheet.
It works fine for a small amount of rows, downloads it within a couple
seconds. They then go out to a certain file share and move/copy their
spreadsheet.

The problem comes when the number of rows exceeds a certain amount, it's
usually at about 500 - 700 records or so, it fluctuates. (They do have the
chance to select how many columns are included in the report.) Any more than
this number, and the download to the spreadsheet runs on and on and on. I've
usually killed it after 30-60 minutes. That's too long, something is wrong
somewhere, I don't know where.

Is there a setting, more code, something I can do to make this download to
Excel take place in a reasonable time.


This is the code in the DevExtract.aspx.vb file.


Private Sub CreateFile()
'REQUIRES: Imports System.IO and Imports DNRLib05.MSOffice2003
Dim oExcel As New DNRLib05.MSOffice2003.clsExcelXML
Dim sServerTempDir As String =
System.Configuration.ConfigurationManager.AppSettings("ServerTempDirectory")
Dim sLocalTempDir As String =
System.Configuration.ConfigurationManager.AppSettings("LocalTempDirectory")
Dim bServerDirExists As Boolean
Dim sServerFileName As String
Dim sFileName As String
bServerDirExists = Directory.Exists(sServerTempDir)
If bServerDirExists Then
sServerFileName = sServerTempDir & "DeviceExtract" & sUser &
".xls"
Else
sServerFileName = sLocalTempDir & "DeviceExtract" & sUser & ".xls"
End If
sFileName = "DeviceExtract" & ".xls"
oExcel.NewSpreadsheet()
...
oExcel.DataSource = ViewState("ReportSQL")
oExcel.DataSourceStartRow = 1
oExcel.DataBind(oDB)
oExcel.Save(sServerFileName)
Response.Redirect("Download.aspx?server_file_name=" &
sServerFileName & "&file_name=" & sFileName)
End Sub



Private Sub CreateReportFile()
Dim oExcel As New DNRLib05.MSOffice2003.clsExcelXML
Dim sServerFileName As String
Dim oDB2 As clsDBMS
Try
sServerFileName = Session("sServerFileName")
oExcel = Session("oExcel")
Session("sServerFileName") = Nothing
Session("oExcel") = Nothing

oExcel.DataSource = ViewState("ReportSQL")
oExcel.DataSourceStartRow = 1
oDB2 = New clsDBMS(True)
oExcel.DataBind(oDB2)
oExcel.Save(sServerFileName)
Session("FileCreated") = "Done"
Catch ex As Exception
lblError.Text = "Error: " & ex.Message
End Try
End Sub

 

[Kenneth Hobson]

I would suggest using VBA button to add code tags for posting code vba
or vb code.

You might try setting the calculation mode to manual and turn off
screen updating. I posted some speedup routines in this link. 'VBA
Express : Excel - Speed Up Code'
(http://vbaexpress.com/kb/getarticle.php?kb_id=1035)