Save results of ASP to excel and save file on server

Currently I am using the:

Response.ContentType = "application/vnd.ms-excel"
Response.AddHeader "Content-Disposition", "attachment;
filename="myFile.xls"

Opening this page will prompt the user to save or download, as
expected.

What I am trying to do is create the XLS documents overnight so the
user doesnt need to wait for the asp script to take its course. They
can simply click on the "myFile.xls" and download its already saved
results. However, I don't know how to save the file dynamically
without user intervention to click "Save"

Thanks in advance for any assistance.

Mark
Tue Apr 03 07:27:05 CDT 2007

In my searching I found many people posting this same sort of question
with no answers. So since I was fortunate enough to locate one and
get it to work I thought I would share...
Placing the 2 lines of:

>> Response.ContentType = "application/vnd.ms-excel"
>> Response.AddHeader "Content-Disposition", "attachment; filename="thisnamedoesnotmatter.xls"

at the top of your GET page will save the file

The filename after the "filename=" above will NOT be your filename.
Whatever you name it in the code snippet below will be. But you must
have it there so it will load the data in excel format. I couldnt get
the code to work without it.

Dim objSXH, objADOStream

Set objSXH = Server.CreateObject("Msxml2.ServerXMLHTTP.4.0")
'Set objSXH = Server.CreateObject("Msxml2.XMLHTTP.4.0")
objSXH.setTimeouts 0,0,0,0
link = "http://www.whateverlink.com?q=1"
objSXH.open "GET", link, false
objSXH.send


Set objADOStream = Server.CreateObject("ADODB.Stream")
objADOStream.Type = 1 'Binary
objADOStream.Open
objADOStream.Write objSXH.responseBody
strFileName = "C:\temp.xls"


objADOStream.SaveToFile strFileName
objADOStream.Close

Hope this helps whoever may be wanting.

Anthony
Tue Apr 03 07:59:41 CDT 2007


<desju@aol.com> wrote in message
news:1175596144.416369.53180@l77g2000hsb.googlegroups.com...
> Currently I am using the:
>
> Response.ContentType = "application/vnd.ms-excel"
> Response.AddHeader "Content-Disposition", "attachment;
> filename="myFile.xls"
>
> Opening this page will prompt the user to save or download, as
> expected.
>
> What I am trying to do is create the XLS documents overnight so the
> user doesnt need to wait for the asp script to take its course. They
> can simply click on the "myFile.xls" and download its already saved
> results. However, I don't know how to save the file dynamically
> without user intervention to click "Save"
>
> Thanks in advance for any assistance.
>

First of all lets be clear that the Save dialog is displayed either because
the content disposition indicates the content is an attachment OR because
the user has not allowed that type of content to be opened automatically in
the browsers. It has nothing to do with whether the content is dynamically
created on the fly or not.

Hence fetching a pre-built xls may still show the user a save dialog just as
much as removing attachment from the content-disposition could be openned
directly in-browser.

It sounds like you just want to cache the xls content so that the code
needed to generate it doesn't have to run everytime. Do you really need
this to run overnight?

Assuming that the whole content is created through calls to Response.Write
you can replace Response object with an instance of an ADODB.Stream object
in text mode.

Something like this air code:-


sFilePath = "c:\somefolder\excelcache\myFile.xls"
Set oStream = Server.CreateObject("ADODB.Stream")

If NewFileNeedsGenerating Then

oStream.Type = 2 ' Text
oStream.CharSet = "Windows-1252" 'Adjust as necessary
oStream.Open

'Response.Write "<table><tr>....your stuff...</tr></table>"
oStream.WriteText "<table><tr>....your stuff...</tr></table>"

oStream.SaveToFile sFilePath
oStream.Position = 0
oStream.Type = 1 ' binary

Else

oStream.Type = 1 ' binary
oStream.Open
oStream.LoadFromFile sFilePath

End If

Response.ContentType = "application/vnd.ms-excel"
Response.AddHeader "Content-Disposition", "filename=myFile.xls"
Response.BinaryWrite oStream.Read()

oStream.Close

Mark
Tue Apr 03 17:47:50 CDT 2007

Yes thank you.

I did start to do something close to this. The problem was I took the
easy way out in creating the excel files for the client to download in
the first place by adding a header to a HTML populated page. Then
when they complained that it took 3 minutes to load a year to date
file, I had to come up with this solution.

The reason that I have them run over night is so they are fresh and
ready for the client should they desire to view the results. So I
have a script that fires using Windows Scheduler that creates 50
reports. Now they simply click on the link to the overnight created
excel file and it opens in seconds.

Thank you again for your quick response.

Mark
Tue Apr 03 17:50:20 CDT 2007

Oh and to answer your question... the reason i needed to get rid of
the dialog box was to run these massive pulls from the database one at
a time overnight. I expect the user to see a dialog box when trying
to download the files. The difference is they dont have to wait
anymore to get their data. (the downside is that it is not exactly
live data)