How to make macro call Crystal Reports? 1

[DavidMWilliams]

Dear all,

I'm hoping you can help; we've developed a Crystal Report to display certain info (summary of invoices by customers).

I would like to make a macro to prompt for the customers and the date period to include in the report. I think I know how to pass this info to Crystal Reports.

What I am having trouble with though is actually calling Crystal Reports. I'd really like to make the macro look just like "another" ACCPAC screen for our staff's ease-of-use. Ideally, I'd like it to honour the user's preferences re printer choice (preview, etc.) and the like.

Thanks!

David M. Williams
National I.T. Manager
Advantage Personnel Pty. Ltd.
AUSTRALIA

 

[Relloc]

Dear David

You can call your new Crystal report like any other ACCPAC report via VBA

Provided that you have correctly made provision for the report in the relevant report ini file.

If u use version 5 plus u can use the accpac fields to do lookups on date, customers etc

If you want I can mail you sample code

RELLOC

 

[DavidMWilliams]

I would love some sample code - that would be great!!

My email address is dwilliams@advantagepersonnel.com.au - many thanks!

David M. Williams
National I.T. Manager
Advantage Personnel Pty. Ltd.
AUSTRALIA

 

[Relloc]

Hello David

My notebook with my samples is going to be out of town for a week.

In the meantime

Have a look at the bas.avb macro. In the ACCPAC MACROS FOLDER

You will find a 4.2 compatible finder and code to automatically update the xxRPT.INI so you don't have to do that during an install of a custom report.

Also look at the System manager guide - chapter 14 Writing Visual Basic macros. I think it shows how to call the 5.1 finder too.

Alternatively

1. Ensure data is not saved with report

2. Ensure there is a parameter called "Company" in the report (it doesn’t need to be placed in the report, the parameter simply needs to exist)

3. You can embed a crystal report in a macro, and change the xxrpt.ini file to handle multiple parameters; or

4. Use the Crystal report control in VBA to pass parameters from a custom screen form (handy for running one report on many companies)

 

[zemp]

Here is some sample code of how a reprot can be called from a Macro. This macro has a small user interface that allows the selection of parameters.

Private Sub cmdProcess_Click()
 On Error GoTo ACCPACErrorHandler
    
    Dim PersonFrom As String, PersonTo As String
    Dim BegDate As String, EndDate As String
    Dim SepPage As Integer
    
    '// Fill variables with values from the UI form controls.
    PersonFrom = Trim(cmbSalesFrom.Text)
    PersonTo = Trim(cmbSalesTo.Text)
    BegDate = Format(dateFrom.Value, "yyyymmdd")
    EndDate = Format(dateTo.Value, "yyyymmdd")
    If chkDifPage.Value = True Then SepPage = 1
    
    '// Set the report.
    Dim CommisionReport As ACCPACXAPILib.xapiReport
    Set CommisionReport = CreateObject("ACCPAC.xapiReport")
    CommisionReport.Select Session, "ARSPCOMM", ""
    
    '// Feed the report parameteres.
    CommisionReport.SetParam "StartSP", PersonFrom
    CommisionReport.SetParam "EndSP", PersonTo
    CommisionReport.SetParam "StartDate", BegDate
    CommisionReport.SetParam "EndDate", EndDate
    CommisionReport.SetParam "Page", chkDifPage
    
    CommisionReport.NumOfCopies = 1
    
    CommisionReport.PrintDestination = PD_PREVIEW
        
    CommisionReport.PrintReport 1
    
  Exit Sub

ACCPACErrorHandler:
  Dim Error As Variant

  If Errors.Count = 0 Then
    MsgBox Err.Description
  Else
    For Each Error In Errors
      MsgBox Error.Description
    Next
    Errors.Clear
  End If

  Resume Next

End Sub

Take Care,

zemp

"Show me someone with both feet on the ground and I will show you someone who can't put their pants on."

 

[zemp]

One more thing, in the example the name of the crystal report is "ARSPCOMM". Don't forget to change the ini file as Relloc mentioned.

Take Care,

zemp

"Show me someone with both feet on the ground and I will show you someone who can't put their pants on."

 

[DavidMWilliams]

You guys are extremely helpful! I feel like I am on my way to becoming an ACCPAC programmer

I have looked at the bas.abv macro although I'm midly concerned mine may be different to yours; my "bas.abv" generates business activity statements which satisfy the Australian tax office's requirements. Although this sample makes sense to me (in Australia) is this the same sample used worldwide?

I am having a problem with the routine to prepare the INI file. In particular, I get an error 'The "Session" Object was was not opened'. I believe this is in the line "With session.ActiveApplications".

I do appreciate the tremendous help so far and I feel confident if I can get this report going my ACCPAC abilities will be greatly enhanced!

Here is my code, in two files.

First, the file frmInvoiceSummary which contains the UI:

-------------------------------------------
Option Explicit

Private Sub bClose_Click()
Unload Me
End Sub

Private Sub bPrint_Click()
On Error GoTo ACCPACErrorHandler

Dim BegDate As String, EndDate As String

BegDate = Format(dateFrom.Value, "yyyymmdd&quot
EndDate = Format(dateTo.Value, "yyyymmdd&quot

'// Set the report.
Dim InvoiceSummaryReport As ACCPACXAPILib.xapiReport
Set InvoiceSummaryReport = CreateObject("ACCPAC.xapiReport&quot
InvoiceSummaryReport.Select session, "ARINVSUM", ""

'// Feed the report parameteres.
InvoiceSummaryReport.SetParam "StartDate", BegDate
InvoiceSummaryReport.SetParam "EndDate", EndDate

InvoiceSummaryReport.NumOfCopies = 1
InvoiceSummaryReport.PrintDestination = PD_PREVIEW
InvoiceSummaryReport.PrintReport 1
Exit Sub

ACCPACErrorHandler:
Dim Error As Variant

If Errors.Count = 0 Then
MsgBox Err.Description
Else
For Each Error In Errors
MsgBox Error.Description
Next
Errors.Clear
End If

Resume Next
End Sub
-------------------------------------------

Next, MainModule -

-------------------------------------------
Option Explicit

' Produce an invoice summary report, between specified dates
' and for specified customers

Global strAccpacDir As String 'The directory where ACCPAC was installed.
Global session As xapiSession 'Current ACCPAC Session

Declare Function RegOpenKeyEx Lib "advapi32" Alias "RegOpenKeyExA" _
(ByVal hKey As Long, ByVal lpSubKey As String, ByVal ulOptions As Long, _
ByVal samDesired As Long, phkResult As Long) As Long
Declare Function RegQueryValueEx Lib "advapi32" Alias "RegQueryValueExA" _
(ByVal hKey As Long, ByVal lpValueName As String, ByVal lpReserved As Long, _
ByRef lpType As Long, ByVal lpData As String, ByRef lpcbData As Long) As Long
Declare Function RegCloseKey Lib "advapi32" (ByVal hKey As Long) As Long

Declare Function GetPrivateProfileSection Lib "Kernel32" Alias "GetPrivateProfileSectionA" _
(ByVal lpAppName As String, ByVal lpReturnedString As String, _
ByVal nSize As Long, ByVal lpFileName As String) As Long
Declare Function WritePrivateProfileString Lib "Kernel32" Alias "WritePrivateProfileStringA" _
(ByVal lpAppName As String, ByVal lpKeyName As String, _
ByVal lpString As String, ByVal lpFileName As String) As Boolean

Public Const HKEY_LOCAL_MACHINE As Long = &H80000002
Public Const KEY_QUERY_VALUE As Long = &H1
Public Const REG_SZ = (1) 'Unicode nul terminated string

Sub MainSub()
' Set up the ACCPAC environment
Initialise_Session

' Get the current date
Dim theDate As Date
theDate = Date

' Set the report defaults to be the entire last month
Dim NumDay As Integer
Dim NumMonth As Integer
NumDay = Day(theDate)
NumMonth = Month(theDate)

Dim fromDate As Date
fromDate = CDate(DateAdd("d", -(NumDay - 1), DateAdd("m", -1, theDate)))

Dim toDate As Date
toDate = CDate(DateAdd("d", -NumDay, theDate))

Dim frmMain As New frmInvoiceSummary
frmMain.dateFrom.Value = fromDate
frmMain.dateTo.Value = toDate
frmMain.Show
Set frmMain = Nothing
End Sub

Public Sub Initialise_Session()
On Error GoTo ErrorHandler

' An elegant way to know the installation directory is to check the registry.
' However, for unknown reasons, this won't work in NT environment.
' Therefore I just assume that the macro is started from "${strAccpacDir}\Runtime" directory.

' Retrieve the installation directory of ACCPAC
Dim hKeyResult As Long
Dim cbData As Long
Dim nType As Long

RegOpenKeyEx HKEY_LOCAL_MACHINE, _
"Software\ACCPAC INTERNATIONAL, INC.\ACCPAC\Configuration" & vbNullChar, _
0, KEY_QUERY_VALUE, hKeyResult

strAccpacDir = String(1024, vbNullChar)
cbData = 128
nType = REG_SZ
RegQueryValueEx hKeyResult, "Programs" & vbNullChar, 0, nType, strAccpacDir, cbData
RegCloseKey hKeyResult

Dim nPos As Long
nPos = InStr(1, strAccpacDir, vbNullChar)
If nPos > 1 Then
strAccpacDir = Trim(Left(strAccpacDir, InStr(1, strAccpacDir, vbNullChar) - 1))
ChDir (strAccpacDir & "\Runtime&quot
Else
strAccpacDir = InputBox("Please specifiy the installation directory of your ACCPAC package.&quot
' strAccpacDir = CurDir()
' strAccpacDir = Left(strAccpacDir, InStrRev(strAccpacDir, "\&quot - 1)
End If

Set session = ACCPACXAPILib.session
Prepare_Rpt_Ini

Exit Sub

ErrorHandler:
MsgBox Err.Description
End
Resume Next
End Sub

'Read the ARINVSUM.INI and append a section for BAS reports if not exist..
Public Sub Prepare_Rpt_Ini()
On Error GoTo ErrorHandler

Dim iApp As Integer
Dim strIniFile As String
Dim strPgmId As String
Dim strPgmVer As String

' ***********************************
' **** ERROR BELOW ******************
With session.ActiveApplications
For iApp = 0 To .Count - 1
If .Item(iApp).PgmID = "AR" Then
Exit For
End If
Next iApp
If iApp = .Count Then
Exit Sub
End If
strPgmId = .Item(iApp).PgmID
strPgmVer = .Item(iApp).PgmVer
strIniFile = strAccpacDir & "\" & .Item(iApp).PgmID & .Item(iApp).PgmVer & "\ARINVSUM.INI"
End With

Dim strSection As String
Dim strReportName As String
Dim strKey As String
Dim strValue As String
Dim nLenSection As Integer

'Check section ARINVSUM
strSection = String(4096, vbNullChar)
nLenSection = Len(strSection)
strIniFile = strIniFile & vbNullChar
strReportName = "ARINVSUM" & vbNullChar
GetPrivateProfileSection strReportName, strSection, nLenSection, strIniFile
strSection = Trim(Left(strSection, InStr(1, strSection, vbNullChar) - 1))

If Len(strSection) = 0 Then
'ARINVSUM does not exist.
strKey = "crystal" & vbNullChar
strValue = strAccpacDir & "\" & strPgmId & strPgmVer & "\ENG\SQLS\ARINVSUM.rpt" & vbNullChar
WritePrivateProfileString strReportName, strKey, strValue, strIniFile

strKey = "orientation" & vbNullChar
strValue = "landscape" & vbNullChar
WritePrivateProfileString strReportName, strKey, strValue, strIniFile

strKey = "paper size" & vbNullChar
strValue = "1" & vbNullChar
WritePrivateProfileString strReportName, strKey, strValue, strIniFile
End If

Exit Sub

ErrorHandler:
HandleError
Exit Sub

FileNotExists:
Exit Sub
End Sub

'Error handling routine.
Public Sub HandleError()
Dim Errors As xapiErrors
Dim Error As Variant

Set Errors = session.Errors
If Errors.Count = 0 Then
MsgBox Err.Description
Else
For Each Error In Errors
MsgBox Error.Description
Next
Errors.Clear
End If
Set Errors = Nothing
End Sub
-------------------------------------------


David M. Williams
National I.T. Manager
Advantage Personnel Pty. Ltd.
AUSTRALIA

 

[zemp]

First off, I think that the "bas.abv" is sent worldwide as a sample. Mine says "' Australian Business Activity Statement" and I am on the wrong side of the equator.

From what I understood you just want to call an exsisting report and pass a couple of parameters. The code I posted is basically all you need. My macro has one form and a few controls. The other code in the form just encompasses the control events and helps smooth the user interface. I placed an icon, where I wanted, on the main ACCPAC screen and it calls my one form macro to allow the user to select the criteria and print the report. Because it is the COMAPI run within ACCPAC I don't have to worry about opening a session, it just uses the current session opened by the system manager.

I'll be honest, I have not really looked into the bas.avb macro. But my experience with macros has always told me that the code they use is often overkill and can be simplified. Below is the entire code for my macro.

Option Explicit

Private Sub cmbSalesFrom_Change()
    Dim sPerson As ACCPACXAPILib.xapiView
    Set sPerson = Session.OpenView("AR0018", "AR")
    
    sPerson.Fields("CODESLSP").PutWithoutVerification Trim(cmbSalesFrom.Text)
    Dim x As Boolean
    x = sPerson.Read
    If x = True Then
        lblPFrom.Caption = Trim(sPerson.Fields("NAMEEMPL").Value)
    Else
        lblPFrom.Caption = ""
    End If
        
    Set sPerson = Nothing
End Sub

Private Sub cmbSalesTo_Change()
    Dim sPerson As ACCPACXAPILib.xapiView
    Set sPerson = Session.OpenView("AR0018", "AR")
    
    sPerson.Fields("CODESLSP").PutWithoutVerification Trim(cmbSalesTo.Text)
    Dim x As Boolean
    x = sPerson.Read
    If x = True Then
        lblPTo.Caption = Trim(sPerson.Fields("NAMEEMPL").Value)
    Else
        lblPTo.Caption = ""
    End If
        
    Set sPerson = Nothing
End Sub

Private Sub cmdCancel_Click()
    End
End Sub

Private Sub cmdProcess_Click()
 On Error GoTo ACCPACErrorHandler
    
    Dim PersonFrom As String, PersonTo As String
    Dim BegDate As String, EndDate As String
    Dim SepPage As Integer
    
    PersonFrom = Trim(cmbSalesFrom.Text)
    PersonTo = Trim(cmbSalesTo.Text)
    BegDate = Format(dateFrom.Value, "yyyymmdd")
    EndDate = Format(dateTo.Value, "yyyymmdd")
    If chkDifPage.Value = True Then SepPage = 1
    
    Dim CommisionReport As ACCPACXAPILib.xapiReport
    Set CommisionReport = CreateObject("ACCPAC.xapiReport")
    CommisionReport.Select Session, "ARSPCOMM", ""
    CommisionReport.SetParam "StartSP", PersonFrom
    CommisionReport.SetParam "EndSP", PersonTo
    CommisionReport.SetParam "StartDate", BegDate
    CommisionReport.SetParam "EndDate", EndDate
    CommisionReport.SetParam "Page", chkDifPage
    
    If optFile.Value = True Then CommisionReport.PrintDestination = PD_FILE
    If optHTML.Value = True Then CommisionReport.PrintDestination = PD_HTML
    If optPreview.Value = True Then CommisionReport.PrintDestination = PD_PREVIEW
    If optPrinter.Value = True Then CommisionReport.PrintDestination = PD_PRINTER
        
    CommisionReport.PrintReport 1
    
  Exit Sub

ACCPACErrorHandler:
  Dim Error As Variant

  If Errors.Count = 0 Then
    MsgBox Err.Description
  Else
    For Each Error In Errors
      MsgBox Error.Description
    Next
    Errors.Clear
  End If

  Resume Next

End Sub

Private Sub UserForm_Initialize()
    Dim sPerson As ACCPACXAPILib.xapiView
    Set sPerson = Session.OpenView("AR0018", "AR")
    
    Do Until sPerson.Fetch = False
        Me.cmbSalesFrom.AddItem sPerson.Fields("CODESLSP").Value
        Me.cmbSalesTo.AddItem sPerson.Fields("CODESLSP").Value
    Loop
    cmbSalesFrom.ListIndex = 0
    cmbSalesTo.ListIndex = cmbSalesTo.ListCount - 1
    Set sPerson = Nothing
    
    optPrinter.Value = True
End Sub

I hope I am able to clear things up a bit.


Take Care,

zemp

"Show me someone with both feet on the ground and I will show you someone who can't put their pants on."

 

[DavidMWilliams]

You really are being tremendously helpful - I'm very grateful to you.

I'm impressed by the bas.abv being a worldwide sample; we're so used to North American code samples that when I saw it say "Australian tax office" I just assumed it had to be localised!

You are completely right in your understanding of what I want to achieve (just call a report, passing parameters). I think I've got lost trying to work out how to manage the ini file (and just what it is needed for, in the first place).

I'll trim things back down and concentrate on getting the report running and then come back to these other matters.

I'll report back here soon!! Thanks!

David M. Williams
National I.T. Manager
Advantage Personnel Pty. Ltd.
AUSTRALIA

 

[DjangMan]

Just a note to future readers that the excellent examples shown above are xapi examples. Some of the syntax changes when running the via the COM interface.

Some of the nice things in the COM interface is that you don't have to do much coding for finders anymore and you can pull the print destination that the user has set in ACCPAC.

Updating the xxRPT.INI file is nice - one day I'd like to code a more elegant way of updating the file - there's a lot of repeated code and that code should go into its own module.

Cheers to all,

DjangMan

 

[hahsia]

Hi,

Not sure if it is ok to post my question here but i'm also currently working on macro to call crystal report for accpac.
My question is how do i set my macro to check what is the current printing option set in "Print Destination" in Accpac.
I want my report to be print, preview or ... based on the
print destination settings set in Accpac.

Zemp
----
I found your code could help but noq quite sure what is optfile, optHTML, optPreview & optPrinter is set to?:-

If optFile.Value = True Then CommisionReport.PrintDestination = PD_FILE
If optHTML.Value = True Then CommisionReport.PrintDestination = PD_HTML
If optPreview.Value = True Then CommisionReport.PrintDestination = PD_PREVIEW
If optPrinter.Value = True Then CommisionReport.PrintDestination = PD_PRINTER


Is there any library that i have to reference or declare>
Please Help.
Thanks In Advance.

 

[zemp]

To check the current printing option you can look at the xAPIReport objects' .PrintDestination property. For example innthe code posted above you can add code similar to below to check,

Select Case CommisionReport.PrintDestination 
   Case 1 
      msgbox "To Printer"
   Case 2
      msgbox "To File"
   Case 3
      msgbox "To HTML"
   Case 4
      msgbox "To Preview"
End Select

For your second question, 'optFile' is one of four option buttons. They appear on the macro's form and allow the user to select a print destination for the report. All the code is doing is determining which optioj button the user has selected and then setting the print destination accordingly. That section of code is not required to create or print the report. If omitted the report object will use the default value set by the system manager.

Hope this clears things up.

Take Care,

zemp

"Show me someone with both feet on the ground and I will show you someone who can't put their pants on."

 

[hahsia]

Thanks Zemp for clearing things up.

What i wanted to do is to use the settings set in Print Destination. I believed you aware that in Accpac, we can set our Print Destination to print, preview, ....

Let say i set it to print, all other accpac reports when i run will automatically prints directly to printer.

Is it possible for me to check what is the current settings and when user runs my crystal report macro, it will directly prints or preview depending on the user Print Destination settings in Accpac?
Please Advice.
Thanks In Advance.

 

[DjangMan]

Hi Hahsia,

If you are using the COMAPI interface (available in versions 5.x) in your macro then you can access what the current printer settings are and make your report act the same way.

Here's an example using the COMAPI interface from one of my macros:

Public mDBLinkCmpRW As AccpacCOMAPI.AccpacDBLink
Public ReportObject As AccpacCOMAPI.AccpacReport
Public PrintSetup As AccpacCOMAPI.AccpacPrintSetup

... some code here ...

Set mDBLinkCmpRW = OpenDBLink(DBLINK_COMPANY, DBLINK_FLG_READWRITE)
Set PrintSetup = mDBLinkCmpRW.Session.GetPrintSetup("", "")

Set ReportObject = mDBLinkCmpRW.Session.ReportSelect("VMQDSITE", "", "VM")
    
With ReportObject
  .SetParam "FROMJOB", frmReports.texFromDocID.value
  .SetParam "TOJOB", frmReports.texToDocID.value
  .Destination = PrintSetup.Destination
  .NumOfCopies = 1
  .PrintReport
End With
    
    Set ReportObject = Nothing

 

[hahsia]

Hi All,

I tried the following code in my sampe macro program but hit session not open error. Can someone enlighten me as i'm really very new to writing macro. The macro stops at setdblink.... Please Advice. thanks!

Set mDBLink = OpenDBLink(DBLINK_COMPANY, DBLINK_FLG_READWRITE)
Set PrtSetup = mDBLink.Session.GetPrintSetup("", "&quot
Set RptObj = mDBLink.Session.ReportSelect("apvendlist", "", "AP&quot

With RptObj
.SetParam "VENDFR", VENDFR
.SetParam "VENDTO", VENDTO
.Destination = PrtSetup.Destination
.NumOfCopies = 1
.PrintReport
End With

 

[DjangMan]

Where are you running your macro from? The session variable is created automatically is ACCPAC is executing the macro. If you're running this code from within a VB program you have some other steps to perform first - such as creating a session object...

Why don't you start a new topic and we can address your issue there.

DjangMan

 

[newcmelgar]

How do I make my macro not use the session object? I would like to take it away because Accpac is the VBA host.

Private Sub PrintMyReport_Click()
Dim session As New ACCPACXAPILib.xapiSession
session.Open "ADMIN", "ADMIN", "MISYS", #2/9/2004#, 0

Dim rptobj As ACCPACXAPILib.xapiReport
Set rptobj = CreateObject("ACCPAC.xapiReport&quot
rptobj.Select session, "ARCUSTOMER", ""

rptobj.PrintDestination = PD_PREVIEW

rptobj.PrintReport 1

End Sub

Thanks,

Carlos

 

[zemp]

You have to use the session object. You can use the current one, if ACCPAC is open, or you can create your own. Either way it is the session that makes a connection to Systme Manager and threfore your ACCPAC data.

When placing a date in the open Session statement you don't need to surround the date with the '#' symbol. That is syntax for the MS Access Jet engine and will cause errors with ACCPAC. Try removing those symbols to create your own session object.

You can search this forum for other examples of opening a session object. Personally I always use the VBA function '

Date

'. This returns the current date and ensures that the opened session uses today's date.

session.Open "ADMIN", "ADMIN", "MISYS", Date, 0

Take Care,

zemp

"If the grass looks greener... it's probably because there is more manure."