Solved

Exporting ASP Reports to Excel Format

Posted on 2000-04-17
5
320 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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Hello, all! I just recently started using Microsoft's IIS 7.5 within Windows 7, as I just downloaded and installed the 90 day trial of Windows 7. (Got to love Microsoft for allowing 90 days) The main reason for downloading and testing Windows 7 is t…
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…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

635 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