Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Exporting ASP Reports to Excel Format

Posted on 2000-04-17
5
Medium Priority
?
322 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
  • 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 1200 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

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…
This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…

971 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