Export to Excel
acCmdOutPutToExcel
This procedure accepts the name and the type of an object and exports it to Excel.
'***************** Code Start *******************
'This code was originally written by Terry Wickenden.
'It is not to be altered or distributed,
'except as part of an application.
'You are free to use it in any application,
'provided the copyright notice is left unchanged.
Sub ExcelWizard(strName As String, Optional strItem As String = "Report")
' Expects the name of the item to be exported = strName
' Also looks for type of object to be exported = strItem
' Defaults to Report - other valid entries are Form, Table, Query
' Note:- These are all case sensitive
Dim intType As Integer
Dim strMsg As String
On Error GoTo ErrExcelWizard
Select Case strItem
Case "Report"
intType = acReport
Case "Query"
intType = acQuery
Case "Form"
intType = acForm
Case "Table"
intType = acTable
Case Else
MsgBox "Invalid object type", vbCritical, "Entry Error"
Exit Sub
End Select
DoCmd.SelectObject intType, strName, True
DoCmd.RunCommand acCmdOutputToExcel
Exit Sub
ErrExcelWizard:
Select Case Err
Case 2544
' Invalid object name
strMsg = "There is no " & strItem & " called " & strName & "."
MsgBox strMsg, vbCritical, "Entry Error"
Exit Sub
Case Else
MsgBox Err & vbCrLf & vbCrLf & Err.Description, vbCritical, "Error Message"
Exit Sub
End Select
End Sub
'****************** Code End ********************
