• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3497
  • Last Modified:

VBS - Run Crystal Report with Parameters

Via VB script I want to be able to run a crystal report (vers. XI), enter the report parameters, save the report to a server location and email it. This script (attached) does everything that I want except for setting the report parameters. Any ideas? Thanks!
Dim sReportName
Dim CRapp
Dim reportDb
Dim reportTables
Dim reportTable
Dim sReportOutput
Dim objOutl

'**********************************************************************************************
'Specify the report file name and location
'**********************************************************************************************
	
	sReportName = "C:\SampleCrystal\test.rpt" 'Crystal report location
	sReportOutput = "C:\SampleCrystal\SampleFinances_20111018.pdf"
	
'**********************************************************************************************
'Save report file in PDF format - specified location
'**********************************************************************************************

	Set objCRApp= WScript.CreateObject("CrystalRunTime.Application")
	Set objReport= objCRApp.OpenReport(sReportName)
	Set reportDb = objReport.Database
	Set reportTables = reportDb.Tables
	Set reportTable = reportTables.Item(1)

	Call reportTable.SetLogOnInfo("Opennet Custom Report") 'ODBC Connection to DBF file

	With objReport
		.EnableParameterPrompting=True
		.ExportOptions.FormatType = 31 'crEFTPortableDocFormat
		.ExportOptions.DestinationType = 1 'crEDTDiskFile
		.ExportOptions.DiskFileName = sReportOutput
		.Export(False)
	End With

'**********************************************************************************************
'Email attached report file
'**********************************************************************************************
	
	Set objOutl = CreateObject("Outlook.Application")
	Set objMailItem = objOutl.CreateItem(olMailItem)
	strEmailAddr  = "yourname@whatever.com"
	objMailItem.Subject = "Weekly Report File: " & Mid(sReportOutput, 18, 23) 
	objMailItem.Body = "Test: Please see the attached weekly report file."

	'objMailItem.Display
	objMailItem.Recipients.Add strEmailAddr
	objMailItem.Attachments.Add sReportOutput
	objMailItem.Send

'**********************************************************************************************
'Error handling and clean-up
'**********************************************************************************************

On Error Resume Next

Set CRapp = Nothing
Set report = Nothing
Set reportDb = Nothing
Set reportTables = Nothing
Set reportTable = Nothing
Set objMailItem = nothing
Set objOutl = nothing

Open in new window

0
GreenLeghorn
Asked:
GreenLeghorn
  • 7
  • 7
1 Solution
 
mlmccCommented:
Here is an example of passing parameters to the report

http://www.experts-exchange.com/Programming/Languages/.NET/Visual_Basic.NET/Q_24572097.html

mlmcc
0
 
GreenLeghornAuthor Commented:
I didn't find any code within that linked question/answer or the link ref'd in the question/answer that will work with my vb script. I need to know where to set the parameter in the script (in or outside of the With statement?) and the basic syntax that i need to use to make it work.

objReport.SetParameterValue("End Date", "1/1/2010")
- Error: Cannot use parenthesis when calling a Sub
0
 
mlmccCommented:
Try

objReport.SetParameterValue "End Date", "1/1/2010"
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
GreenLeghornAuthor Commented:
Error: Object doesn't support this property or method: 'objReport.SetParameterValue'
0
 
GreenLeghornAuthor Commented:
There is 1 parameter for this report named "End Date" in Crystal.
0
 
mlmccCommented:
Does VBS provide any options when you type in objReport. ?

I don't use VBS so I don't know how it is done.

Are you trying to use the RDC (CRAXDRT.DLL)?

mlmcc
0
 
GreenLeghornAuthor Commented:
No, I am writing this script in notepad.
My code is posted above, I am not using any type of remote connection to call a DLL.
0
 
mlmccCommented:
The RDC is not a remote connection.  It is the Report Design Component and the API for using Crystal.

My question is, what Crystal application object is being created here
Set objCRApp= WScript.CreateObject("CrystalRunTime.Application")

It seems to be the RDC application object.

Look for this file
crsdk_net_doc.chm

On my system it is here
C:\Program Files\Business Objects\Crystal Reports 11.5\Help\en\crsdk_net_doc\doc

You can then examine the parameter class and see how values are passed in VB.

mlmcc
0
 
GreenLeghornAuthor Commented:
Oops, wrong interpretation.
I checked that help file and don't see any VBS code that can help me with my problem.

I see a lot of VB6 ref's but that isn't helping me to determine the correct VBS syntax to use.
0
 
mlmccCommented:
There is very limited help for VBS with Crystal.  I don't believe they ever really intended it to be run that way.  You will be using the same dll with VBS as with VB6 or VB.Net.  The best you can probably get is to look at the references and see the methods used then try to use a similar construct.

I don't recall having ever seen a VBS example ont he Crystal site.

mlmcc
0
 
GreenLeghornAuthor Commented:
I agree with you, but I have limited resources here for automating/scheduling Crystal reports and would like to just finish up this last piece of my code to include report parameters.

I have searched and searched the web and find many posts of where this same type of problem has come up, but I have yet to find a solution.
0
 
mlmccCommented:
Here is a question with code from Crystal

http://forums.sdn.sap.com/thread.jspa?threadID=1970049

Parameters passed as

Report.ParameterFields.GetItemByName("From").AddCurrentValue CDate("2011-06-06 00:00:00")


mlmcc
0
 
GreenLeghornAuthor Commented:
That worked!! Thank you very much for that reference.
This VBS now runs the crystal report, includes parameters, saves to a disk file location and emails the report file.
Dim Appn
Dim Report
Dim FSO
Dim Path
Dim ReportName
Dim OutputFile
Dim DiskFilePath
Dim Param1
Dim Param1Name
Dim EmailAddr
Dim EmailAttach
Dim FullOutputPath
Dim EmailSubj
Dim EmailBody
 
On Error Resume Next

'**********************************************************************************************
'Define variables
'**********************************************************************************************
	ReportName = "C:\SampleCrystal\Test.rpt"
	DiskFilePath = "C:\SampleCrystal\"
	OutputFile = "SampleFinances"
	FileFormat = ".pdf"
	Param1Name = "End Date"
	Param1 = "2011-06-06 00:00:00"
	
	EmailAddr  = "yourname@whatever.com"
	EmailSubj = "Weekly Report File: "
	EmailBody = "Test: Please see the attached weekly report file."

'**********************************************************************************************
'Open report
'********************************************************************************************** 
	Set FSO = CreateObject("Scripting.FileSystemObject")
	Set Appn = CreateObject("CrystalRuntime.Application")
 	Set Report = Appn.OpenReport(ReportName)
 	
	Path = OutputFile & "_" & Year(Now) & Month(Now) & Day(Now) & Hour(Now) & Minute(Now) & Second(Now)
	FullOutputPath = DiskFilePath & Path & FileFormat
	
'**********************************************************************************************
'Report parameters
'********************************************************************************************** 
	Report.ParameterFields.GetItemByName(Param1Name).AddCurrentValue CDate(Param1)
	Report.EnableParameterPrompting = False

'**********************************************************************************************
'Report format
'********************************************************************************************** 
	Report.ExportOptions.FormatType = 31 ' crEFTPortableDocFormat
	Report.ExportOptions.DestinationType = 1 ' crEDTDiskFile
	Report.ExportOptions.PDFExportAllPages = True
	Report.ExportOptions.DiskFileName = FullOutputPath 
	Report.Export (False)

'**********************************************************************************************
'Email report
'********************************************************************************************** 	
	Set objOutl = CreateObject("Outlook.Application")
	Set objMailItem = objOutl.CreateItem(olMailItem)
	objMailItem.Subject = EmailSubj & Mid(FullOutputPath, 18, 29) 
	objMailItem.Body = EmailBody
	'objMailItem.Display
	objMailItem.Recipients.Add EmailAddr
	objMailItem.Attachments.Add FullOutputPath
	objMailItem.Send

'**********************************************************************************************
'Object clean-up
'********************************************************************************************** 	
	Set FSO = Nothing
	Set Appn = Nothing
	Set Report = Nothing
	Set objOutl = Nothing
	Set objMailItem = Nothing

Open in new window

0
 
mlmccCommented:
Great.  I'll save this as a reference for Crystal/VBS questions in the future

mlmcc
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

  • 7
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now