Question

How to write a vba code for access table from access to excel sheet OR
How to write a vba code for export data from access to excel

Answer 1

How to write a vba code for access table from access to excel sheet OR How to write a vba code for export data from access to excel

Here the Answer of the above Question is:

'EXPORTS TABLE IN ACCESS DATABASE TO EXCEL

Dim objexcel As Excel.Application
Dim wbexcel As Excel.Workbook
Dim wbExists As Boolean
Set objexcel = CreateObject("excel.Application")
objexcel.Visible = True

On Error GoTo Openwb
wbExists = False
Set wbexcel = objexcel.Documents.Open("C:\Users\Ramesh\Desktop\Final copy MDC\ValidationReportNew\ValidationReport.xls")
wbExists = True

Openwb:

On Error GoTo 0
If Not wbExists Then
'Set wbexcel = objexcel.Workbooks.Add("C:\Users\Ramesh\Desktop\Final copy MDC\ValidationReportNew\ValidationReport.xls")
Set wbexcel = objexcel.ActiveWorkbook

'EXPORTS TABLE IN ACCESS DATABASE TO EXCEL
'REFERENCE TO DAO IS REQUIRED

Dim strExcelFile As String
Dim strWorksheet As String
Dim strDB As String
Dim strTable As String
'Dim DB As DAO.Database
Dim objDB As Database

'Change Based on your needs, or use
'as parameters to the sub

strExcelFile = "C:\Users\Ramesh\Desktop\Final copy MDC\ValidationReportNew\ValidationReport.xls"
strWorksheet = "WorkSheet1"
strDB = "C:\Users\Ramesh\Desktop\Final copy MDC\MDC Tool_Backup.accdb"
strTable = "1 SCORECARD ALL SCOPE ERRORS BY CAT"

Set objDB = OpenDatabase(strDB)


'If excel file already exists, you can delete it here
If Dir(strExcelFile) <> "" Then Kill strExcelFile

objDB.Execute _
"SELECT * INTO [Excel 8.0;DATABASE=" & strExcelFile & _
"].[" & strWorksheet & "] FROM " & "[" & strTable & "]"
objDB.Close
Set objDB = Nothing

End If