Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    New Coder
    Join Date
    Nov 2003
    Location
    Canada
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Exporting access database to Excel spreadsheet

    Hi, guys.

    Good to see you again.
    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
    New Coder
    Join Date
    Nov 2003
    Location
    Canada
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Please help me guys~

  • #3
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    This code is supposed to write data from access database to excel spreadsheet.
    But it doesn't work. What should I do?
    Well, what does it do ?

    Anyway, if you need a quick answer, run a search. This thread contains multiple codes to create xls-files.

    http://www.codingforums.com/showthre...ighlight=excel

    In your case, you would just open the recordset and file, and then inside the while loop, write each record as a line to the file and save the file as an xls.

  • #4
    New Coder
    Join Date
    Nov 2003
    Location
    Canada
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Well, what does it do ?
    It doesn't write database data to excel file.
    excelface.xls

  • #5
    New Coder
    Join Date
    Nov 2003
    Location
    Canada
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Please help me, guys.

  • #6
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Euh. What extra help do you need ?

    The link in my previous psot co=tains all the code you need (multiple versions). Check out whammy's code there (or below)
    The part in bold need to be replaced by your own fileadress or sql or recordsetcode.

    the whammy-code
    Code:
    <%
    Dim fs, f, thefile, Conn, dbquery, rs, linevalue, LeadColumns
    thefile = Server.MapPath("yourfilename.csv")
    Set fs = Server.CreateObject("Scripting.FileSystemObject")
    Set f = fs.OpenTextFile(thefile, 2, True)
    Set Conn = Server.CreateObject("ADODB.Connection")
    Conn.Open 'Put your connection string here
    Set rs = Server.CreateObject("ADODB.Recordset")
    
    dbquery = "SELECT * FROM tablename ORDER BY whatever"
    Set rs = Conn.Execute(dbquery)
    
    For Each Item in rs.fields
       LeadColumns = LeadColumns  & """" &  Replace(Item.Name,"""","")  & ""","
    Next
    LeadColumns = Left(LeadColumns,Len(LeadColumns)-1)
    f.WriteLine(LeadColumns)
    
    Do While NOT rs.EOF
       linevalue = "" 'reset linevalue to empty
       For Each Item in rs.fields
          linevalue = linevalue & """" & Replace(Item.Value,"""","") & ""","
       Next
       linevalue = Left(linevalue,Len(linevalue)-1)
       f.WriteLine(linevalue)
       rs.MoveNext
    Loop
    
    Set rs = Nothing
    Conn.Close
    Set Conn = Nothing
    
    Set f = Nothing
    Set fs = Nothing
    %>
    You need to ask more specific questions if you want quick and concrete help.
    Like, for instance, what the problem is : do you get an error, dus the script timeout, ... etc

  • #7
    New Coder
    Join Date
    Nov 2003
    Location
    Canada
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thank you, Raf.
    It works.
    But how can I align items written in excel file to the column?

    Is there any way to change the code above to align like that when it writes the data in excel file?
    [

  • #8
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    What do you mean by 'align' ?

  • #9
    Senior Coder
    Join Date
    Jun 2002
    Location
    41° 8' 52" N -95° 53' 31" W
    Posts
    3,660
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Squall, regarding your "Please help me, guys" messages... first of all you have to have a little patience. I've seen you post that message within an hour or two of posting your original question

    Everyone here volunteers their time to help others, and most of us have full time jobs as programmers, so if your question doesn't get answered in a couple of hours, then you must realize we're probably way too busy to visit the forums. Your question (unless totally ambiguous) will probably get answered, because we all love to help!

    Also please make sure you have fully tested something before you say it doesn't work, or at least provide the code you've used to test, and any error messages.
    Last edited by whammy; 11-26-2003 at 01:51 AM.
    Former ASP Forum Moderator - I'm back!

    If you can teach yourself how to learn, you can learn anything. ;)

  • #10
    Senior Coder
    Join Date
    Jun 2002
    Location
    41° 8' 52" N -95° 53' 31" W
    Posts
    3,660
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Regarding that .csv code that I have used above, which raf posted, it's just a comma-delimited text file, which happens to work well in excel. It doesn't use "excel writer", but as long as your column headers and data match up, everything should align just fine.
    Former ASP Forum Moderator - I'm back!

    If you can teach yourself how to learn, you can learn anything. ;)

  • #11
    New Coder
    Join Date
    Nov 2003
    Location
    Canada
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Squall, regarding your "Please help me, guys" messages... first of all you have to have a little patience. I've seen you post that message within an hour or two of posting your original question
    I really appreciate whenever you guys help me.
    I guess I was too enthusiastic.

    What do you mean by 'align' ?
    Raf, I mean when asp code export data from database to excel file, items contents kinda become written stick together not being separated by columns.

  • #12
    New Coder
    Join Date
    Nov 2003
    Location
    Canada
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Right now code is writing everything in one column.
    For example, there are fields ID, Category, Description, Doc_ID in Access database.
    There are columns A,B, C ,D E,....so on in Excel datasheet.
    Right now code is putting everything into one column A.
    I wanted to write field ID to column A, Category to column B, and so on.

  • #13
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    did you save the file with extension csv ? not extension xls !!

    Your file will open in excell if you save it as file blablabla.cvs

    cvs stands for domma seperated value. but if you save it with an xls extension, it could look like 'valueID', 'valueCategory', 'valueDescription', 'valueDoc_ID'
    --> all stick to eachother whith a , in it.

    The csv extension will tell excell to 'split the value by the ',' seperator'

  • #14
    New Coder
    Join Date
    Nov 2003
    Location
    Canada
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thank you very much.
    It works. Yeah~

  • #15
    New Coder
    Join Date
    Nov 2003
    Location
    Canada
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi, guys.
    Sorry to bug you again.
    mm.... Project specification says I was required to write data into .xls file instead of .csv
    I had been going wrong way.

    Please take a look at following code.

    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>
    As you can see, what I am trying to do is writing data into excel .xls file. Code is not working. So is there any way to do this way?


  •  
    Page 1 of 2 12 LastLast

    Posting Permissions

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