Write to Excel using ADO from ASP

Posted on 2004-10-28
Last Modified: 2011-04-14
Hi All

I am trying to export data from my asp page using ADO to an Excel worksheet. This is what i have so far



Dim sql
Dim Rs

set conn=Server.CreateObject("ADODB.Connection") = "Driver={Sybase System 11};Srvr=DEV-DATABASE;DB=AAAA_0500;UID=username;PWD=PASSWORD"

If Conn.State = 0 Then

response.write "Connection Failed"

End If

conn.cursorlocation = 3

sql = "select Study_site_no,Study_pat_id,visit from report"

   Set Rs = Server.CreateObject("ADODB.Recordset")

      Rs.CursorType = adOpenDynamic
      Rs.CursorLocation = 3
      Rs.Open sql,Conn,3,1

Response.ContentType = "application/"

Response.AddHeader "Content-Disposition", "attachment; filename=Enquiry_List.xls"

if Rs.eof <> true then

response.write "<table border=1>"

while not Rs.eof

response.write "<tr><td>" & Rs.fields("ID") & "</td><td>" &

Rs.fields("Study_site") & "</td><td>" & Rs.fields("Study_Pat") & "</td><td>" & Rs.fields("Visit") &

response.write "</table>"
end if
set rs=nothing

I am getting a compilation error at response.write "<tr><td>" & Rs.fields("ID") & "</td><td>" &

Any help is highly appreciated..

If this code is wrong and if there are some good examples it would be great.

Question by:sramkris
    LVL 1

    Expert Comment

    You can't end a line with '&'

    if this line is supposed to continue onto the next line then put an '_' on the end, and get rid of the blank lines.

    response.write "<tr><td>" & Rs.fields("ID") & "</td><td>" & _
    Rs.fields("Study_site") & "</td><td>" & Rs.fields("Study_Pat") & "</td><td>" & Rs.fields("Visit") & _

    all that text is now effectively on the same line
    LVL 1

    Author Comment


    I changed my code to what you had but i get an erroe expected end of statement

    response.write "<tr><td>" & Rs.fields("Study_Site") & "</td><td>" Rs.fields("Study_Pat") "</td></tr>"

    Not sure what is wrong here

    LVL 1

    Accepted Solution


    mmm not sure how you got that line

    anyway if you look at the line in your first post which is fine;

    Rs.fields("Study_site") & "</td><td>" & Rs.fields("Study_Pat") & "</td><td>" & Rs.fields("Visit") &

    and this line you just posted
     "<tr><td>" & Rs.fields("Study_Site") & "</td><td>" Rs.fields("Study_Pat") "</td></tr>"

    where are the '&' symbols between on either side of the  Rs.fields("Study_Pat") ???
    "<tr><td>" & Rs.fields("Study_Site") & "</td><td>" & Rs.fields("Study_Pat") & "</td></tr>"
                                                                                ^                                  ^

    Its quite OK for all this to be on the same line, like so;

    response.write "<tr><td>" & Rs.fields("ID") & "</td><td>" & Rs.fields("Study_site") & "</td><td>" & Rs.fields("Study_Pat") & "</td><td>" & Rs.fields("Visit") & "</td></tr>"

    but it appeared that you had separated them out into three lines, thats fine just so long as the continuation is on the immediate next line and a '_' (underscore) is used at the end, to indicate  continue on to the next line

    As shown below this is all taken as one line
    response.write "<tr><td>" & Rs.fields("ID") & "</td><td>" & Rs.fields("Study_site") _
    & "</td><td>" & Rs.fields("Study_Pat") & "</td><td>" & Rs.fields("Visit") & "</td></tr>"

    remove the '_' from the end of the first line and it will be taken as two separate lines.

    LVL 1

    Author Comment

    Hi smoken

    I will accpet this as the answer but i just wanted to ask one more thing. If its possible can you tell me how do i get the column name to print before i print the data.
    in the excel sheet.

    Thanks a lot

    LVL 1

    Expert Comment

    Well anything that goes into your html table will be exported to the excel spreedsheet, so you just have to put the Column Names as the first row in your table e.g.
    response.write "<tr><td>Column1</td><td>Column2</td><td>Column3</td></tr>......etc"
    if you use <th> then the headers will be Bold and Centered in Excel.
    See line 2 below on how it would appear in your code.
    simple as that

    response.write "<table border=1>"
    response.write "<tr><th>Column1</th><th>Column2</th><th>Column3</th></tr>"
    while not Rs.eof
    response.write "<tr><td>" & Rs.fields(0) & "</td><td>" & _
    Rs.fields(1) & "</td><td>" & Rs.fields(2) & "</td><td>" & Rs.fields(3) & _

    LVL 1

    Author Comment

    Hi s_moken

    I have posted another question an extension of the same problem could you please look in to that and help me out.


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Course: From Zero to Hero with Nodejs & MongoDB

    Interested in Node.js, but don't know where to start or how to learn it properly? Confused about how the MEAN stack pieces of MongoDB, Expressjs, Angularjs, and Nodejs fit together? Or how it's even possible to run JavaScript outside of the browser?

    Suggested Solutions

    Title # Comments Views Activity
    Remove Session 3 32
    Currency formatting 5 52
    post data with a link 8 41
    how to hide hopscotch tour popup page reload? 2 27
    Hello, all! I just recently started using Microsoft's IIS 7.5 within Windows 7, as I just downloaded and installed the 90 day trial of Windows 7. (Got to love Microsoft for allowing 90 days) The main reason for downloading and testing Windows 7 is t…
    This demonstration started out as a follow up to some recently posted questions on the subject of logging in: and…
    To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…
    This video is in connection to the article "The case of a missing mobile phone (". It will help one to understand clearly the steps to track a lost android phone.

    856 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

    Need Help in Real-Time?

    Connect with top rated Experts

    9 Experts available now in Live!

    Get 1:1 Help Now