Using Access VBA to convert Crystal Reports to Excel

Hi Guys, Am not sure whether this belongs here or in the VBA forum. Have searched both but can't find an answer. 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

Using Access VBA to convert Crystal Reports to Excel