Save As Dialog Box

[rn5a]

When users submit a Form with a SQL query, the resultset is retrieved
from a MS-Access database table which is then exported to an MS-Excel
worksheet. After the records get exported to MS-Excel, I want to give
users the option to download the Excel file to their local machine. In
other words, when users click a download link, I want the Save As..
dialog box to pop-up so that users can save the Excel file to their
local machine (note that I don't want users to view the Excel
worksheet in their browser). This is how I tried it:

<%
Dim strFile
'here strFile is just the file name & does not include it's
physical path
strFile=Request("file")

Response.ContentType="application/octetstream"
Response.AddHeader "Content-Disposition","filename=" & strFile

Dim objStream
strFile=Server.MapPath(strFile)

Set objStream=Server.CreateObject("ADODB.STREAM")
objStream.Open
objStream.Type=1
objStream.LoadFromFile(strFile)

Response.BinaryWrite(objStream.Read)

objStream.Close
Set objStream=Nothing
%>

The above code doesn't give users the option to save the Excel file in
their local machines. Rather the Excel worksheet opens in the browser.

What am I doing wrong here?

What I find is if I pass the PHYSICAL PATH of the Excel file to the
LoadFromFile method, say, something like C:\Inetpub\wwwroot\XL
\XLFile.xls & change the above code slightly to look like this:

<%
Dim strFile,strFileName
strFile=Request("file")
strFile="C:\Inetpub\wwwroot\XL\" & strFile

'just get the file name; exclude it's path
strFileName=Right(strFile,Len(strFile)-InStrRev(strFile,"\"))

Response.ContentType="application/octetstream"
Response.AddHeader "Content-Disposition","filename=" & strFileName

Dim objStream
Set objStream=Server.CreateObject("ADODB.STREAM")
objStream.Open
objStream.Type=1
objStream.LoadFromFile(strFile)

Response.BinaryWrite(objStream.Read)

objStream.Close
Set objStream=Nothing
%>

In the 2nd code, I am passing the file name as well as it's entire
PHYSICAL PATH to the LoadFromFile method & this does pop-up the Save
As... dialog box so that users can download the Excel file to their
local machines.

But in the 1st code, I am passing the file name along with it's
VIRTUAL PATH to the LoadFromFile method. Hence to get the PHYSICAL
PATH, I am using Server.MapPath but the Excel file opens up in the
browser & doesn't pop-up the Save As.. dialog box.

[rn5a]

On Apr 4, 4:55 am, [e-mail] wrote:

When users submit a Form with a SQL query, the resultset is retrieved
from a MS-Access database table which is then exported to an MS-Excel
worksheet. After the records get exported to MS-Excel, I want to give
users the option to download the Excel file to their local machine. In
other words, when users click a download link, I want the Save As..
dialog box to pop-up so that users can save the Excel file to their
local machine (note that I don't want users to view the Excel
worksheet in their browser). This is how I tried it:

<%
Dim strFile
'here strFile is just the file name & does not include it's
physical path
strFile=Request("file")

Response.ContentType="application/octetstream"
Response.AddHeader "Content-Disposition","filename=" & strFile

Dim objStream
strFile=Server.MapPath(strFile)

Set objStream=Server.CreateObject("ADODB.STREAM")
objStream.Open
objStream.Type=1
objStream.LoadFromFile(strFile)

Response.BinaryWrite(objStream.Read)

objStream.Close
Set objStream=Nothing
%>

The above code doesn't give users the option to save the Excel file in
their local machines. Rather the Excel worksheet opens in the browser.

What am I doing wrong here?

What I find is if I pass the PHYSICAL PATH of the Excel file to the
LoadFromFile method, say, something like C:\Inetpub\wwwroot\XL
\XLFile.xls & change the above code slightly to look like this:

<%
Dim strFile,strFileName
strFile=Request("file")
strFile="C:\Inetpub\wwwroot\XL\" & strFile

'just get the file name; exclude it's path
strFileName=Right(strFile,Len(strFile)-InStrRev(strFile,"\"))

Response.ContentType="application/octetstream"
Response.AddHeader "Content-Disposition","filename=" & strFileName

Dim objStream
Set objStream=Server.CreateObject("ADODB.STREAM")
objStream.Open
objStream.Type=1
objStream.LoadFromFile(strFile)

Response.BinaryWrite(objStream.Read)

objStream.Close
Set objStream=Nothing
%>

In the 2nd code, I am passing the file name as well as it's entire
PHYSICAL PATH to the LoadFromFile method & this does pop-up the Save
As... dialog box so that users can download the Excel file to their
local machines.

But in the 1st code, I am passing the file name along with it's
VIRTUAL PATH to the LoadFromFile method. Hence to get the PHYSICAL
PATH, I am using Server.MapPath but the Excel file opens up in the
browser & doesn't pop-up the Save As.. dialog box.

OK....I got it. The line

Response.AddHeader "Content-Disposition","filename=" & strFile

should read as

Response.AddHeader "Content-Disposition","attachment;filename=" &
strFile