Exporting ASP Reports to Excel Format

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
kpettiAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
clockwatcherConnect With a Mentor Commented:
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
 
TTomCommented:
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
 
kpettiAuthor Commented:
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
 
clockwatcherCommented:
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
 
kpettiAuthor Commented:
I think I might have over-estimated the complexity of this question.  Enjoy the points...

kpetti
0
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.

All Courses

From novice to tech pro — start learning today.