Export multiple excel sheets

Forums

My Profile

Inbox

Address Book

My Subscription

My Forums

Export multiple excel sheets

Logged in as: Guest

arrSession:exec spGetSession 2,13,59678

	Active Users: There are 0 members and 0 guests.
	Users viewing this topic: none

Printable Version

All Forums >> [Welcome] >> Test Posting Messages >> Export multiple excel sheets
Do you like VisualBasicScript.com? Link to us and help spread the word about our forum. Thanks!

Page: [1]

Message

<< Older Topic Newer Topic >>

Export multiple excel sheets - 4/30/2008 7:28:45 AM

isaws

Posts: 2
Score: 0
Joined: 4/30/2008
Status: offline

Hi All, I am in a need to write a VB script which should accomplish the following task

1. Put record set data in mutiple excel sheets.
2. Whenever user click the export button, display Save as dialog box to save the multiple excel sheets data on a excel workbook on user local machine.
3. I do not want to store the excel sheet on server.

I have a script which does this, but for a single sheet. I can modify this script to read the data from a record set but it just works for single sheet. Please help for mutiple sheets.

<%@ LANGUAGE="VBSCRIPT" %>
<%
Option Explicit
' -- Display's data and sends it as Excel Spreadsheet if required
Randomize Timer
Select Case Request("e")
Case ""
 DisplayData
Case Else
 SendExcelData
End Select
Response.End

Sub DisplayData()
Dim i
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML>
<HEAD>
<TITLE>ASP Pro 10 Minute Solution Sample</TITLE>
</HEAD>
<BODY BGCOLOR="#FFFFFF">

Welcome: 
Got Data? Send it to your users as an Excel Spreadsheet from your ASP Page 

<table BORDER="1" CELLSPACING="0" WIDTH="100%">
<tr>
 <td COLSPAN="11" BGCOLOR="lightgrey">Activity Report</td>
</tr>
<tr>
 <th>Activity #</th>
 <td>Project #</td>
 <td>Priority</td>
 <td>Title</td>
 <td>Status</td>
 <td>Requested By</td>
 <td>Date Needed</td>
 <td>Plan End Date</td>
 <td>Release</td>
 <td ALIGN="left">Est. Effort Hrs</td>
 <td ALIGN="left">Act. Effort Hrs</td>
</tr>
<% For i = 1 to 10 %>
<tr>
 <td><%= GetRandomValue(0) %></td>
 <td><%= GetRandomValue(0) %></td>
 <td ALIGN="CENTER"><%= GetRandomValue(0) %></td>
 <td><%= GetRandomValue(0) %></td>
 <td ALIGN="CENTER"><%= GetRandomValue(0) %></td>
 <td><%= GetRandomValue(0) %></td>
 <td ALIGN="CENTER"><%= GetRandomValue(0) %></td>
 <td ALIGN="CENTER"><%= GetRandomValue(0) %></td>
 <td><%= GetRandomValue(0) %></td>
 <td ALIGN="RIGHT"><%= GetRandomValue(0) %></td>
 <td ALIGN="RIGHT"><%= GetRandomValue(0) %></td>
</tr>
<% Next %>
<tr>
 <td BGCOLOR="GRAY">Total</td>
 <td>   </td>
 <td>   </td>
 <td>   </td>
 <td>   </td>
 <td>   </td>
 <td>   </td>
 <td>   </td>
 <td>   </td>
 <td ALIGN="RIGHT"><%= GetRandomValue(0) %></td>
 <td ALIGN="RIGHT"><%= GetRandomValue(0) %></td>
</tr>
</table>
<%
%>

<A HREF="<%= Request.ServerVariables("SCRIPT_NAME") %>?e=1">Download this data as an Excel Spreadsheet</A> 
</BODY>
</HTML>
<%
End Sub
Sub SendExcelData()
Dim i, j
' -- will send directly as excel
 response.clear()
 'Response.AppendHeader "Content-Disposition","attachment; filename=", "Title"
response.ContentType="application/ms-excel"
' Response.OutputStream.Write arrFileData, 0, arrFileData.Length
Response.AddHeader "Content-Disposition", "filename=mydata.xls;"
response.write "Activity #,"
 response.write "Project #,"
 response.write "Priority,"
 response.write "Title,"
 response.write "Status,"
 response.write "Requested By,"
 response.write "Date Needed,"
 response.write "Plan End Date,"
 response.write "Release,"
 response.write "Est. Effort Hrs,"
 response.write "Act. Effort Hrs,"
response.write vbNewLine

For i = 1 to 10
For j = 1 to 11
 Response.Write GetRandomValue(0) & ","
Next
response.write vbNewLine
Next

Response.write "Total,,,,,,,,,"
Response.Write GetRandomValue(0) & ","
Response.Write GetRandomValue(0) & ","
response.write vbNewLine

End sub

' -- returns a random number
Function GetRandomValue(byval iType)
Select Case iType
Case 0 ' Integer Number
 ' -- return an int between 1 and 30000
 ' Int((upperbound - lowerbound + 1) * Rnd + lowerbound)
 GetRandomValue = Int((30000-1+1)* Rnd+ 1)
End Select
End Function
%>