ASP to XLS Code Explanation

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.
TSchumanAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

weareuProgrammerCommented:
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
TSchumanAuthor Commented:
Weareu,

I tried your solution above but when i attempt to run it, It just sits there and nothing happens.  Any ideas?
0
weareuProgrammerCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
TSchumanAuthor Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.