Command Line Project:
Export crystal report to file in pdf/excel/word format & optionally email it.
Command line args incl input parameters for the crystal report.
VB6 being converted to VB.net using Visual Basic 2005 Express Edition.
Creator has since left & I need to fix/edit the application.
Having problems... when call Export, CR params dialog box pops up awaiting user input (& params not set properly), dont want this to popup.
Googling lots, cant find answer, examples use these references, but causes namespace or type not found in my project.
EXTRACTS of the VB.net code:
Export crystal report to file in pdf/excel/word format & optionally email it.
Command line args incl input parameters for the crystal report.
VB6 being converted to VB.net using Visual Basic 2005 Express Edition.
Creator has since left & I need to fix/edit the application.
Having problems... when call Export, CR params dialog box pops up awaiting user input (& params not set properly), dont want this to popup.
Googling lots, cant find answer, examples use these references, but causes namespace or type not found in my project.
VB.NET:
Imports CrystalDecisions.CrystalReports.Engine
Imports CrystalDecisions.Shared
Dim objReport As New ReportDocument
Dim paraValue As New ParameterDiscreteValue
Dim currValue As ParameterValues
objReport.SetParameterValue("@whichclient", Value.ToString)EXTRACTS of the VB.net code:
VB.NET:
'* References: added
'EventSystem 1.0 Type Library; COM; 1.0; C:\Windows\system32\es.dll
'Microsoft CDO for Windows 2000 Library; COM; 1.0; C:\Windows\system32\cdosys.dll
'Microsoft Scripting Runtime; COM; 1.0; C:\Windows\system32\scrrun.dll
'OLE Automation; COM; 2.0; C:\Windows\system32\stdole2.tlb
'Crystal Reports 9 ActiveX Designer Run Time Library; COM; 9.0; C:\Program Files\Crystal Decisions\Report Designer Component\craxdrt9.dll
'Crystal Report Viewer Control 9; COM; 9.2; C:\Program Files\Common Files\Crystal Decisions\2.0\crystalreportviewers\ActiveXViewer\crviewer9.dll
'Crystal Reports 9 Library; COM; 9.2; C:\Program Files\Crystal Decisions\Report Designer Component\craxdui.dll
'* References: couldnt add, said already existed:
'Microsoft ActiveX Data Objects 2.8 Library; C:\Program Files\Common Files\System\ado\msado15.dll
Dim strRepCmdLnArgs(20) As String
Dim strParamName(20) As String
Dim strParamValue(20) As String
Dim strReportParamName(20) As String
Dim strReportParamValue(20) As String
Dim a_strArgs() As String
...etc...
Public Sub SendEmail(ByVal pi_strRecipient As String, ByVal pi_strSender As String, ByVal pi_strSubject As String, ByVal pi_strMessage As String, ByVal pi_strSMTP As String)
Dim objMail = New clsEmail
Call objMail.SendEmail(pi_strRecipient, pi_strSubject, pi_strMessage, pi_strSender, pi_strSMTP, strExportFilename)
Exit Sub
End Sub
Sub Main()
...etc...
a_strArgs = Environment.GetCommandLineArgs()
'Get parameter & report parameter names & values from command line (0=path/appname)
For intCounter = 1 To UBound(a_strArgs)
intEqualPos = InStr(a_strArgs(intCounter), "=")
intStrLen = Len(a_strArgs(intCounter))
strRepCmdLnArgs = a_strArgs(intCounter).Split(New Char() {","c})
'Fill arrays: strParamName, strParamValue, strReportParamName, strReportParamValue with commandline args passed
For intCounter2 = 0 To UBound(strRepCmdLnArgs)
strRepCmdLnArgs(intCounter2) = Replace(strRepCmdLnArgs(intCounter2), "'", String.Empty)
intEqualPos = InStr(strRepCmdLnArgs(intCounter2), "=")
intStrLen = Len(strRepCmdLnArgs(intCounter2))
strTempValue = Right(strRepCmdLnArgs(intCounter2), intStrLen - intEqualPos)
'if it's a report parameter field, it starts with @@
If Left(strRepCmdLnArgs(intCounter2), 2) = "@@" Then
strTempName = Mid(strRepCmdLnArgs(intCounter2), 2, intEqualPos - 2)
strReportParamName(intRepParamCounter) = strTempName
strReportParamValue(intRepParamCounter) = strTempValue
intRepParamCounter = intRepParamCounter + 1
Else
strTempName = Left(strRepCmdLnArgs(intCounter2), intEqualPos - 1)
strParamName(intParamCounter) = strTempName
strParamValue(intParamCounter) = strTempValue
intParamCounter = intParamCounter + 1
End If
Next intCounter2
Next intCounter
...etc...
Call PrintReport(strParamReportName, strParamDSN, strParamExportType, strParamExportFileName)
If blnSendEmail = True Then
Call SendEmail(strRecipient, strSender, strSubject, strMessage & vbCr & "THIS IS AN AUTOMATED MESSAGE, DO NOT REPLY ", strSMTP)
End If
Exit Sub
End Sub
Public Sub PrintReport(ByVal pi_strReportName As String, ByVal pi_strDSN As String, ByVal pi_strExportType As String, ByVal pi_strExportFileName As String)
Dim objCrApp As CRAXDRT.Application = New CRAXDRT.Application
Dim objCrRpt As CRAXDRT.Report = objCrApp.OpenReport(pi_strReportName, crOpenReportByDefault)
Dim objParams As CRAXDRT.ParameterFieldDefinitions = objCrRpt.ParameterFields
Dim objParam As CRAXDRT.ParameterFieldDefinition
Dim objDb As CRAXDRT.Database = objCrRpt.Database
Dim objTables As CRAXDRT.DatabaseTables = objDb.Tables
Dim objTable As CRAXDRT.DatabaseTable = objTables.Item(1)
Dim objCrExport As CRAXDRT.ExportOptions = objCrRpt.ExportOptions
Dim objConnDummy As ADODB.Connection = New ADODB.Connection
Dim intParamCount As Integer = 0
Dim intCounter As Integer = 0
Dim strParamName2 As String = ""
Dim strRepParamName As String = ""
Dim strParamValue2 As String = ""
Dim strPassword As String = ""
...etc...
' Set connection details for crystal report
With objTable
.ConnectionProperties("FILEDSN").Value = pi_strDSN
.ConnectionProperties("Password").Value = strPassword
.ConnectionProperties("Database").Value = objConnDummy.DefaultDatabase
End With
'Set strParamName2 & strParamValue2 for each ReportParam
'and set the relevant parameter by calling: objParam.AddCurrentValue(strParamValue2) in the Crystal Report itself
' Loop through parameters in Crystal Report itself
For intParamCount = 1 To objParams.Count
objParam = objParams.Item(intParamCount)
strParamName2 = objParam.ParameterFieldName
' Loop through repParams passed in via command line
For intCounter = 0 To intRepParamCounter - 1
strRepParamName = Mid(strReportParamName(intCounter), 2)
' If Crystal Report parameter = repParams from command line, set it
If UCase(strParamName2) = UCase(strRepParamName) Then
'check data type
Select Case objParam.ValueType
Case crStringField
strParamValue2 = strReportParamValue(intCounter)
objParam.ClearCurrentValueAndRange()
objParam.AddCurrentValue(strParamValue2)
Case crDateTimeField
If UCase(strParamName2) = "@STARTD" Then
strHmsTime = " 00:00:00"
ElseIf UCase(strParamName2) = "@ENDD" Then
strHmsTime = " 23:59:59"
End If
'0 = today, -1 = yesterday, etc...
If IsNumeric(strReportParamValue(intCounter)) = True Then
dtDateTime = DateTime.Parse(Today & strHmsTime)
If strReportParamValue(intCounter) <> "0" Then
dtDateTime = DateAdd("d", CDbl(strReportParamValue(intCounter)), dtDateTime)
End If
strDateTime = Format(dtDateTime, scDateFormat2)
Else
dtDateTime = DateTime.Parse(strReportParamValue(intCounter) & strHmsTime)
strDateTime = Format(dtDateTime, scDateFormat2)
End If
strParamValue2 = strDateTime
objParam.AddCurrentValue(strDateTime)
Case crBooleanField
If strReportParamValue(intCounter) = 1 Then
strParamValue2 = "true"
objParam.AddCurrentValue(True)
Else
strParamValue2 = "false"
objParam.AddCurrentValue(False)
End If
Case Else
strParamValue2 = CDbl(strReportParamValue(intCounter))
objParam.AddCurrentValue(strParamValue2)
End Select
' Prepare Report Title for top of Crystal Report (that shows the parameters that were selected)
objCrRpt.ReportTitle = objCrRpt.ReportTitle & strParamName2 & " = " & strParamValue2 & vbCr
End If
Next intCounter
Next intParamCount
' set export criteria: DestinationType, DiskFileName, FormatType
With objCrExport
.DestinationType = crEDTDiskFile
.DiskFileName = pi_strExportFileName
strExportFilename = .DiskFileName
Select Case UCase(pi_strExportType)
Case "PDF"
.FormatType = crEFTPortableDocFormat
Case "EXCEL"
.FormatType = crEFTExcel97
Case "DOC"
.FormatType = crEFTWordForWindows
Case "RPT"
.FormatType = crEFTCrystalReport
Case "XLSDATA"
.FormatType = crEFTExcel80Tabular
.ExcelUseTabularFormat = True
.ExcelUseConstantColumnWidth = True
.ExcelConstantColumnWidth = 150
Case Else
.FormatType = crEFTPortableDocFormat
End Select
End With
' delete file if exists
objFile = New Scripting.FileSystemObject
If objFile.FileExists(pi_strExportFileName) = True Then
objFile.DeleteFile(pi_strExportFileName)
End If
'Export the report
objCrRpt.Export(False) 'Supposed to NOT prompt user but it DOES
'objCrRpt.Export() 'Causes many user prompts
'objCrRpt.Close() 'Not defined
objCrRpt = Nothing
Exit Sub
End Sub
Last edited:
