ACCPAC SQL Query to PDF

[Boris10]

Hi Everyone,

I have created a custom VB macro,that runs a query and displays results in flexgrid. Is there anyway to save a pdf file of the displayed grid.
I am trying to email these results to a specified recipient.

Thank you.

 

[ettienne]

Crystal report > PDF > email PDF

 

[Boris10]

Thank you Ettinne, i was wonderinf if there was anyway to loop through the grid and dircetly save it into PDF? Any ideas?

 

[Jay Converse]

Private Sub cmdExport_Click()

Dim sReportName As String
Dim rptPrintSetup As AccpacPrintSetup
Dim rpt As AccpacReport

Dim iRecordCount As Long

If UCase(fso.GetExtensionName(Me.txtInputFileName)) <> "RPT" Then
    MsgBox "That is not a valid Crystal report file", , APPHEADER
    Exit Sub
End If

If Not fso.FileExists(Me.txtInputFileName) Then
    MsgBox "Crystal report file does not exist", , APPHEADER
    Exit Sub
End If

sReportName = fso.GetFileName(Me.txtInputFileName)

CSCompany.Read
ARBatchHeader.Init
ARBatchHeader.Browse "CNTBTCH = " & Me.fldVendor, True
Me.fldVendor.DataSource.Read

Do While ARBatchHeader.Fetch
    iRecordCount = iRecordCount + 1
    Me.lblStatus = "Processing batch " & Me.fldVendor & ", entry " & iRecordCount & " of " & Me.fldVendor.DataSource.Fields("CNTINVCENT")
    DoEvents

'    PrintRep "{ARIBH.CNTBTCH} in " & Me.fldVendor & " to " & Me.fldVendor & " and {ARIBH.CNTITEM} in " & ARBatchHeader.Fields("CNTITEM") & " to " & ARBatchHeader.Fields("CNTITEM")
    Set rpt = ReportSelect("ARDINVO[" & Me.txtInputFileName & "]", "      ", "      ")
    Set rptPrintSetup = GetPrintSetup("      ", "      ")
    rpt.SetParam "SELECTBY", "1"             ' Report parameter: 12
    rpt.SetParam "FROMBATCH", Me.fldVendor
    rpt.SetParam "TOBATCH", Me.fldVendor
    rpt.SetParam "FROMENTRY", ARBatchHeader.Fields("CNTITEM")
    rpt.SetParam "TOENTRY", ARBatchHeader.Fields("CNTITEM")
    rpt.SetParam "FROMCUST", " "             ' Report parameter: 0
    rpt.SetParam "TOCUST", "ZZZZZZZZZZZZ"    ' Report parameter: 0
    rpt.SetParam "FROMDOCNBR", " "           ' Report parameter: 0
    rpt.SetParam "TODOCNBR", "ZZZZZZZZZZZZZZZZZZZZZZ"   ' Report parameter: 0
    rpt.SetParam "FCURNDEC", "2"             ' Report parameter: 0
    rpt.SetParam "ADDR01", CSCompany.Fields("ADDR01")
    rpt.SetParam "ADDR02", CSCompany.Fields("ADDR02")
    rpt.SetParam "ADDR03", CSCompany.Fields("ADDR03")
    rpt.SetParam "ADDR04", CSCompany.Fields("ADDR04")
    rpt.SetParam "CITY", CSCompany.Fields("CITY")
    rpt.SetParam "STATE", CSCompany.Fields("STATE")
    rpt.SetParam "POSTAL", CSCompany.Fields("POSTAL")
    rpt.SetParam "@SELECTION_CRITERIA", "{ARIBH.CNTBTCH} in " & Me.fldVendor & " to " & Me.fldVendor & " and {ARIBH.CNTITEM} in " & ARBatchHeader.Fields("CNTITEM") & " to " & ARBatchHeader.Fields("CNTITEM")
    rpt.NumOfCopies = 1
    rpt.Destination = PD_FILE
    rpt.Format = PF_PDF
    rpt.PrintDir = Me.lblExportFolder & ARBatchHeader.Fields("IDINVC") & ".PDF"
    rpt.PrintReport
    Set rptPrintSetup = Nothing
    Set rpt = Nothing


Loop

Me.lblStatus = ""
MsgBox iRecordCount & " invoices exported to " & Me.lblExportFolder, , APPHEADER

Exit Sub

'------------------------------
BadStuff:
HandleError "CmdExport"
MsgBox "Critical processing error, contact programming", vbExclamation, APPHEADER
Me.Show

End Sub

 

[ettienne]

Boris, why reinvent the wheel? I'm sure there are some utilities that will allow you to write to PDF, something like CutePDF maybe?

 

[Boris10]

Thank you for the Post Tuba. As far as i understand this code can allow me to print ACCPAC table results, i am going to try it out.
I agree with you Ettiene about reinventing the wheel, it does sound quite unnecessary. However, my sql query looks quite complicted. Therefore, i sought such solution.
Initially i was thinking to send an email via sql dbmail and attach my query there, however the file format that i can attach to an email is txt. Thus, i am trying to find more user freidnly way to display query results.

Thank you for your contributions!

 

[Boris10]

I managed to send an email via sql with my query, in HTML format. It came out quite neat and simple.

Thank you once again