[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Exporting ASP Reports to Excel Format

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

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Question has a verified solution.

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

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…
There is a wide range of advantages associated with the use of ASP.NET. This is why this programming framework is used to create excellent enterprise-class websites, technologies, and web applications.
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…
Suggested Courses

591 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