Export datas to a Excel file using query without openrowset method

**shalabh6** · Nov 24 '09, 07:13 AM

Originally posted by baburmm

Hi all,
Can any one tell me,
Export datas to a Excel file using query without openrowset method

is there any other methods are posible

for creating an Excel file to export it

Thanks,
Babu.K

Try the code below, it works

<%@ Language=VBScri pt %>
<%
'Change HTML header to specify Excel's MIME content type
Response.Buffer = TRUE
Response.Conten tType = "applicatio n/vnd.ms-excel"

%>
<HTML>
<BODY>
<p>
<%
' Create ADO Connection object
dim myConnection
Dim I
Dim myRecordset
Dim sqlStr
set myConnection = Server.CreateOb ject("ADODB.Con nection")
set myRecordset = Server.CreateOb ject("ADODB.Rec ordset")
'Open SQL Server Pubs database...
myConnection.Op en "DSN=myweb;uid= abc;password=ab c;Database=dbna me"

'Get a recordset of info from Authors table...
sqlStr = "SELECT * from SUPPLIER"' where empm_empid = 'E0123'"
'sqlstr =Request.QueryS tring("qry")
'sqlstr=session ("excelqry")

'response.write sqlstr
'response.end
I=1
set rsAuthors = myConnection.Ex ecute(sqlStr)
%>


<TABLE WIDTH=75% BORDER=1 CELLSPACING=1 CELLPADDING=1>

<% do while not rsAuthors.EOF %>

<% if I=1 then %>
<TR>
<% I=I+1 %>
<% For Each fld In rsAuthors.Field s %>
<TD><%=fld.na me %></TD>
<% next %>
</TR>
<% end if %>

<TR>

<% For Each fld In rsAuthors.Field s %>
<TD><%=fld.valu e %></TD>
<% next %>
</TR>
<% rsAuthors.MoveN ext
loop
' Clean up
rsAuthors.Close
set rsAuthors = Nothing
myConnection.Cl ose
set myConnection = Nothing
%>
</TABLE>
</BODY>
</HTML>

Export datas to a Excel file using query without openrowset method

Export datas to a Excel file using query without openrowset method

Comment