Solved

ASP to XLS Code Explanation

Posted on 2003-10-30
6
323 Views
Last Modified: 2007-12-19
Ive been trying to find information on how to export ASP to XLS and there doesnt seem to be much out there.  I was wondering if anyone knew the rules about how to do this.  I would like to be able to create multiple worksheet in one file as well as adjusting the fonts, alignment, etc.

This is some of the code i have been playing around with:

Set ExcelApp = CreateObject("Excel.Application")
ExcelApp.Application.Visible = True
Set ExcelBook = ExcelApp.Workbooks.Add

ExcelBook.Worksheets(1).Cells(2, 2).Font.Name = "Verdana"
ExcelBook.Worksheets(1).Cells(2, 2).Font.Size = 10
ExcelBook.Worksheets(1).Cells(2, 2).Font.Italic = true
ExcelBook.Worksheets(1).Cells(2, 2).Value="Hello world"
ExcelBook.Worksheets(1).Cells(2, 2).HorizontalAlignment = 1
ExcelBook.Worksheets(1).Cells(2, 2).Font.Color = RGB(0,0,0)

ExcelBook.SaveAs "c:\yourfile.xls"
ExcelApp.Application.Quit
Set ExcelApp = Nothing

I thought changing the worksheet # would allow me to input data to a new worksheet but it doesnt appear to work that way.  Any help would be appreciated.
0
Comment
Question by:TSchuman
  • 2
  • 2
6 Comments
 
LVL 2

Expert Comment

by:weareu
ID: 9657182
Set ExcelApp = CreateObject("Excel.Application")
ExcelApp.Application.Visible = True
Set ExcelBook = ExcelApp.Workbooks.Add
Set ExcelSheet1 = ExcelBook.Worksheets(1)

ExcelSheet1.Cells(2, 2).Font.Name = "Verdana"
ExcelSheet1.Cells(2, 2).Font.Size = 10
ExcelSheet1.Cells(2, 2).Font.Italic = true
ExcelSheet1.Cells(2, 2).Value="Hello sheet 1"
ExcelSheet1.Cells(2, 2).HorizontalAlignment = 1
ExcelSheet1.Cells(2, 2).Font.Color = RGB(0,0,0)

Set ExcelSheet2 = ExcelBook.Worksheets.Add
ExcelSheet2.Cells(2, 2).Font.Name = "Verdana"
ExcelSheet2.Cells(2, 2).Font.Size = 10
ExcelSheet2.Cells(2, 2).Font.Italic = true
ExcelSheet2.Cells(2, 2).Value="Hello sheet 2"
ExcelSheet2.Cells(2, 2).HorizontalAlignment = 1
ExcelSheet2.Cells(2, 2).Font.Color = RGB(0,0,0)

ExcelBook.SaveAs "c:\yourfile.xls"
ExcelApp.Application.Quit
Set ExcelApp = Nothing
0
 

Author Comment

by:TSchuman
ID: 11636993
Weareu,

I tried your solution above but when i attempt to run it, It just sits there and nothing happens.  Any ideas?
0
 
LVL 2

Accepted Solution

by:
weareu earned 125 total points
ID: 11644277
I have tried the example by copy and pasting it on a few different machines and configurations. It seems to work fine and write a file in your c:\ folder called 'yourfile.xls'. In the browser itself it would not do anything no. If you would like to have the excel file downloaded to the users PC use the following code:

<%
Set ExcelApp = CreateObject("Excel.Application")
ExcelApp.Application.Visible = True
Set ExcelBook = ExcelApp.Workbooks.Add
Set ExcelSheet1 = ExcelBook.Worksheets(1)

ExcelSheet1.Cells(2, 2).Font.Name = "Verdana"
ExcelSheet1.Cells(2, 2).Font.Size = 10
ExcelSheet1.Cells(2, 2).Font.Italic = true
ExcelSheet1.Cells(2, 2).Value="Hello sheet 1"
ExcelSheet1.Cells(2, 2).HorizontalAlignment = 1
ExcelSheet1.Cells(2, 2).Font.Color = RGB(0,0,0)

Set ExcelSheet2 = ExcelBook.Worksheets.Add
ExcelSheet2.Cells(2, 2).Font.Name = "Verdana"
ExcelSheet2.Cells(2, 2).Font.Size = 10
ExcelSheet2.Cells(2, 2).Font.Italic = true
ExcelSheet2.Cells(2, 2).Value="Hello sheet 2"
ExcelSheet2.Cells(2, 2).HorizontalAlignment = 1
ExcelSheet2.Cells(2, 2).Font.Color = RGB(0,0,0)

ExcelBook.SaveAs Server.MapPath("yourfile.xls")
ExcelApp.Application.Quit
Set ExcelApp = Nothing

Response.Write "<SCRIPT>document.location.href = 'yourfile.xls';</SCRIPT>"
%>

Do not use Response.Redirect since this will not change the response type... This way the browser will request and download the file just written to the same path as this file is in. If this example returns an error, you might need to grant rights to your IIS user. Alternatively if you are confident that your web-server is secure you could make your administrator user your IIS user.
0
 

Author Comment

by:TSchuman
ID: 11646563
Thanks Weareu, that did it!

Im not sure what was wrong before but when i would run the ASP it would just sit there like it was caught in an endless loop.  Having the page response.write the file after its done must have been what was missing.

Thanks again for your help.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Out the count variables 6 53
Age between date range query (SP) 13 52
XML Parsing Classic ASP 5 51
Choose the older file FSO 6 43
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…
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 tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…

861 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

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now