Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


create xls file thru asp page

Posted on 2001-08-06
Medium Priority
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
Technology Partners: 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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

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…
I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

688 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