create xls file thru asp page

Posted on 2001-08-06
Last Modified: 2008-03-27
we need to mke a form that when submitted will create a excel file on the webserver.
I found some code for an example:
Dim ExcelSheet
Set ExcelSheet = CreateObject("Excel.sheet")

' Make Excel visible through the Application object.
ExcelSheet.Application.Visible = True
' Place some text in the first cell of the sheet.
ExcelSheet.ActiveSheet.Cells(1,1).Value = "This is column A, row 1"
' Save the sheet.
ExcelSheet.SaveAs Server.Mappath("TEST.XLS")
' Close Excel with the Quit method on the Application object.
' Release the object variable.
Set ExcelSheet = Nothing
but it gives me this error:

error '800706bb'
The RPC server is too busy to complete this operation.

/index.asp, line 3

excel 2000 is installed on the server, AspAllowoutofProcComponents is true
iis 4 is running.

does anyone know how to create an excel file with asp?
Question by:mattdunlap
LVL 19

Expert Comment

ID: 6356923
Why do you need an Excel file on the server? It would be much better to either write a text file, save the info in a database, or not save it at all.

If you just want a text file, there are lots of options to do that. If you want to save it to a database, it would definitely be cleaner.
LVL 18

Expert Comment

ID: 6356956
Pretty easy really, this is what I use.  The fields are populated with record data from a dB, but it is easy to get it from a form, plus I have a requirement to save and then open the file;

<%@ Language=VBScript%>
<% ' Set database connection and recordset properties %>
<!--#INCLUDE FILE="Database.asp"-->
     sSql = Request("sSql")
     Set rs = RunWithRS(sSQL)
theSessionID = Session.SessionID
'Response.Write theSessionID
'Create a random Filename based on the SessionID

fileExcel = theSessionID&".xls"
filePath = Server.mapPath("temp")
filename = filePath & "\" & fileExcel

Set fs = Server.CreateObject("Scripting.FileSystemObject")
Set MyFile = fs.CreateTextFile(filename, 2, True)
strLineTitle = "" 'Initialize the variable for storing the fieldnames
strLineTitle = "Name" &chr(9) &_
"FirstName" &chr(9) &_
"Middle" &chr(9) &_
"LastName" &chr(9) &_
"Suffix" &chr(9) &_
"Title" &chr(9) &_
"Company" &chr(9) &_
"Department" &chr(9) &_
"Address1" &chr(9) &_
"Address2" &chr(9) &_
"Address3" &chr(9) &_
"City" &chr(9) &_
"State" &chr(9) &_
"ZipCode" &chr(9) &_
"Country" &chr(9) &_
"Phone" &chr(9) &_
"Fax" &chr(9) &_
"DirectLine" &chr(9) &_
"CompanyMaine" &chr(9) &_
"MobilePhone" &chr(9) &_
"Pager" &chr(9) &_
"Phone1" &chr(9) &_
"Phone2" &chr(9) &_
"Email" &chr(9) &_
"Email2" &chr(9) &_
"Email3" &chr(9) &_
"WebPage" &chr(9) &_
"Source" &chr(9) &_
"Level" &chr(9) &_
"Categories" &chr(9) &_
"V/S/O" &chr(9) &_
"Region" &chr(9) &_
            strLineTitle = strLineTitle & vbCrlf
         'Write this string into the file
          MyFile.writeline strLineTitle

         'Retrieve the values from the database and write into the Excel file
          Do while Not rs.EOF
            str = rs.Fields("Name")&chr(9) &_
rs.Fields("FirstName")&chr(9) &_
rs.Fields("Middle")&chr(9) &_
rs.Fields("LastName")&chr(9) &_
rs.Fields("Suffix")&chr(9) &_
rs.Fields("Title")&chr(9) &_
rs.Fields("Company")&chr(9) &_
rs.Fields("Department")&chr(9) &_
rs.Fields("Address1")&chr(9) &_
rs.Fields("Address2")&chr(9) &_
rs.Fields("Address3")&chr(9) &_
rs.Fields("City")&chr(9) &_
rs.Fields("State")&chr(9) &_
rs.Fields("ZipCode")&chr(9) &_
rs.Fields("Country")&chr(9) &_
rs.Fields("Phone")&chr(9) &_
rs.Fields("Fax")&chr(9) &_
rs.Fields("DirectLine")&chr(9) &_
rs.Fields("CompanyMain")&chr(9) &_
rs.Fields("MobilePhone")&chr(9) &_
rs.Fields("Pager")&chr(9) &_
rs.Fields("Phone1")&chr(9) &_
rs.Fields("Phone2")&chr(9) &_
rs.Fields("Email")&chr(9) &_
rs.Fields("Email2")&chr(9) &_
rs.Fields("Email3")&chr(9) &_
rs.Fields("WebPage")&chr(9) &_
rs.Fields("Source")&chr(9) &_
rs.Fields("Level")&chr(9) &_
rs.Fields("Categories")&chr(9) &_
rs.Fields("V/S/O")&chr(9) &_
rs.Fields("Region")&chr(9) &_
          strLine = str
          MyFile.writeline strLine

       'Clean up
       Set MyFile=Nothing
       Set fs=Nothing

' Now open the newly created .xls file
Response.CacheControl = "no-store"
Response.AddHeader "Pragma", "no-cache"
Response.Expires = -1
Response.ContentType = "application/"
Response.Redirect "temp/"&theSessionID&".xls"


Expert Comment

ID: 6357381
Give this a whirl...

Dim Excel, oSheet, oRng

 Set Excel = CreateObject("Excel.Application")
 Excel.Visible = True

 Set oSheet = Excel.ActiveSheet

 oSheet.Cells(1,1).Value = "TEXT"

 Set oRng = oSheet.Range("A1", "Z1")
 Excel.ActiveWorkbook.SaveAs Server.Mappath & "test.xls", FileFormat:=xlNormal
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.


Expert Comment

ID: 6364682
there is a license and threading problem if u run excel object in asp. usually, it is prefered that you create a html table to store your data and specify the content type to excel (application/, then your browser will open the html table in excel at client browser.


you can use mgfranz 's method to create a csv file instead of html table which also can be open by excel in client browser.
LVL 18

Expert Comment

ID: 6364723
I have never had a problem with what you are refering to LCP, but then again I have not had to develop an app with the Object for Excel, I have always just created the file with FSO.

Expert Comment

ID: 6364799
IF u have use excel object, u may face excel object make your pc hang sometimes. Your can imagine if a number of users create an excel object in web server at the same time.. the performance of web server can't be imaginated.

Microsoft have introduce another object library in office 2000 called Office Web Component, it also got similar functionality as excel object library and it is smaller in size. But you must install ms office 2000 in web server, and according to their license agreement, you must make sure the each client browser must install a version of ms office b4 use (if I didn't interpret wrong)..
therefore, I always recommend use html table and csv file format. :>

Expert Comment

ID: 6364884
LVL 18

Expert Comment

ID: 6364894
I see...
LVL 33

Expert Comment

ID: 8671383
No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:

Please leave any comments here within the next seven days.


EE Cleanup Volunteer

Accepted Solution

SpideyMod earned 0 total points
ID: 8736168
per recommendation

Community Support Moderator @Experts Exchange

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I would like to start this tip/trick by saying Thank You, to all who said that this could not be done, as it forced me to make sure that it could be accomplished. :) To start, I want to make sure everyone understands the importance of utilizing p…
Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

820 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question