Solved

Exporting ASP Reports to Excel Format

Posted on 2000-04-17
5
319 Views
Last Modified: 2008-03-17
I am currenly exporting data from my .asp page to Excel.  In my code, I dynamically create html tables based on selection a user made on the previous page.  For each of these tables, I am creating a separate worksheet in Excel.  

How do I:

1.  Add only the worksheets that I generate to the Excel workbook (Excel defaults to already having 3 worksheets in the workbook then when I add mine, I have 3 blank worksheets plus the ones I add).

2.  How do I arrange the worksheets in the order.  Currently, if there are 3 html tables, I generate 3 worksheets.  The tabs in my excel workbook look like this: sheet6, sheet5, sheet4, sheet1, sheet2, sheet3.)

3.  How do I change the name of the tab using code, from something like:  sheet1 to "1999 Activity Report".

4.  How can I make the columns autofit using code?  And can I bold line 1 and 2 which is the report title and field heading respectively?

5.  How can I prompt the user to save the worksheet to there hard drive?  Currently, I am using:

xlWorksheet.SaveAs Server.MapPath(".") & "\" & strFile

Thanks,

kpetti
0
Comment
Question by:kpetti
[X]
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
  • 2
  • 2
5 Comments
 
LVL 9

Expert Comment

by:TTom
ID: 2724553
As far as the formatting goes, it seems to me that you can output formatted HTML and it will be "reproduced" by Excel.

My .02pts,

Tom
0
 
LVL 25

Accepted Solution

by:
clockwatcher earned 300 total points
ID: 2725069
1.  How do I add only the worksheets I generate.

  You can either work directly on the default worksheets or you can simply remove them.  Assuming wrkbook is your workbook object:

  wrkbook.worksheets("Sheet1").delete

2.  How do I arrange my worksheets?

  Use the move method of your sheet object.
 
  mysheet.move(optional before as worksheet, optional after as worksheet)

  Where 'before' is the sheet object you want it before or 'after' is the sheet you want it after.  Only specify one or the other (either 'before' or 'after') not both.

3.  How do I change the tab names?

  Use the sheet's name property.

  mysheet.name = "My Sheet"

4a.  How can I autofit using code?

  Use the autofit method of the range object.

  mysheet.column(1).autofit

4b.  Can I bold rows 1 and 2?

  Use the bold property of the Range's font object

  mysheet.rows(1).font.bold = true

5.  Goodluck.  IE likes to display anything that it can figure out.  So if you save it with an .xls extension, IE will try to embed it and open it with excel.  I think most people get around it by using zip files -- or they explicitly post info on the link telling users to right-click the link and choose the save target as... function.
0
 

Author Comment

by:kpetti
ID: 2744017
Hi clockwatcher,

I am having trouble using the move method in my ASP page.  I keep getting an error:

Microsoft VBScript compilation error '800a0400'

Expected statement

/NetPro/ResultsExcel4-21-2000.asp, line 250

xlWorkSheets("Sheet1").Move after:=xlWorkSheets("Sheet3")
----------------------------------^


xlWorkSheets is a variable I created using the following code:

Dim xlWorkSheets

Set xlWorksheets = objXLApplication.Worksheets

As you know everything in .asp is type variant so I could not explicitly Dim the variable as Excel.WorkSheets

Any ideas why I am getting this error?  Can you not use := in asp?  Are there any other alternatives?

Thanks,

kpetti

0
 
LVL 25

Expert Comment

by:clockwatcher
ID: 2744769
VBScript doesn't support named parameters.

Instead of this:

xlWorkSheets("Sheet1").Move after:=xlWorkSheets("Sheet3")

Use this-- just leave the first parameter out; it's optional.

xlWorkSheets("Sheet1").Move ,xlWorkSheets("Sheet3")
0
 

Author Comment

by:kpetti
ID: 2749177
I think I might have over-estimated the complexity of this question.  Enjoy the points...

kpetti
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
If-Then-Else ASP problem 6 78
Button to go back 3 44
Sudden decrease in performance when updating mysql using classic asp 6 40
JSON Error in ASP Page 3 38
I have helped a lot of people on EE with their coding sources and have enjoyed near about every minute of it. Sometimes it can get a little tedious but it is always a challenge and the one thing that I always say is:   The Exchange of informatio…
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…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

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