Error Attempting to Open Crystal Reports via Script.

Question

Hello, 
 
 I'm working on a project where I need to pass a parameter to and open a custom crystal report. I have done a lot of searching, and understand that it is possible, but I can't even open crystal let alone pass parameters. I keep receiving the following error: 
 
 OLE Error Number: 429 Description: ActiveX component can't create object: 'CrystalRuntime.Application' Language: VBScript 
 
 I have tried several different methods of opening crystal, here are a few but I get the same error regardless: 
 Ex:1 
 Set FSO = CreateObject("Scripting.FileSystemObject") Set Appn = CreateObject("CrystalRuntime.Application") Set Report = Appn.OpenReport(ReportName) 
 Ex:2 
 Dim oApp, oRpt vFilenameReport = UCase("C:\Path\ToReport\myreport.rpt") vFilenameExport = UCase("C:\Path\ToDestination\myreport.xls") Set oApp =CreateObject("CrystalRuntime.Application") Set oRpt = oApp.OpenReport(vFilenameReport, 1) 
 
 If anyone has any knowledge as to what I'm doing wrong I would greatly appreciate it. 
 
 Thanks!

David Speck · Answer

Here are two scripts you can use to open a report using the SY_ReportEngine object. 
 One is for executing from a button or event script, i.e. from inside of sage 100. 
 The other is for executing from outside of sage 100. Make sure the script file is executed by the 32 bit version of cscript.exe or wscript.exe. 
 This is from inside sage 100. 
 If IsObject(oSession) Then
	sReportName = "Report1.rpt"
	sReportTitle = "Report Test"
	sPathRoot = ""
	sPathRoot = oSession.PathCSRoot
	Set oFileSystemObject = CreateObject("Scripting.FileSystemObject")
	If Not(oFileSystemObject.FolderExists(sPathRoot)) Then sPathRoot = oSession.PathRoot
	sReportPath = sPathRoot & "Reports\" & sReportName
	bValidReportPath = oFileSystemObject.FileExists(sReportPath)
	Set oFileSystemObject = Nothing
	sErrorMsg = ""
	If bValidReportPath Then
 nReport_Rpt = 0 : nReport_Rpt = oSession.GetObject("SY_ReportEngine")
 If nReport_Rpt > 0 Then
 Set oReport_Rpt = oSession.AsObject(nReport_Rpt)
 If oReport_Rpt.OpenReport(sReportPath) = 1 Then
 oReport_Rpt.SetReportTitle sReportTitle
 oReport_Rpt.SetPreviewOptions sReportTitle
 If oReport_Rpt.DatabaseLogon() = 1 Then
 oReport_Rpt.SetSelectionFormula("Not(IsNull({SO_SalesOrderHeader.CustomerPONo}))") ' Use this to set your own selection formula in Crystal Syntax.
 oReport_Rpt.SetFormula "CompanyCode", """" & oSession.CompanyCode & """" ' Use this to set the value of the formula name specified in the first argument. You must respect the syntax specified in the formula in the report.
 oReport_Rpt.Preview
 Else
 sErrorMsg = "Unable to logon to database." _
 & vbCrLf & "Error Number: " & oReport_Rpt.sLastErrorNum _
 & vbCrLf & "Error Message: " & oReport_Rpt.sLastErrorMsg
 End If
 Else
 sErrorMsg = "Unable to open report." _
 & vbCrLf & "Error Number: " & oReport_Rpt.sLastErrorNum _
 & vbCrLf & "Error Message: " & oReport_Rpt.sLastErrorMsg
 End If
 oReport_Rpt.CloseReport
 Set oReport_Rpt = Nothing
 Else
 sErrorMsg = "Unable to get handle to SY_ReportEngine." _
 & vbCrLf & "Error Number: " & oSession.sLastErrorNum _
 & vbCrLf & "Error Message: " & oSession.sLastErrorMsg
 End If
	Else
 sErrorMsg = """" & sReportPath & """ could not be found."
	End If
End If 
 
 This is from outside sage 100. 
 Set oWScriptShell = CreateObject("WScript.Shell")
sPathRoot = oWScriptShell.RegRead("HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\SOTAMAS90\Directory") ' Using the SOTAMAS90 DSN to determine path.
Set oWScriptShell = Nothing
sReportName = "Report1.rpt"
sReportPath = sPathRoot & "\Reports\" & sReportName
Set oFileSystemObject = CreateObject("Scripting.FileSystemObject")
bValidReportPath = oFileSystemObject.FileExists(sReportPath)
Set oFileSystemObject = Nothing
sErrorMsg = ""
If bValidReportPath Then
	Set oProvideXScript = CreateObject ("ProvideX.Script")
	oProvideXScript.Init(sPathRoot & "\Home") ' Make sure to set this path to point to the mas90 folder on the server if you don't use the SOTAMAS90 DSN.
	Set oSession = oProvideXScript.NewObject("SY_Session")
	Set oProvideXScript = Nothing
	' UI object is needed if you want to see progress bars, message boxes, or preview the report.
	If IsObject(oSession.oUI) Then
 Set oUI = oSession.oUI ' Use this to set your own object handle to the existing UI object.
 ' oSession.nTerminateUI ' Use this to terminate the UI if it exists and you don't need it.
	Else
 Set oUI = oSession.oInitiateUI() ' Use this to set your own object handle by initiating the UI object.
	End If
	' Make sure to specify credentials.
	sUserName = "user"
	sUserPassword = "password"
	If oSession.nSetUser(sUserName, sUserPassword) = 1 Then
 sCompanyCode = "ABC"
 If oSession.nSetCompany(sCompanyCode) = 1 Then
 sModuleCode = "S/Y" ' Set this to the module code that you intend to access in the X/X format.
 sModuleDate = "20190815" ' This needs to be in yyyyMMdd format.
 sModuleDate = oSession.sSystemDate ' Use this if you want to use the current system date returned by the SY_Session object.
 oSession.nSetDate sModuleCode, sModuleDate
 oSession.nSetModule sModuleCode
 sReportTitle = "Report Test"
 If oSession.nSetProgram(oSession.nLookupTask("SY_Listing_UI")) > 0 Then
 nReport_Rpt = 0 : nReport_Rpt = oSession.nNewObject("SY_ReportEngine")
 If nReport_Rpt > 0 Then
 Set oReport_Rpt = oSession.oGetObject("SY_ReportEngine")
 ' Make sure your report has been converted to the correct level or else the report engine will not open it.
 If oReport_Rpt.nOpenReport(sReportPath) = 1 Then
 oReport_Rpt.nSetReportTitle sReportTitle
 oReport_Rpt.nSetPreviewOptions sReportTitle
 If oReport_Rpt.nDatabaseLogon() = 1 Then
 oReport_Rpt.nSetSelectionFormula("Not(IsNull({SO_SalesOrderHeader.CustomerPONo}))") ' Use this to set your own selection formula in Crystal Syntax.
 oReport_Rpt.nSetFormula "CompanyCode", """" & oSession.sCompanyCode & """" ' Use this to set the value of the formula name specified in the first argument. You must respect the syntax specified in the formula in the report.
 oReport_Rpt.nPreview
 Else
 sErrorMsg = "Unable to logon to database." _
 & vbCrLf & "Error Number: " & oReport_Rpt.sLastErrorNum _
 & vbCrLf & "Error Message: " & oReport_Rpt.sLastErrorMsg
 End If
 Else
 sErrorMsg = "Unable to open report." _
 & vbCrLf & "Error Number: " & oReport_Rpt.sLastErrorNum _
 & vbCrLf & "Error Message: " & oReport_Rpt.sLastErrorMsg
 End If
 oReport_Rpt.nCloseReport
 Set oReport_Rpt = Nothing
 oSession.nDropObject nReport_Rpt
 Else
 sErrorMsg = "Unable to get handle to SY_ReportEngine." _
 & vbCrLf & "Error Number: " & oSession.sLastErrorNum _
 & vbCrLf & "Error Message: " & oSession.sLastErrorMsg
 End If
 Else
 sErrorMsg = "Unable to get security object." _
 & vbCrLf & "Error Number: " & oSession.sLastErrorNum _
 & vbCrLf & "Error Message: " & oSession.sLastErrorMsg
 End If
 Else
 sErrorMsg = "Unable to set company." _
 & vbCrLf & "Error Number: " & oSession.sLastErrorNum _
 & vbCrLf & "Error Message: " & oSession.sLastErrorMsg
 End If
	Else
 sErrorMsg = "Unable to set user." _
 & vbCrLf & "Error Number: " & oSession.sLastErrorNum _
 & vbCrLf & "Error Message: " & oSession.sLastErrorMsg
	End If
	Set oUI = Nothing
	If IsObject(oSession.oUI) Then oSession.nTerminateUI
	oSession.nCleanUp
	Set oSession = Nothing
Else
	sErrorMsg = """" & sReportPath & """ could not be found."
End If 
 
 In both, you can decide what you want to do with the sErrorMsg variable if it is not blank. 
 EDIT: 
 On version 2017 and higher, you need to make sure to copy the MAS90\Home\pvxwin32.exe.config file to the location of pvxcom.exe and rename the copied pvxwin32.exe.config file to pvxcom.exe.config. 
 pvxcom.exe should be in "Program Files (x86)\Common Files\Sage\Common Components" if on 64 bit windows and in "Program Files\Common Files\Sage\Common Components" if on 32 bit windows. 
 EDIT: 
 Corrected a typo in the FolderExists method. 
 EDIT: 
 Corrected extra END IF and another ELSE section.

Kentaro · Answer

a little syntax error 
 David Speck said: If Not(oFileSystemObject.FolderExist(sPathRoot)) Then sPathRoot = oSession.PathRoot 
 should be 
 If Not(oFileSystemObject. FolderExists (sPathRoot)) Then sPathRoot = oSession.PathRoot

Sage 100

Error Attempting to Open Crystal Reports via Script.

Top Replies

COMMUNITY

COMPANY

PARTNERS

SUPPORT & TRAINING