VBA Macro with Crystal Report

VBA Macro with Crystal Report

Version Compatibility : Microsoft Visual Basic 6.5,Crystal Reports 11 or Crystal Report 8.5

More informations : VBA Macro support report software business packes Example use : Sage Accpac or software business other. You can create VBA Macro with MS SQL 2000-2005, Pervasive and use Crystal report 11, Crystal report 8.5. You not build EXE file. You can copy VBA Macro Project, Crystal report file and Run.

Instructions : Copy the delarations and code below and paste directly into your VBA Macro project.

Delarations :

Add References and Components to New Form1
Available Conponents:
-Crystal ActiveX Report Viewer Controls 11.0

Available References:
-Visual Basic For Applications
-Microsoft Forms 2.0 Object Library
-Crystal ActiveX Report Viewer Library 11.0
-Crystal Reports ActiveX Designer Run Time Library 11.0

Source Code :

Private Sub UserForm_Initialize()
Dim rptReport As New CRAXDRT.Report
Dim rptRepApp As New CRAXDRT.Application
Dim rptViews As CRAXDRT.DatabaseTable
Dim strCon As CRAXDRT.ConnectionProperties
Dim rptPath As String

'--
Set rptRepApp = New CRAXDRT.Application
rptPath = ReportPath & "\" & "oeorlst1_CUS.rpt"' ' Set Report Path

'-- Show Report
With CRVReport ' CRVReport is CrystalActiveXReportViewer Control
Set rptReport = rptRepApp.OpenReport(rptPath, 1)
With rptReport
.FormulaFields.GetItemByName("fmtCUSCODEFROM").Text = "totext('" & Trim("" & strCustomerCodeFrom) & "')"
.FormulaFields.GetItemByName("fmtCUSCODETO").Text = "totext('" & Trim("" & strCustomerCodeTo) & "')"
.FormulaFields.GetItemByName("fmtPONUMFROM").Text = "totext('" & Trim("" & strPONumberFrom) & "')"
.FormulaFields.GetItemByName("fmtPONUMTO").Text = "totext('" & Trim("" & strPONumberTo) & "')"
.FormulaFields.GetItemByName("fmtDATEFROM").Text = "totext('" & Trim("" & strDateFrom) & "')"
.FormulaFields.GetItemByName("fmtDATETO").Text = "totext('" & Trim("" & strDateTo) & "')"
End With

.ReportSource = rptReport
.ViewReport
.Zoom 90
End With
End Sub

Remark : You notice
.FormulaFields.GetItemByName("fmtCUSCODEFROM").Text
.FormulaFields.GetItemByName("fmtCUSCODETO").Text
.FormulaFields.GetItemByName("fmtPONUMFROM").Text
.FormulaFields.GetItemByName("fmtPONUMTO").Text
.FormulaFields.GetItemByName("fmtDATEFROM").Text
.FormulaFields.GetItemByName("fmtDATETO").Text

is Folmura Fields Name add Stirng Value and Show to Crystal Report.