Passing parameters to CR v6.0 using macros and vba? 1

[ehran]

Hello All,

I'm currently working on a project to create an automated batch processing for order confirmations under ACCPAC using macros, VBA, OutLook 2000 and Crystal Reports 6.0

However, I don't quite seem to know how to pass parameters from ACCPAC to my custom report using Macros/VBA.

I found most of the code for exporting to email, save to disk but there isn't much info on passing parameters from ACCPAC to crystal.

Any snippet of code will help and will be most grateful.

 

[DjangMan]

Hi Erahn,

The easiest way to see how the parameters work is to record a macro of you printing a report. Here's the code generated by printing the fiscal calendar:

[tt]
Dim CSCALENDAR1 As ACCPACXAPILib.xapiView
Dim CSCALENDAR1Fields As ACCPACXAPILib.xapiFields
Set CSCALENDAR1 = OpenView("CS0002", "CS&quot
Set CSCALENDAR1Fields = CSCALENDAR1.Fields


Dim rptObj As ACCPACXAPILib.xapiReport
Set rptObj = CreateObject("ACCPAC.xapiReport&quot
rptObj.Select Session, "CSCALENDAR", ""
rptObj.SetParam "YEAR", "2008" ' Report parameter: 4 - Fiscal Year
rptObj.PrintDestination = PD_PREVIEW
rptObj.PrintReport 1
[/tt]

The syntax is:
rptObj.SetParam ParameterName, ParameterValue

DjangMan

 

[ehran]

DjangMan,

Thanks for the info. However I was looking at a way of automating the process with the least amount of user interaction.

Taking the example you provided, say I wanted to export to email.

The syntax would be:
rptObj.PrintDestination = PD_EMAIL

Problem with this is that a dialog box pops up and after you've selected the format, it attaches the report to your email program and you still have to enter the email address you want to send it to.

Looking at the Systems Manager manual for ACCPAC, I could find no code to suppress the dialog box or pass the information such as format or email addresses directly to the email program.

So that's why I've resorted to creating the application in VB instead of using the macros in ACCPAC. I need to know the syntax for correctly opening the view in ACCPAC, export the fields I need to my crystal report.

 

[DjangMan]

Another option might be for you to run the report by automating the Crystal report directly. The snag may be you're using Crystal 6. By opening and manipulating the report directly you pretty much only need to know what company (OrgID) the user wants to print from. From there I have VB code to show you how to get the database location which you need to pass to Crystal to say where the database tables are.

I believe that the Crystal Reports cd has the VB code for controlling reports - have a look on their site or in the Crystal forums here.

Lots of thinks for you to think about so I'll sign off here and see what questions you have.

DjangMan

 

[ehran]

Actually that's what I planned to do after I found out the macros in ACCPAC are very limited and does not offer automation.

So I've opted to automating the Crystal Reports directly with VB code. I have access to both Crystal 6 and 8 on my work system.

If you can drop me the code snippet that'd be great!

Thanks,
Ehran

 

[ehran]

Djangman,

Here's the bit of code snippet that I'm trying to have automated. Originally when I intended to use macros in ACCPAC, I did the following:

For OrderNumberSelected = SrtFrom To SrtTo - 1
rptObj.Select Session, "OECONF01[ACKNOWL.RPT]", ""
rptObj.SetParam "SORTFROM", "TS" & OrderNumberSelected
rptObj.SetParam "SORTTO", "TS" & OrderNumberSelected
rptObj.SetParam "PRINTED", "1"
rptObj.SetParam "QTYDEC", "0"
Next OrderNumberSelected

This does not allow me convenience of automation. So I've decided to go with automating it directly through the use of VB code and Crystal 8.0 as seen below.

Set CrystalApp = CreateObject("Crystal.CRPE.Application&quot
Set CrystalRpt = CrystalApp.OpenReport ("C:\ACKNOWL.RPT&quot

Set CrystalOpt = CrystalRpt.ExportOptions
Set CrystalRpt = CrystalApp.OpenReport("C:\ACKNOWL.RPT&quot

CrystalRpt.ParameterFields(5) = "SORTFROM;TS211160;TRUE"
CrystalRpt.ParameterFields(6) = "SORTTO;TS211160;TRUE"
CrystalRpt.ParameterFields(7) = "PRINTED;1;TRUE"

When I run the code, I get a Runtime Error 13: Type Mismatch

I suspect it may have something to do with the way I declared it but unfortunately I'm not programmer and can't see what I'm doing wrong.

Any suggestions

 

[ehran]

Djangman,

Here's the bit of code snippet that I'm trying to have automated. Originally when I intended to use macros in ACCPAC, I did the following:

For OrderNumberSelected = SrtFrom To SrtTo - 1
rptObj.Select Session, "OECONF01[ACKNOWL.RPT]", ""
rptObj.SetParam "SORTFROM", "TS" & OrderNumberSelected
rptObj.SetParam "SORTTO", "TS" & OrderNumberSelected
rptObj.SetParam "PRINTED", "1"
rptObj.SetParam "QTYDEC", "0"
Next OrderNumberSelected

This does not allow me convenience of automation. So I've decided to go with automating it directly through the use of VB code and Crystal 8.0 as seen below.

Set CrystalApp = CreateObject("Crystal.CRPE.Application&quot
Set CrystalRpt = CrystalApp.OpenReport ("C:\ACKNOWL.RPT&quot

Set CrystalOpt = CrystalRpt.ExportOptions
Set CrystalRpt = CrystalApp.OpenReport("C:\ACKNOWL.RPT&quot

CrystalRpt.ParameterFields(5) = "SORTFROM;TS211160;TRUE"
CrystalRpt.ParameterFields(6) = "SORTTO;TS211160;TRUE"
CrystalRpt.ParameterFields(7) = "PRINTED;1;TRUE"

When I run the code, I get a Runtime Error 13: Type Mismatch

I suspect it may have something to do with the way I declared it but unfortunately I'm not programmer and can't see what I'm doing wrong.

Any suggestions?

 

[DjangMan]

Hi Erhan,

One thing to check is to make sure that the type of value you are passing are strings. It may be the PRINTED parameter causing you grief as it looks like an integer value (0 or 1).

I don't have the developer version of Crystal 8 so I'm not familiar with the syntax. :-(

Let me know how it goes!

DjangMan

 

[DjangMan]

Hi Ehran,

There's a fellow in the Crystal forums here who has a $12 manual on running Crystal from VB and automation that may be of help.

http://www.kenhamady.com/

DjangMan

 

[ehran]

Hi DjangMan,

Long time no chat...

Anyways, finally figured out what I was doing wrong with the automation process. Turns out I was mixing the methods between the crystal API and OCX. Got it automated this morning just by using the CRYSTL32.OCX

Now I have a problem that relating to ACCPAC.

I need to extract information from OEORDH.DAT to pass to CRYSTL32.OCX and this is how I began the code snippet:

Dim X As String
Dim OEORD1 As ACCPACXAPILib.xapiView
Dim OEORD1Fields As ACCPACXAPILib.xapiFields
Set OEORD1 = OpenView("OE0520", "OE&quot
Set OEORD1Fields = OEORD1.Fields
OEORD1.Read

X = OEORD1Fields("PONUMBER&quot

When I do a MsgBox X to debug and see what the "PONUMBER" is, it displays nothing.

I can't see what is wrong with the coding since I use the same when extracting the ORDNUMBER from OEOPT.DAT

Any feedback is appreciated

 

[DjangMan]

Hi Ehran,

Your code is just missing a few lines:
[tt]
Dim X As String
Dim OEORD1 As ACCPACXAPILib.xapiView
Dim OEORD1Fields As ACCPACXAPILib.xapiFields
Set OEORD1 = OpenView("OE0520", "OE&quot
Set OEORD1Fields = OEORD1.Fields

OEORD1.Init
OEORD1Fields(&quot;ORDUNIQ&quot.PutWithoutVerification <number>

OEORD1.Read
[/tt]

The OE Order Detail lines are indexed on the ORDUNIQ field which you need to get from the OE Order Header table.

Stepping back, you have two ways to get to the data, using read or browse and fetch. Using read you have to populate the indexes first. Using browse and fetch you can start at the first order and work your way down.

It all depends on what you need to do...

DjangMan

 

[ehran]

DjangMan,

Good News!!! It works... I embedded a CRYSTAL.OCX onto the form and everything was a breeze from there...

Now I gotta figure out how to implement fax capabilities...

Anyways, thanks for all your help in the past.

Ehran

 

[Guest_imported]

Hi,

I Have Accpac for Windows and i want to open Session and read information in table like PO, OE. This is possible with VBA with the Library ACCPACXAPILib ? How can't program in VBA. I try but i don't no how to use the sub Open. I need to pass the company, User, Password and Date Session). I try many thinks but is not working.

Dim APSession As ACCPACXAPILib.xapiSession

Sub Open(UserIdentifier As String, Password As String, DataBase As String, SessionDate As Date, Flags As Long)

Please help me,

Eric