Export to Excel

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 previewed = strName
'Also looks for type of object to be previewed = 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 ********************

Return to Example List