Automating transaction export using standard Crystal reports

Question

Hi, 
 
 I was asked to create a custom service that will do the following: 
 1. Get Purchase Orders based on specific criteria 
 2. Export each Purchase Order using the standard Crystal Report file to a PDF document 
 
 Getting the purchase orders is easy enough to do, but how do I then send this data off to the Crystal report and export to PDF. It must be the Crystal report that was set up for this company as it has their approved look and feel already done. 
 I have looked at using Crystal Decisions in my code, but the report uses the POPORN.dll file as a data source and I am not entirely sure how to use this dll in code. 
 Another option would be to use the Accpac COM API, but there does not seem to be a lot of information or examples on using this. 
 Can anyone please point me in the right direction?

Shane - Ascend Business Solutions · Accepted Answer

Here's some sample code that should get you moving in the right direction should you want to explore the COM API_Note - teh SQL calls are direct. You can instead use view CS0120 (see sample below): 
 Private Sub cmdMakePDFs_Click() 
 Dim strRecords As Integer Dim strYear As String Dim strSQL As String Dim strWhere As String Dim strVendName As String Dim strVendorID As String Dim strRecNumber As Integer 
 Dim rpt As AccpacCOMAPI.AccpacReport Set rpt = mAS.ReportSelect("APCPRST4A[APCPRST4A.RPT]", " ", " ") 
 Dim rptPrintSetup As AccpacCOMAPI.AccpacPrintSetup Set rptPrintSetup = mAS.GetPrintSetup(" ", " ") 
 rptPrintSetup.DeviceName = "" rptPrintSetup.OutputName = "USB001" rptPrintSetup.Orientation = 1 rptPrintSetup.PaperSize = 1 rptPrintSetup.PaperSource = 7 rpt.PrinterSetup rptPrintSetup 
 '--------------------------------------------------- ' Loop through table and make a pdf for each record '--------------------------------------------------- 
 Set cnCBINDA = New ADODB.Connection 
 cnCBINDA.Open strSQLConCBINDA 
 strYear = Me.txtYear 
 strSQL = "SELECT * FROM APCCS_YEAR" strWhere = " WHERE APCCS_YEAR.CNTYEAR = " & strYear & "" 
 Set rsCBINDA = New ADODB.Recordset 
 rsCBINDA.Open strSQL & strWhere & "", cnCBINDA, adOpenKeyset, adLockReadOnly strRecords = rsCBINDA.RecordCount 
 strRecNumber = 0 
 Do While rsCBINDA.EOF = False strVendName = UCase(Trim(rsCBINDA.Fields("VENDNAME"))) strVendorID = Trim(rsCBINDA.Fields("VENDORID")) 
 strRecNumber = strRecNumber + 1 
 Me.Caption = "Processing " & strRecNumber & " of " & strRecords & " T4As" 
 '---------------------- ' New '---------------------- rpt.SetParam "YEAR", Me.txtYear ' Report parameter: 5 rpt.SetParam "FEDID", "106844053 RP0001" ' Report parameter: 5 rpt.SetParam "VENDORID", strVendorID ' Report parameter: 5 
 rpt.NumOfCopies = 1 rpt.Destination = AccpacCOMAPI.tagPrintDestinationEnum.PD_FILE rpt.Format = AccpacCOMAPI.tagPrintFormatEnum.PF_PDF rpt.PrintDir = Me.txtPath & "\" & strVendName & "_" & txtYear & ".pdf" rpt.PrintReport 
 rsCBINDA.MoveNext Loop 
 rsCBINDA.Close 
 MsgBox "T4A File creation complete", vbInformation, "Create PDF" 
 Me.Caption = "T4A file creation complete" 
 End Sub 
 
 CS0120 code: 
 Dim viewCSQRY2 As AccpacCOMAPI.AccpacView Dim viewCSQRY2Fields As AccpacCOMAPI.AccpacViewFields DBLnk.OpenView "CS0120", viewCSQRY2 Set viewCSQRY2Fields = viewCSQRY2.Fields 
 viewCSQRY2.Browse strSQLQuery, True viewCSQRY2.InternalSet (256) Do While viewCSQRY2.Fetch = True 
 Do Stuff Loop

Sage 300

Automating transaction export using standard Crystal reports

Top Replies

COMMUNITY

COMPANY

PARTNERS

SUPPORT & TRAINING