Results 1 to 4 of 4

Thread: How to export data from acess to excel

  1. #1
    Join Date
    Apr 2009
    Posts
    24

    How to export data from acess to excel

    I am given a project from my company & have finished completing the project. I have made the project in access & now my boss want all the table from access to excel. I have got frustrated with it that because it will be wasting of time copying all the data to excel. Is there any feature available to access which i don't know on how to export data from access to excel.....!

    Is there anyone who can provide me the solution.....???

    Please Help...!

  2. #2
    Join Date
    Apr 2008
    Posts
    4,642

    Re: How to export data from acess to excel

    Export Access data to Excel

    Often, you will come across situations where your data is stored in Access, but you need to move or copy it to Excel. For example, you might want to distribute your report to a group of users who prefer to view it in Excel. Or, you might want to analyze your Access data by taking advantage of the analysis features of Excel. In such situations, export the contents of your Access database to an Excel worksheet.

    You can export the following to Excel:

    • The data in a table, query, form, or report.
      • The following illustration shows how the Catalog report in the Northwind
      • Traders sample database will look after it has been exported to Excel:



    • All or some of the rows and columns in a datasheet view
      • The following illustration shows how a portion of the Employee table in datasheet view will look after it has been exported to Excel:



    Getting started with the export operation :

    1. Identify the data that you want to export
    2. Decide where to start the export operation
    3. Identify the destination file for the export operation
    4. Things you should know before exporting certain data types and controls
    5. Start the export operation
    6. Review the Excel worksheet

  3. #3
    Join Date
    May 2008
    Posts
    4,831

    Re: How to export data from acess to excel

    Follow the following steps to export data to excel :

    1. Click on the query of data you want to export
    2. Go to "File" menu, click "Save As/Export"
    3. Select "To an External File or Database"
    4. Choose a location for your Excel spreadsheet.


    You now have an Excel spreadsheet containing the data from your query that you can email to anyone you like.

  4. #4
    Join Date
    Apr 2008
    Posts
    4,088

    Re: How to export data from acess to excel

    the following procedure can import data from an Access table to a worksheet.

    Sub ADOImportFromAccessTable(DBFullName As String, _
    TableName As String, TargetRange As Range)
    ' Example: ADOImportFromAccessTable "C:\FolderName\DataBaseName.mdb", _
    "TableName", Range("C1")
    Dim cn As ADODB.Connection, rs As ADODB.Recordset, intColIndex As Integer
    Set TargetRange = TargetRange.Cells(1, 1)
    ' open the database
    Set cn = New ADODB.Connection
    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _
    DBFullName & ";"
    Set rs = New ADODB.Recordset
    With rs
    ' open the recordset
    .Open TableName, cn, adOpenStatic, adLockOptimistic, adCmdTable
    ' all records
    '.Open "SELECT * FROM " & TableName & _
    " WHERE [FieldName] = 'MyCriteria'", cn, adCmdText
    ' filter records

    RS2WS rs, TargetRange ' write data from the recordset to the worksheet

    ' ' optional approach for Excel 2007 or later (RS2WS is not necessary)
    ' For intColIndex = 0 To rs.Fields.Count - 1 ' the field names
    ' TargetRange.Offset(0, intColIndex).Value = rs.Fields(intColIndex).Name
    ' Next
    ' TargetRange.Offset(1, 0).CopyFromRecordset rs ' the recordset data

    End With
    rs.Close
    Set rs = Nothing
    cn.Close
    Set cn = Nothing
    End Sub
    The macro examples assumes that your VBA project has added a reference to the ADO object library. You can do this from within the VBE by selecting the menu Tools, References and selecting Microsoft ActiveX Data Objects x.x Object Library. Use ADO if you can choose between ADO and DAO for data import or export.

Similar Threads

  1. How to export data from tally to excel
    By Amitesh in forum Windows Software
    Replies: 5
    Last Post: 19-02-2011, 03:35 PM
  2. Export excel data to Word 2007
    By Uzair in forum Windows Software
    Replies: 5
    Last Post: 25-02-2010, 06:31 PM
  3. Export data from excel spreadsheet to an Oracle Table
    By Kurtz in forum Windows Software
    Replies: 2
    Last Post: 13-05-2009, 01:11 PM
  4. export data in the bd Mysql to the Excel file
    By Actionguy in forum Software Development
    Replies: 6
    Last Post: 02-03-2009, 07:23 PM
  5. Export Project Data to Excel
    By Asgar in forum Microsoft Project
    Replies: 3
    Last Post: 20-06-2007, 07:56 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Page generated in 1,711,624,167.85931 seconds with 17 queries