Here's a function that I use for this purpose. This is written for Crystal v8.5 and works great in Access 2000. You'll need references set for the Crystal Reports 8.5 ActiveX Designer Runtime Library (craxdrt.dll) and Crystal Report Export (sviewhlp.dll).
Also, appropriate error trapping and exiting sections should be added.
Function Crstl_Exprt_Excel(<can put parameters in to accept values to be used in function>)
Dim Application As New CRAXDRT.Application
Dim CR_Report As CRAXDRT.Report
Dim CRXExport As CRAXDRT.ExportOptions
Dim CRXParamDefs As CRAXDRT.ParameterFieldDefinitions
Dim CRXParamDef As CRAXDRT.ParameterFieldDefinition
Set CR_Report = Application.OpenReport(<full path and name of Crystal report file goes here>)
Set CRXExport = CR_Report.ExportOptions
CR_Report.DiscardSavedData
With CRXExport
.FormatType = crEFTExcel80
.DiskFileName = sOutputName ' this variable contains full path and file name of Excel file being created
.DestinationType = crEDTDiskFile
.UseReportDateFormat = True
.UseReportNumberFormat = True
End With
'If you have parameters, you'll need this section. Values can be passed to function as parameter, constants
'of global variables. Use as many Case groups as number of parameters in report.
Set CRXParamDefs = CR_Report.ParameterFields
For Each CRXParamDef In CRXParamDefs
With CRXParamDef
CR_Report.ParameterFields(1).EnableMultipleValues = False
Select Case .ParameterFieldName
Case "<Parameter1 Name>"
.ClearCurrentValueAndRange
.AddCurrentValue <parameter value>
Case "<Parameter2 Name>" .ClearCurrentValueAndRange
.AddCurrentValue <parameter value>
Case "<Parameter3 Name>"
.ClearCurrentValueAndRange
.AddCurrentValue <parameter value>
End Select
End With
Next
'If you're going to print the report, use a boolean variable to determine printing or exporting at this point.
If booPrint = True Then
CR_Report.PrintOut False, 1
Else
CR_Report.EnableParameterPrompting = False
CR_Report.Export False
End If
End Function
Hope this helps,
Scott