Exporting Crystal to Excel using Access VBA (1 Viewer)

[mccoydo]

Hi, have searched both VBA & crystal forums but can't find an answer to this. Here goes anyway...

I've been given a set of crystal reports that I need to export to excel. I can do so individually using Crystal Reports Viewer but this is time consuming for multiple reports.

I'm trying to use Access 2013 VBA to do this. I think I have downloaded the correct VBA reference (craxdrt.dll). Code I'm using is below. Do you think I'm on the right track?

Note that I do not have the option to have the original reports exported in Excel format and that I cannot connect to Crystal itself. It is just the report outputs that I've access to.

Thanks in advance for your help.

'sDirectory, sFile & sPath aren't declared here to save space...

 Private Sub ExportFile(sPath As String)
DoCmd.SetWarnings False
 
Dim CR As CRAXDRT.Application
Dim rep As CRAXDRT.report
Set CR = New CRAXDRT.Application
Set rep = CR.OpenReport(sPath)
 
Dim sFile As String
Dim sDirectory As String
 
sDirectory = fGetDirectory(sPath)
sFile = fGetFileName(sPath)
 
 
sFile = Left(fGetFileName(sPath), Len(fGetFileName(sPath)) - 4) & ".xlsx"
 
rep.SaveAs fGetDirectory(sPath) & sFile
rep.Close False
Set rep = Nothing
 
Debug.Print "created file " & sFile
 
CR.Quit
Set CR = Nothing
 
DoCmd.SetWarnings True
 
End Sub

 

[Rx_]

Took some time to search around on this. Will suggest the odds are against you being successful. Would like to be proven wrong.
One place claims to migreat them into PDF, but never reports back the next step to Excel from PDF was successful.
If you make any progress or get some files converted into some other format, let us know.
Once the Reference is set in the Code module (Tools References) for the DLL, it would be interesting to choose the F2 Object Browser and see what properties, methods, and events the DLL offers to VBA.