Access To Crystal To Excel - Automation ??

[mordja]

Hi,

Im looking to automate the process of creating a crystal report from an access table and then exporting it to excel. I want it to be automated in the sense, that the user will click a button in access, all this will go on behind the scenes and create the appropriate excel file in the appropriate directory. Any suggestions ??

Thanks

 

[scclem]

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