Exporting to Excel

Results 1 to 3 of 3

Thread: Exporting to Excel

  1. #1
    Join Date
    Dec 1969

    Default Exporting to Excel

    I wanted to ask if someone could offer a better way of doing this. My code works, but it&#039;s very slow when the recordset gets too large. <BR><BR>My users are presented with a list of 75 checkboxes which they check to indicate which fields they want to export to Excel. <BR><BR>The code basically works like this:<BR><BR>&#060;%<BR>&#039;Collect the checkbox values from the previous page containing all of the checkboxes<BR><BR>&#039;Ticker<BR>strTicker = Request.Form("C1")<BR><BR>&#039;CompanyName<BR>str CompanyName = Request.Form("C2")<BR><BR>&#039;Industry<BR>strInd ustry = Request.Form("C3")<BR><BR>&#039;etc..<BR><BR>&#039 ;Create our recordset - this recordset will be used to loop through our returned records<BR> Set rs = con.Execute(strSqlText)<BR><BR> <BR> fileExcel = systime=now()<BR> fileExcel = fileExcel & cstr(year(systime)) & cstr(month(systime)) & cstr(day(systime))<BR> <BR>&#039;Adds system hour, minute and second as file name<BR> fileExcel = fileExcel & cstr(hour(systime))<BR> fileExcel = fileExcel & cstr(minute(systime)) <BR> fileExcel = fileExcel & cstr(second(systime))<BR><BR>&#039;Where you want the file saved<BR> filePath= "d:ExcelReports\"<BR> filename=filePath & "\" & fileExcel & strFileType<BR><BR> Set fs = Server.CreateObject("Scripting.FileSystemObject")< BR> Set MyFile = fs.CreateTextFile(filename, True)<BR><BR>&#039;Initialize the variable for storing the fieldnames<BR> strLine="" <BR><BR><BR>&#039;**** These are the column headings for the excel report ****<BR><BR><BR>If strTicker &#060;&#062; "" Then<BR> strLine = strLine & "Ticker" & chr(9)<BR> blnTicker = True<BR>End If<BR><BR>If strCompanyName &#060;&#062; "" Then<BR> strLine = strLine & "Company" & chr(9)<BR> blnCompanyName = True<BR>End If<BR><BR>If strIndustry &#060;&#062; "" Then<BR> strLine = strLine & "Industry" & chr(9)<BR> blnIndustry = True<BR>End If <BR> <BR>&#039;etc... <BR><BR><BR>&#039;***** Loop through our records for the excel worksheet *****<BR><BR>If blnTicker = True Then<BR> strLine = strLine & rs("Ticker") & chr(9)<BR>End If<BR><BR>If blnCompanyName = True Then<BR> strLine = strLine & rs("CompanyName") & chr(9)<BR>End If<BR><BR>If blnIndustry = True Then<BR> strLine = strLine & rs("Industry") & chr(9)<BR>End If<BR><BR>&#039;etc...<BR><BR><BR> <BR>strLine = strLine & vbCrLf<BR>rs.MoveNext<BR>Loop <BR> <BR>MyFile.writeline strLine<BR> <BR>&#039;Clean up<BR>MyFile.Close<BR>Set MyFile=Nothing<BR>Set fs=Nothing <BR><BR>%&#062;<BR><BR><BR>

  2. #2
    Join Date
    Dec 1969

    Default modify your query

    it looks as if you are goign to have 50 if..then statements. Modify your query to only include the columns they are asking for and export everything the recordset has in it.<BR><BR>Should clean up all of that extra logic that is chewing up compute cycles.<BR><BR>So instead of doing 50 if...then statements for all 10,000 records, you may do it one time, and then just export all of the rows.<BR><BR>Build you SQL query dynamically and roll with it. should speed up the process quite a bit!<BR>

  3. #3
    Join Date
    Dec 1969

    Default RE: Exporting to Excel

    Building up strings like this can get slow when the strings get large. You&#039;ll probably get quite an improvement by using shorter strings - rather that sticking all the records into a string and writing the whole thing to the file in one go try writing to the file after each record.<BR><BR>I don&#039;t know what the performance of the FSO is like but you may get better performance if you write to the file after every x rows instead. Try a few different values and see what gives the best results.<BR><BR>Gav

Posting Permissions

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