I have just spent two days tackling a similar issue in ASP.
I created an ASP page that uses a database SELECT statement and html TABLE to create an Excel spreadsheet. To create the spreadsheet, I used:
- Response.ContentType = "application/vnd.ms-excel"
- Response.AddHeader "content-disposition","attachment; filename=MyFile.xls"
First time user opens window and clicks on link, Excel spitout works GREAT!! Then the user closes their active window. Does not matter if they logout properly or not. When a new window is opened, and user logs back into the ASP app and tries to press link again, they then receive an error message:
"Microsoft Excel cannot access the file 'http://www.myurl.com/dir1/excel_spitout.asp'. There are several possible reasons:
- The file name or path name does not exist.
- The file you're trying to open is being used by another program. Close the document in the other program, and try again.
- The name of the workbook you're trying to save is the same as the name of another document that is read-only. Try saving the workbook with a different name."
I discovered that, whenever the error occurred, I could click on ANY OTHER LINK on my ASP page - so long as it was a link that made a database request - and then press the BACK button, come back to my original page, and then press my Excel link again, and ... voila!! The link would suddenly work!
It would not, however, work if the link I pressed was an HTML only page or did not make a database request from the server / back-end database.
Clearly this indicated that the Excel object (or maybe some other object) was being retained in memory, and that opening a new db connection and executing a recordset seemed to wipe it out.
I Googled and Googled and the only good answer I found on this topic was that this was a known issue in XL97 and that Microsoft had created a patch for it.
Fat lot of good a service pack for XL97 would do me when I and most of my users are using XL2003.
CHEAP AND DIRTY RESOLUTION:
I hate that I did this, but seeing as how no one has come up with a "clean" way to resolve this, such as using code to determine if the Excel object is still open or not, and I could not figure out how to do so by myself, I opted for a cheap & dirty solution. I created a new ASP page, called whenever the user presses on the "Create Excel Spitout" link. This intermediary web page does nothing more than:
- opens a database connection
- creates a small recordset (does not matter what info is retrieved, so long as the database is engaged in activity)
- executes it, then closes it, then ...
- redirects user to the ACTUAL "Create Excel Spitout" link.
I offer this solution to anyone who has been driven MAD by the MS Office bug that creates this error!! This also is a challenge --> if any of you can come up with a more sophisticated solution, I would love to hear about it.
Hoping this helps someone,