Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2003
    Location
    Canada
    Posts
    1

    Question Unanswered: Exporting access database to Excel spreadsheet

    Hi, guys.

    This is my first time. Please be gentle.
    Nice to meet you.
    I am trying to export data from access database to excel spreadsheet.

    Please take a look at following code.


    code:--------------------------------------------------------------------------------
    <HTML>
    <HEAD>
    </HEAD>
    <BODY LEFTMARGIN=0 MARGINWIDTH="0" MARGINHEIGHT="0">
    <a href="pricelist.asp"><FONT FACE=Arial,Helvetica SIZE=2><B>Price List</B></FONT></a>
    </BODY>
    </HTML>
    --------------------------------------------------------------------------------

    When I click link Price List, I go to following page.


    code:--------------------------------------------------------------------------------
    <%@ Language="VBScript" %>
    <% Option Explicit %>

    <%
    Dim objConnection
    Dim objRecords
    Dim objExcel
    Dim strQuery
    Dim i

    Set objConnection = Server.CreateObject("ADODB.Connection")
    objConnection.Open "DRIVER={Microsoft Access Driver (*.mdb)};DBQ= " & Server.MapPath("tech_re.mdb")

    Set objRecords = Server.CreateObject("ADODB.Recordset")
    strQuery = "SELECT * FROM tblFAQ"
    objRecords.Open strQuery, objConnection

    Set objExcel = Server.CreateObject("Excel.Application")
    objExcel.Workbooks.Open "DRIVER={Microsoft Excel Driver (*.xls)};DBQ= " & Server.MapPath("excelface.xls")

    'Defines the first row
    i = 3

    'Creates the column description
    objExcel.ActiveSheet.Range("A" & i).Value = "ID"
    objExcel.ActiveSheet.Range("B" & i).Value = "Category"
    objExcel.ActiveSheet.Range("C" & i).Value = "Description"
    objExcel.ActiveSheet.Range("C" & i).Value = "Document ID"
    i = i + 1

    'Fills columns for each recordset
    While not objRecords.EOF
    objExcel.ActiveSheet.Range("A" & i).Value = objRecords("ID")
    objExcel.ActiveSheet.Range("B" & i).Value = objRecords("Category")
    objExcel.ActiveSheet.Range("C" & i).Value = objRecords("Description")
    objExcel.ActiveSheet.Range("D" & i).Value = objRecords("Doc_ID")
    objRecords.MoveNext
    i = i + 1
    Wend

    'Saves file and close Excel
    objExcel.ActiveWorkbook.SaveAs("excelface (" & Date & ").xls")
    objExcel.ActiveWorkbook.Close
    objExcel.Workbooks.Close
    objExcel.Quit
    %>
    <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
    <html>
    <head>
    <title>Create a pricelist with ASP</title>
    </head>
    <body>
    Pricelist has been created successfully.
    </body>
    </html>
    --------------------------------------------------------------------------------


    This code is supposed to write data from access database to excel spreadsheet.
    But it doesn't work. What should I do?

  2. #2
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    You say it is not working,... what is it doing? Does it report any errors? Have you checked to see if you are getting records back from your database query?

  3. #3
    Join Date
    Jun 2003
    Location
    USA
    Posts
    1,032
    Or perhaps add the ability for the user to download a CSV file which can easily be opened in Excel.

    Puzzle 5 Not much different from other solutions by Manohar Kamath posted 1/12/99
    http://www.asp101.com/puzzles/puzzle5_s7.asp
    J. Paul Schmidt, Freelance Web and Database Developer
    www.Bullschmidt.com
    Access Database Sample, Web Database Sample, ASP Design Tips

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •