Creating New Excel Worksheets With Classic ASP

newbie5574 · September 28th, 2011, 03:23 PM

I'm trying to build an application that creates an Excel template to help standardize documents. I created a form that collects the data from the user and sends it to an asp page which created the spreadsheet and formats the submitted data.

The initial page is created fine but How can I add new worksheets to the XLS file?

My code is here:

<%

OPTION EXPLICIT

' Waits until all the asp is done before dumping the text to the browser
response.buffer = true

'Receiving values from Form, assign the values entered to variables
'Set a new variable for each new form field added

DIM newclient
newclient=Request.Form("newclienttxt") 'Define Variable For New Client Acronym

DIM tech
tech=Request.Form("techreptxt") 'Define Variable For Tech Support Field

DIM techph
techph=Request.Form("techphone") 'Define Variable For Tech Support Phone

DIM tsbk
tsbk=Request.Form("addrep1") 'Define Variable For Additional Tech Rep

DIM bkrepph
bkrepph=Request.Form("bkrepphone") 'Define Variable For Additional Tech Phone

Dim sqetxt
sqetxt=Request.Form("sqetxt")

DIM sqeph
sqeph=Request.Form("sqephone") 'Define Variable For SQE Phone

DIM dba
dba=Request.Form("dbatxt") 'Define Variable For DBA

DIM dbaph
dbaph=Request.Form("dbaphone") 'Define Variable For DBA Phone

DIM sitecontact
sitecontact=Request.Form("sitecontactstxt") 'Define Variable For Site Contact

DIM sitecontactph
sitecontactph=Request.Form("sitecontactphone")'Def ine Variable For Site Contact Phone

DIM siteprimaryemail
siteprimaryemail=Request.Form("siteprimaryemail") ' Define Variable For Primary Site Email Address

DIM alineo
alineo=Request.Form("alineo") 'Define Variable For Software

Dim cpweb
cpweb=Request.Form("cpweb") 'Define variable for CPWeb drop down menu

Dim iExchange
iExchange=Request.Form("iExchange") 'Define variable for iEchange drop down menu

Dim fso 'Define filesystem object

' Tells the browser to open Excel
Response.ContentType = "application/vnd.ms-excel"

%>

<html>
<head>
<title></title>

<style type="text/css">

.title {
FONT-SIZE: 15pt; COLOR: blue; FONT-FAMILY: arial; BACKGROUND-COLOR: #dddddd
}
.txt {
FONT-SIZE: 25pt; COLOR: black; FONT-FAMILY: tahoma
}

TD.maintxt {
FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: arial narrow
}

TD.subhead {
FONT-SIZE: 15pt; COLOR: black; FONT-FAMILY: arial narrow; font-weight:bold; BACKGROUND-COLOR: #dddddd;

}

</style>
</head>

<body>



' Check to see if any querystring varibles exist

if len (request.servervariables("query_string")) >1 then

%>

<b>Variables passed via QueryString</b><br>


<%

Dim variable_name
Dim variable_value

'Loop to find each querystring variable name and varible value

for each variable_name in request.querystring
variable_value=request.querystring(variable_name)

'If there is a querystring variable name and value inputted then write it also
response.write variable_name &" = "
response.write variable_value &"<br>" &chr(13)
next

' end for next loop for all querystrings
end if

' end check for querstring values

' Check to see if any form varibles exist
if len(request.form) > 0 then

%>

<p>

<b>Variables passed via Post</b><br>



<%
'Loop to find each variable name and varible value

for each variable_name in request.form
variable_value=request.form(variable_name)

'If there is a variable name and value inputted then write it also
response.write variable_name &" = "
response.write variable_value &" <br>" &chr(13)
next ' end for next loop for all
end if ' end check for form values
%>

</body>
</html>

mat41 · September 28th, 2011, 08:04 PM

A good question. Im not so sure you can call and write to a new sheet using this method. You are afterall just showing an HTML page inside excel, its not a proper xls file.

I think you are asking a bit much of 'out of the box classic ASP' and you will need a third party component such as this:

http://www.4guysfromrolla.com/webtech/051502-1.shtml

That said, if you find a way please post it.........Good luck

newbie5574 · September 29th, 2011, 10:07 AM

I saw that before but had some issues with it. I've googled to no end and I don't think I will find any easy FREE option for this.

Thanks for your reply!

mat41 · September 29th, 2011, 06:27 PM

As I said im very sure there are no 'out of the box' solutions for you. Surely you can change your plan/workflow to suit, otherwise you may have to overcome what ever your issues are with the component.

Good luck and sorry I could not assist...