Go Back   TechArena Community > Software > Windows Software
Become a Member!
Forgot your username/password?
Register Tags Active Topics RSS Search Mark Forums Read

Sponsored Links



How to export data from acess to excel

Windows Software


Reply
 
Thread Tools Search this Thread
  #1  
Old 13-05-2009
Member
 
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...!

Reply With Quote
  #2  
Old 13-05-2009
Member
 
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
Reply With Quote
  #3  
Old 13-05-2009
Member
 
Join Date: May 2008
Posts: 4,829
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.
Reply With Quote
  #4  
Old 13-05-2009
Member
 
Join Date: Apr 2008
Posts: 4,097
Re: How to export data from acess to excel

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

Quote:
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.
Reply With Quote
Reply

  TechArena Community > Software > Windows Software
Tags: , , , ,



Thread Tools Search this Thread
Search this Thread:

Advanced Search


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


All times are GMT +5.5. The time now is 02:08 PM.