Printable View
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...!
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 :
- Identify the data that you want to export
- Decide where to start the export operation
- Identify the destination file for the export operation
- Things you should know before exporting certain data types and controls
- Start the export operation
- Review the Excel worksheet
Follow the following steps to export data to excel :
- Click on the query of data you want to export
- Go to "File" menu, click "Save As/Export"
- Select "To an External File or Database"
- 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.
the following procedure can import data from an Access table to a worksheet.
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.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