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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


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

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Jquery Check against database on select from auto fill not working in all browsers 5 56
Replace value 2 55
Html split(text) 2 49
Building JSON Results Table FROM DB 9 50
I recently decide that I needed a way to make my pages scream on the net.   While searching around how I can accomplish this I stumbled across a great article that stated "minimize the server requests." I got to thinking, hey, I use more than one…
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…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

734 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