OutFront Forums
     Home    Register     Search      Help      Login    

Follow Us
On Facebook
On Twitter
RSS
Via Email

Recent Posts
Todays Posts
Most Active posts
Posts since last visit
My Recent Posts
Mark posts read

Sponsors
Shopping Cart Software
Ecommerce software integrated into Frontpage, Dreamweaver and Golive templates. No monthly fees and available in ASP and PHP versions.
Website Templates
We also have a wide selection of Dreamweaver, Expression Web and Frontpage templates as well as webmaster tools and CSS layouts.
Frontpage website templates
Creative Website Templates for FrontPage, Dreamweaver, Flash, SwishMax
 

Problem Creating Excel downloadable link

 
View related threads: (in this forum | in all forums)

Logged in as: Guest
Users viewing this topic: none
Printable Version 

All Forums >> Web Development >> ASP, PHP, and Database >> Problem Creating Excel downloadable link 		Page: [1]
 
Long Island Lune

 

Posts: 2340
Joined: 6/8/2002
From: New York
Status: offline

  		Problem Creating Excel downloadable link - 7/1/2003 23:44:00   
Howdy all,

I have a page that creates an Excel spreadsheet from the information in my database. I created an ASP/VB script. All works fine. The database opens and fills the Excel Spreadsheet. The problem is that I want to make a link that a person can click on to download the spreadsheet to their computer. In ASP, I used the following line after the sheet was created:

response.write " <a href=' client.xls' >Client List</a>"

Theoritically, this should work!!!

In reality what happens is :
1): The client clicks the link and a password box pops up.
2): You have to click [Cancel]. You don' t really have a choice.
3): The spreadsheet appears in the browser.

Totally not what I want done.

The directory that the spreadsheet is created in has full R/W/D permissions set. I don' t think it is password protected either. People save photos in the directory all the time. So I' m stumped..

Has anyone had this problem / know what to do???
Any help will be greatly appreciated.
Thanks
LLLuneeeee :)

_____________________________

SerenityNet

 

Posts: 1387
Joined: 6/12/2001
From: Allen, TX, USA
Status: offline

  		RE: Problem Creating Excel downloadable link - 7/2/2003 1:04:20   
I don' t have the answer for you, but I recall seeing this post. Maybe it will help.

_____________________________

</Chaos, panic, & disorder - my work here is done.>

(in reply to Long Island Lune)
Spooky

 

Posts: 26681
Joined: 11/11/1998
From: Middle Earth
Status: offline

  		RE: Problem Creating Excel downloadable link - 7/2/2003 2:55:06   
Pass the filename to an asp file handler like so :

link.asp?f=excel.xls

Link.asp looks something like this : 

filePath = " D:\Inetpub\wwwroot\files\" 
fileName = request.querystring(" f" )

Response.ContentType = " application/asp-unknown" 
Response.AddHeader " content-disposition" ," attachment; filename="  & fileName
Set FStream = Server.CreateObject(" ADODB.Stream" )
FStream.Open()
FStream.Type = 1
FStream.LoadFromFile(filePath)
Response.BinaryWrite FStream.Read()
FStream.Close
Set FStream = Nothing


_____________________________

If you arent part of the solution, then there is good money to be made prolonging the problem

Sp:)ky


(in reply to Long Island Lune)
Long Island Lune

 

Posts: 2340
Joined: 6/8/2002
From: New York
Status: offline

  		RE: Problem Creating Excel downloadable link - 7/2/2003 12:09:27   
Thanks Spooky.
I' ll give it a try

LLLuneeeee:)

_____________________________


(in reply to Long Island Lune)
Long Island Lune

 

Posts: 2340
Joined: 6/8/2002
From: New York
Status: offline

  		RE: Problem Creating Excel downloadable link - 7/28/2003 12:23:37   
Hi Spooky,

Sorry it took so long to respond with my results. Unfortunately it did not work. Here' s what I did:

Page I:
This is the page that creates the .xls file that I want downloadable.
On the bottom of this page (under the ASP/VB script) I placed a link.

It looks like this:

<a href=" DistributorExcel2.asp?f=distributors.xls" >I
want to download the Excel Spread Sheet NOW</a>

That sent the contents to page II.

Page II:
I placed the code you provided. It looks like this:

<%
filePath = " /uploads/"
fileName = request.querystring(" f" )
Response.ContentType = " application/asp-unknown"
Response.AddHeader " content-disposition" ," attachment; filename=" & fileName
Set FStream = Server.CreateObject(" ADODB.Stream" )
FStream.Open() FStream.Type = 1
FStream.LoadFromFile(filePath)
Response.BinaryWrite FStream.Read()
FStream.Close
Set FStream = Nothing
%>

The only difference in the code is that I put a reference to a file on the server, not my hard drive. (In RED above). This is all that is on page II. There is no other code of any type.

I get: " Page Cannot Be Displayed" -when I test it.

Can you see anything immedately wrong with what I did?
Thanks,
LLLuneeeee :)

< Message edited by Long Island Lune -- 7/28/2003 12:26:36 PM >

_____________________________


(in reply to Long Island Lune)
bobby

 

Posts: 11479
Joined: 8/15/1969
From: Seattle WA USA
Status: offline

  		RE: Problem Creating Excel downloadable link - 7/28/2003 13:29:17   
Have you tried placing it into its own directory and linking to that folder?

Maybe try a .zip ?

_____________________________

Talk to your kittens about catnip, or someone else will.


:)

(in reply to Long Island Lune)
Long Island Lune

 

Posts: 2340
Joined: 6/8/2002
From: New York
Status: offline

  		RE: Problem Creating Excel downloadable link - 7/28/2003 14:01:41   
Bobby,

Actually, the file and the pages are in the same directory " upload" . Do you mean remove the line:

filePath = " /uploads/"

then what would I do with the line:

FStream.LoadFromFile(filePath)
?

ZIP:
I wish I could. The site would have to automatically generate the zip itself after it created the xls file. I have an extended WinZip programmers library that removes the need for me to create zips files manually and does it automatically from a script that you create and call from within C++, -BUT it is only good for a desktop hard drive, not the net. I wish I could do that. It would solve my problem immediately.

_____________________________


(in reply to Long Island Lune)
Long Island Lune

 

Posts: 2340
Joined: 6/8/2002
From: New York
Status: offline

  		RE: Problem Creating Excel downloadable link - 7/28/2003 14:04:01   
quote:

8/15/1969


Holy Moly!!!!!!!!!!! 34 years on OutFront? You should get an award!!!

_____________________________


(in reply to Long Island Lune)
Spooky

 

Posts: 26681
Joined: 11/11/1998
From: Middle Earth
Status: offline

  		RE: Problem Creating Excel downloadable link - 7/28/2003 17:46:31   
FStream.LoadFromFile(filePath & FileName)

_____________________________

If you arent part of the solution, then there is good money to be made prolonging the problem

Sp:)ky


(in reply to Long Island Lune)
Long Island Lune

 

Posts: 2340
Joined: 6/8/2002
From: New York
Status: offline

  		RE: Problem Creating Excel downloadable link - 7/29/2003 14:26:47   
Spooky,

It still didn' t work. I think the problem might be in the ContentType or AddHeader lines. This is driving me crazy...

Have you ever had a download link for a straight .xls (non-zip) file on any of your sites before? Any examples?

I tried an internet search for this problem. I can find many examples of how to create and display an Excel file inside a webpage but not to make one downloadable.

Thank God for Tylenol...

LLLuneeeee :):):)

_____________________________


(in reply to Long Island Lune)
rdouglass

 

Posts: 9280
From: Biddeford, ME USA
Status: offline

  		RE: Problem Creating Excel downloadable link - 7/29/2003 14:48:19   
Have you seen this?

http://www.pstruh.cz/tips/detpg_RSConvert.htm

_____________________________

Don't take you're eye off your final destination.

ASP Checkbox Function Tutorial.

(in reply to Long Island Lune)
bobby

 

Posts: 11479
Joined: 8/15/1969
From: Seattle WA USA
Status: offline

  		RE: Problem Creating Excel downloadable link - 7/29/2003 16:00:33   
quote:

34 years on OutFront? You should get an award!!!

I' ve already been the recipient of the " Oh God, not him again." award three years running..

:)

RD - nice link, looks like a handy bit of code...

Lune - good luck, this looks like a tough one.


:)

_____________________________

Talk to your kittens about catnip, or someone else will.


:)

(in reply to Long Island Lune)
Spooky

 

Posts: 26681
Joined: 11/11/1998
From: Middle Earth
Status: offline

  		RE: Problem Creating Excel downloadable link - 7/29/2003 19:44:39   
Make sure friendly errors is off in IE so that you get a true error -
Note the change below from your code

<%
filePath = " D:\Inetpub\wwwroot\files\"
fileName = request.querystring(" f" )

Response.ContentType = " application/asp-unknown"
Response.AddHeader " content-disposition" ," attachment; filename=" & fileName
Set FStream = Server.CreateObject(" ADODB.Stream" )
FStream.Open()
FStream.Type = 1
FStream.LoadFromFile(filePath&fileName)
Response.BinaryWrite FStream.Read()
FStream.Close
Set FStream = Nothing

%>

_____________________________

If you arent part of the solution, then there is good money to be made prolonging the problem

Sp:)ky


(in reply to Long Island Lune)
Long Island Lune

 

Posts: 2340
Joined: 6/8/2002
From: New York
Status: offline

  		RE: Problem Creating Excel downloadable link - 7/30/2003 14:21:36   
rDouglass,

Thanks for the snipet. :)

bobby,

Could I have finally met my match with this problem? :)

Spooky,

Yeah - I saw that yesterday and made that single line into two seperate lines.
But I think I might be getting a little closer.
I' ve isolated the problem area.

Look at the code below:

<%
filePath = " /uploads/"
fileName = request.querystring(" f" )
Response.ContentType = " application/asp-unknown"
Response.AddHeader " content-disposition" ," attachment; filename=" & fileName
Set FStream = Server.CreateObject(" ADODB.Stream" )
FStream.Open()
FStream.Type = 1
' FStream.LoadFromFile(FilePath&fileName)
' Response.BinaryWrite FStream.Read()
FStream.Close
Set FStream = Nothing
%>

In the code above I disabled the lines in blue with a ' . When I do that the download box pops up. I get an emtpy sheet downloaded. But if I include those two lines I get the: " This page cannot be displayed" error. :)

_____________________________


(in reply to Long Island Lune)
Spooky

 

Posts: 26681
Joined: 11/11/1998
From: Middle Earth
Status: offline

  		RE: Problem Creating Excel downloadable link - 7/30/2003 16:16:32   
The file path should still be the full file based path in the code above

_____________________________

If you arent part of the solution, then there is good money to be made prolonging the problem

Sp:)ky


(in reply to Long Island Lune)
Long Island Lune

 

Posts: 2340
Joined: 6/8/2002
From: New York
Status: offline

  		RE: Problem Creating Excel downloadable link - 7/30/2003 17:02:56   
Spooky,

You mean like a full server based: http://......... all the way to the file?

_____________________________


(in reply to Long Island Lune)
Spooky

 

Posts: 26681
Joined: 11/11/1998
From: Middle Earth
Status: offline

  		RE: Problem Creating Excel downloadable link - 7/30/2003 17:13:58   
No, same as the original code :

filePath = " D:\Inetpub\wwwroot\files\"

_____________________________

If you arent part of the solution, then there is good money to be made prolonging the problem

Sp:)ky


(in reply to Long Island Lune)
Long Island Lune

 

Posts: 2340
Joined: 6/8/2002
From: New York
Status: offline

  		RE: Problem Creating Excel downloadable link - 7/31/2003 13:42:51   
Spooky,

I placed a test Excel file in my hard drive root: C:\ for ease of testing.
Now it tells me the file cannot be opened.
I checked the Excel itself to make sure it was not locked or anything.
It doesn' t seem to be locked or protected.
Then I checked the file' attributes in Windows Explorer. It' s set to Archive.
So I don' t think the problem is the file can' t be opened.

So for temporary testing purproses the code segment I' m testing looks like this:

filePath = " c:\distributor.xls"
' Response.ContentType = " application/vnd.ms-excel"
' Response.AddHeader " content-disposition" ," attachment; filename=" & fileName
Set FStream = Server.CreateObject(" ADODB.Stream" )
FStream.Open()
FStream.Type = 1
FStream.LoadFromFile(filePath)
Response.BinaryWrite FStream.Read()
FStream.Close
Set FStream = Nothing

As you can see, I deactivated the BLUE lines above just to see what' s up with the opening of the stream and access to the file. And it' s telling me the file cannot be opened.

Too many days... Too much pressure... I' m losin it!!!
See my avatar? That' s the perfect ME right now...

Formula:
FP + EXCEL + Downloadable (:)) = Disaster

LLLuneeeee :)

< Message edited by Long Island Lune -- 7/31/2003 1:45:09 PM >

_____________________________


(in reply to Long Island Lune)
J-man

 

Posts: 936
From: Canada
Status: offline

  		RE: Problem Creating Excel downloadable link - 7/31/2003 15:37:09   
Maybe i a little lost here cause to me, the problem seems to have deivated from the original issue. It sounds like your .xls file is being dynamically produced, being populated on the fly? is this correct? or is the xls file created on the server and the server scripts save the .xls file to a folder?

< Message edited by J-man -- 7/31/2003 3:39:17 PM >

(in reply to Long Island Lune)
Spooky

 

Posts: 26681
Joined: 11/11/1998
From: Middle Earth
Status: offline

  		RE: Problem Creating Excel downloadable link - 7/31/2003 15:54:32   
Heres an example

Its using the exact code : 

<%
fileName = " test.xls" 
Response.ContentType = " application/asp-unknown" 
Response.AddHeader " content-disposition" ," attachment; filename="  & fileName
Set FStream = Server.CreateObject(" ADODB.Stream" )
FStream.Open()
FStream.Type = 1
FStream.LoadFromFile(server.mappath(fileName))
Response.BinaryWrite FStream.Read()
FStream.Close
Set FStream = Nothing
%>


< Message edited by Spooky -- 7/31/2003 3:55:30 PM >

_____________________________

If you arent part of the solution, then there is good money to be made prolonging the problem

Sp:)ky


(in reply to Long Island Lune)
Long Island Lune

 

Posts: 2340
Joined: 6/8/2002
From: New York
Status: offline

  		RE: Problem Creating Excel downloadable link - 7/31/2003 18:22:18   
J-Man,

Yes, the .xls is being dynamically created.

When you visit the first page, the database is opened, data is pulled out and placed into a dynamiclly created .xls file on-the-fly. Then the .xls file closes. Now it exists in one of my server directories. It works great. Each time I go to the page, I upload the .xls file back to my laptop for checking purposes. It creates an .xls file of my database of distributors perfectly every time. So now I have the file in my /uploads/ directory on the server.

Then I send the .xls' filename to a second page (the one I' m having trouble with in this post) so it can be downloaded to the client.

WinZip:
I contacted them today to see if they have an add-on that lets a file be dynamically placed into a .ZIP from inside a website. They have many add-ons if your a registered user - which I am. Unfortunately they do not currently have an add-on like this. HARD COOKIES FOR ME!!!!

So the file is being created on-the-fly. And it exists in the server directory waiting to be downloaded.

That is where I stand right now.

LLLuneeeee :)

_____________________________


(in reply to Long Island Lune)
Long Island Lune

 

Posts: 2340
Joined: 6/8/2002
From: New York
Status: offline

  		RE: Problem Creating Excel downloadable link - 7/31/2003 18:23:21   
Spooky,

I' m going to play around with the code you just posted and see what happens.

LLLuneee :)

_____________________________


(in reply to Long Island Lune)
J-man

 

Posts: 936
From: Canada
Status: offline

  		RE: Problem Creating Excel downloadable link - 8/1/2003 2:00:09   
quote:

so it can be downloaded to the client.

how do you want to the client to download the xls? by clicking on a link on a webpage or email?


quote:

Then the .xls file closes

automatically or manually?

what languages / server languages work on the server?

Maybe saving the .xls to a folder after it is created is not the answer? maybe you need to dynamically create it every time unless you need specific timestamped records, maybe you could cache a zip file in a users computer, display pages from the zip on the users computer...Java classes, all from visiting to a web page. I' m sure there are easier work arounds.....

(in reply to Long Island Lune)
Long Island Lune

 

Posts: 2340
Joined: 6/8/2002
From: New York
Status: offline

  		RE: Problem Creating Excel downloadable link - 8/1/2003 13:43:32   
Hi J-man,

quote:


how do you want to the client to download the xls? by clicking on a link on a webpage or email?


By Download on the webpage. Once page (1) creates the .xls file I use a form/post to send the .xls name to page (2). So they click the form-post button and are taken to page (2). But the user never actually sees page (2). What they see is the download box on their screen pop-up. The rest is standard-download-operation procedures from there.

quote:


automatically or manually?


Automatically. The entire .xls creation process is done inside ASP / VBScript on page (1) prior to any attempts to download the file to the user. As a matter of fact I have the post/form button set to appear on page (1), only if all the operations are completed correctly inside the ASP / VBScript code.

In other words I' m using a conditional statement that makes sure the file was created and filled with data dynamically. If it was completed correctly, then the post/form button will appear on page (1).

My server can handle most all languages. The only thing I think it does not handle is PHP.

LLLuneeeee :)

_____________________________


(in reply to Long Island Lune)
Long Island Lune

 

Posts: 2340
Joined: 6/8/2002
From: New York
Status: offline

  		RE: Problem Creating Excel downloadable link - 8/1/2003 14:38:11   
Spooky,

WE HAVE A WINNER!!!

It worked great. Here' s the final code used to accomplish the task:

<%
fileName = request.form(" f" )
Response.ContentType = " application/asp-unknown"
Response.AddHeader " content-disposition" ," attachment; filename=" & fileName
Set FStream = Server.CreateObject(" ADODB.Stream" )
FStream.Open()
FStream.Type = 1
FStream.LoadFromFile(server.mappath(" /uploads/" & fileName))
Response.BinaryWrite FStream.Read()
FStream.Close
Set FStream = Nothing
%>

For anyone interested in how the Excel file was created on page (1), here is the base code minus the conditional-checking:


<%
connect = " deleted out the name"
test = " SELECT Count(' User' ) AS [myTotal] FROM DistNewUser WHERE CredtCardType <> ' 0' And CredtCardType <> ' Free' And Client <> ' Inactive' "
Set myConn = Server.CreateObject(" ADODB.Connection" )
myPath = Server.MapPath(connect)
myConn.Open " Driver={Microsoft Access Driver (*.mdb)}; DBQ=" & myPath & " ;"
mySQL = test
Set myRS = Server.CreateObject(" ADODB.RecordSet" )
myRS.Open mySQL, myConn
amount = myRS(" myTotal" )
myRS.Close
Set myRS = Nothing
myConn.Close
Set myConn = Nothing
if (amount > 0) Then
Set objConn = Server.CreateObject (" ADODB.Connection" )
objConn.Open " DBQ=" & Server.Mappath (connect) & " ;Driver={Microsoft Access Driver (*.mdb)};"
Set RS = objConn.Execute (" SELECT * FROM DistNewUser WHERE CredtCardType <> ' 0' And CredtCardType <> ' Free' And Client <> ' Inactive' ORDER BY Coy" )
file_being_created= " /uploads/distributors.xls"
set fso = createobject(" scripting.filesystemobject" )
Set act = fso.CreateTextFile(server.mappath(file_being_created), true)
act.WriteLine(" <html><body>" )
act.WriteLine(" <table border=" " 1" " >" )
act.WriteLine(" <tr>" )
act.WriteLine(" <th nowrap>Company:</th>" )
act.WriteLine(" <th nowrap>Address:</th>" )
act.WriteLine(" <th nowrap>Town:</th>" )
act.WriteLine(" <th nowrap>State:</th>" )
act.WriteLine(" <th nowrap>Zip:</th>" )
act.WriteLine(" <th nowrap>Country:</th>" )
act.WriteLine(" <th nowrap>Contact:</th>" )
act.WriteLine(" <th nowrap>Phone:</th>" )
act.WriteLine(" <th nowrap>Date Joined:</th>" )
act.WriteLine(" </tr>" )
For i = 1 To amount
coy= RS.Fields (" Coy" )
country= RS.Fields (" Address" )
town= RS.Fields (" Town" )
st= RS.Fields (" St" )
zip= RS.Fields (" Zip" )
country= RS.Fields (" Country" )
contact= RS.Fields (" Contact" )
ph= RS.Fields (" Ph" )
datejoined = RS.Fields (" DateJoined" )
act.WriteLine(" <tr>" )
act.WriteLine(" <td align=" " right" " >" & rs(" coy" ) & " </td>" )
act.WriteLine(" <td align=" " right" " >" & rs(" address" ) & " </td>" )
act.WriteLine(" <td align=" " right" " >" & rs(" town" ) & " </td>" )
act.WriteLine(" <td align=" " right" " >" & rs(" st" ) & " </td>" )
act.WriteLine(" <td align=" " right" " >" & rs(" zip" ) & " </td>" )
act.WriteLine(" <td align=" " right" " >" & rs(" country" ) & " </td>" )
act.WriteLine(" <td align=" " right" " >" & rs(" contact" ) & " </td>" )
act.WriteLine(" <td align=" " right" " >" & rs(" ph" ) & " </td>" )
act.WriteLine(" <td align=" " right" " >" & rs(" datejoined" ) & " </td>" )
act.WriteLine(" </tr>" )
RS.MoveNext
Next
act.WriteLine(" </table></body></html>" )
act.close
RS.Close
objConn.Close
Set RS = nothing
Set objConn = Nothing
End If
%>

THANKS to Spooky, J-Man, rDouglass, bobby and SerenityNet.
An extra award goes to SPOOKY for his patience.

THANK GOD I can finally close this post a SUCCESS!!!

LLLuneeeee :):):):):)

_____________________________


(in reply to Long Island Lune)
J-man

 

Posts: 936
From: Canada
Status: offline

  		RE: Problem Creating Excel downloadable link - 8/1/2003 19:18:30   
I' m glad you figured it out!

Hope you have fun with it and it keeps improving:)

<%VB

Private Sub CommandButton1_Click()
' Define the postscript and .pdf file names.
Dim PSFileName as String
Dim PDFFileName as String
PSFileName = " c:\myPostScript.ps"
PDFFileName = " c:\myPDF.pdf"

Dim MySheet As WorkSheet
Set MySheet = ActiveSheet
MySheet.Range(" myRange" ).PrintOut copies:=1, preview:=False, ActivePrinter:=" Acrobat Distiller" , printtofile:=True, collate:=True, prttofilename:=PSFileName

Dim myPDF As PdfDistiller
Set myPDF = New PdfDistiller
myPDF.FileToPDF PSFileName, PDFFileName, " "

End Sub

(in reply to Long Island Lune)
Long Island Lune

 

Posts: 2340
Joined: 6/8/2002
From: New York
Status: offline

  		RE: Problem Creating Excel downloadable link - 8/1/2003 19:52:16   
I like to say " WE" figured it out. A group process indeed.

Nice code. Where was your code 5 days ago???:)

:)

_____________________________


(in reply to Long Island Lune)
Page:   [1]

All Forums >> Web Development >> ASP, PHP, and Database >> Problem Creating Excel downloadable link 		Page: [1]
Jump to: 1





New Messages No New Messages
Hot Topic w/ New Messages Hot Topic w/o New Messages
Locked w/ New Messages Locked w/o New Messages
 Post New Thread
 Reply to Message
 Post New Poll
 Submit Vote
 Delete My Own Post
 Delete My Own Thread
 Rate Posts


Copyright © 2009 Thomas Brunt's OutFront Inc. All Rights Reserved.