Poor performance exporting Crystal Reports from VBA ???

1005 Views

Hi all,

When I export reports from within Crystal Design to Excel the time it takes to complete the export if fine.

But when I export the same reports from within VBA (Ms-Access 2003) the time it takes to export one report can take up to a few hours. Here's the function I wrote for this.....

*Note that this function executes from an Ms-Access module.

Can someone have a look at the code below and tell me if I'm missing anything to improve performance or is this the nature of exporting through VBA ?

__________________

Public Function RunCrystalReport(strReportPath As String, intFormatType As Integer, strReportFileName As String) as boolean

On Error GoTo ErrorHdl

Dim appCR As CRAXDDRT.Application

Dim cryReport As CRAXDDRT.Report

RunCrystalReport = False

Set appCR = New CRAXDDRT.Application

Set cryReport = appCR.OpenReport(strReportPath)

cryReport.Database.LogOnServer "oracle.dll", "pdor43", "pdor43", "admin", "password"

cryReport.ExportOptions.DiskFileName = strReportFileName

cryReport.ExportOptions.DestinationType = crEDTDiskFile

cryReport.ExportOptions.FormatType = intFormatType

cryReport.ExportOptions.ExcelMaintainRelativeObjectPosition = True

cryReport.ExportOptions.ExcelMaintainColumnAlignment = True

cryReport.ExportOptions.ExcelAreaType = crPageHeader

cryReport.ExportOptions.ExcelExportAllPages = True

cryReport.ExportOptions.ExcelUseConstantColumnWidth = False

cryReport.ReadRecords

cryReport.Export False

Set cryReport = Nothing

Set appCR = Nothing

RunCrystalReport = True

Exit Function

ErrorHdl:

MsgBox Err.Description & " in RunCrystalReport()", vbCritical

End Function

______________

thanks,

Richard