Dowload large files to Excel

[MaddogC]

I am trying export a large amount of data to excel from a recordset which fails presumably because of a timeout. How do I get the size of my recordset so that I can break it down using Chunks? My code thus far is

Do until RSRef.EOF
For Each F In RSREf.Fields
if F.Type = adDouble or F.Type = adInteger or F.Type = adNumeric or F.Type = adSingle then
if isnull(F.Value) then
Main = Main & ",""" & strSpace & """"
Else
Main = Main & "," & replace(F.Value,",","")
End IF
Else

if isnull(F.Value) then
Main = Main & ",""" & strSpace & """"
Else
Main = Main & ",""" & replace(F.Value,",","") & """"
End If
End if
Next
Main = Mid(Main,2) & vbCrLf
Response.ContentType = "text/plain"
Response.Write Main
Main=""
RSRef.Movenext
Loop

 

[ggriffit]

why are you setting the CONTENT-TYPE inside the loop ? this woul dbe better done outside it, why not have a look at

http://www.greggriffiths.org/webdev/both/excel/

for some other ways of doing this.


Greg Griffiths
Livelink Certified Developer & ECM Global Star Champion 2005

http://www.greggriffiths.org/livelink/

 

[Sheco]

Excel is limitted to 65K rows.

 

[MaddogC]

Cheers I'll have a look, yes the content type shouldn't be in the loop.

I'm exporting about 30,000 rows so the Excel limit isn't a problem

 

[MaddogC]

I've had a loot at your site. However is there anyway to download the file using a method similar to that above and without the need to save it to the server.

 

[ggriffit]

can you not use the solution in

http://www.greggriffiths.org/webdev/both/excel/simple.html

?


Greg Griffiths
Livelink Certified Developer & ECM Global Star Champion 2005

http://www.greggriffiths.org/livelink/

 

[Norman55]

i had the same problem not so long ago and some kind sole pointed me in the right direction
Have a read through this it worked a treat !!

http://www.tek-tips.com/viewthread.cfm?qid=1231972

Hope it works for you

 

[Norman55]

by the way its the Sheco postings you want to be looking at

 

[MaddogC]

Thanks, Finally got back to this. Worked a treat.

 

[Norman55]

To make sure your server dosnt get filled with temp files create an .asp page with this code on

<%
On error resume next
set fs = CreateObject("Scripting.FileSystemObject")
fs.DeleteFile("Q:\

http://wwwroot\test\*.csv")

%>

then make it an include on the default page and then ever time someone opens the database it will clear all the files out