DYNAMIC EXCEL WORKSHEET NAME??

tbaquero · September 27th, 2004, 10:32 AM

I need to name an excel worksheet dynamically. I have a recordset that gets its values from an SQL Server.

I already put the <%Response.ContentType="application/vnd.ms-excel"%> and the <%Response.AddHeader "Content-Disposition","filename="&fname&".xls"%> tags. I'm getting the excel file named as I want, but the worksheet is getting the same name as the file name.

How do i name the worksheet "myname" instead of "Sheet1" or the file name??? Can this be done simply, without having to use ActiveX or something more complex???

Please help me!

TANIA P. BAQUERO
Panama Canal Authority
Student

happygv · September 30th, 2004, 10:17 AM

How do you populate the the rows from SQL server to EXCEL? Can you post code related to that, to find what you are missing?

_________________________
- Vijay G
Strive for Perfection

tbaquero · October 5th, 2004, 08:30 AM

I have a table in the sql server with a reportID and the name of the stored procedure that has to be executed according to that id. It also has the report name and other details. When the user clicks on a report, that page sends via querystring the report id (repid). This is the content of the page that receives the querystring:

<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<% on error resume next %>

<% Response.Buffer = TRUE
    response.ContentType="application/vnd.ms-excel"
     repid=request.QueryString("repid")
    txtvalue=request.QueryString("txtvalue")
    Set rs = Server.CreateObject("ADODB.Recordset")
    rs.Open "SELECT rep_name from reportes where rep_id="&repid, connstr, 3, 3 %>

<% fname=rs("rep_name")
    Response.AddHeader "Content-Disposition", "attachment;filename="&fname&".xls"%>

----------------------------------------
Here's the <<exportreport.asp>> content:

<% dim txtvalue
    txtvalue=request.QueryString("txtvalue")%>
<% dim orderby
    orderby=request.QueryString("orderby") %>
<% dim repid
    repid=request.QueryString("repid") %>
<% if repid="" then
        repid="0"
    end if %>

<% sqlstr2="select * from reportes where rep_id="&repid%>

<% dim var_sp_name, rep_name, sort_str, sp_body
    var_sp_name = rs2("sp_name")
    rep_name = rs2("rep_name") %>

<% sqlstr="EXEC " &var_sp_name& " '" &txtvalue& "'"%>

<html>
<body>
<table width="<%=180*rs1.fields.count+200%>" border="1" bordercolor="cccccc" cellpadding="0" cellspacing="0">
<tr>
<% for i=0 to rs1.fields.count-1 %>
<td style="background-color:#999999; color:#000000; font-weight:bold"><%=rs1.fields(i).name%></td>
<% next %>
</tr>
<% linecount=1%>
<% while not rs1.eof %>
    <tr>
    <% for j=0 to rs1.fields.count-1 %>
        <td><% if rs1.fields.item(j).value = "" or rs1.fields.item(j).value=NULL then %>
                     
            <% else
                    response.Write(rs1.fields.item(j).value)
                end if %>
        </td>
    <% next %>
<% linecount=linecount+1%>
<% rs1.movenext%>
<% wend %>
</tr>
</table>
</body>
</html>

TANIA P. BAQUERO
Panama Canal Authority
Student