Stephen Smith's Blog

Musings on Machine Learning…

Reporting Via Macros

with 62 comments


With our Sage 300 ERP 2012 release we updated our Crystal Reports runtime to the newest Crystal 2011 runtime (SP3 actually). The intent is to move to a fully supported version of Crystal Reports, so as they adapt to things like Windows 8 and Windows 2012 Server, we know we are fully supported and can get updates for any problems that show up. Plus it means that people customizing reports can take advantage of any of the new features there.

For reports you can print to preview, print to file or print directly to a printer. Then we have various options for printing from various web contexts like Quotes to Orders. You can drive reports from our regular forms, or you can write VBA macros that automate the reporting process.

This blog post is really for people that are controlling printing reports programmatically and are more affected by the changes in the Crystal runtime and more specifically changes in the Crystal Reports API.

Headache for Customizers

Our intent was that people performing customizations will use our API to drive Crystal Reports. Then your programs are upgrade safe since we maintain compatibility of our COM API. However it turns out that quite a few people have automated the report process by writing to the Crystal COM API directly.

This then leads to a problem because Crystal dropped support for their COM API. Not only did they drop support for it, but they removed it completely from the product. Hence anyone that is writing directly to the Crystal COM API will be broken by the Sage 300 ERP 2012 release. At least for new installs. If you had an older version and don’t un-install it, then you can still use the older version of the Crystal runtime (since it will be still there), but that isn’t a good long term solution as people upgrade computers and go to newer operating systems like Windows 8.

Crystal Reports now only supports a .Net Interface and a Java interface. For this version we had to change our internal interface to Crystal from COM to .Net. (The newer Web portal parts use the Java interface and so were ok).

Printing without User Intervention

It appears that one of the common reasons to go to the Crystal API directly is to print to file without any manual intervention. Often if you choose File as a print destination then we prompt you for the format and then prompt you for the file name to save to. People want to set these programmatically. Our API does have the ability to do this in a couple of situations.

Below is a macro I recorded to print O/E Quotes. I deleted any extra code, like the error handler to make it a bit more compact. Then I edited the destination and format to change the print destination to PD_FILE and the format to PF_PDF.

Sub MainSub()
Dim temp As Boolean
 Dim rpt As AccpacCOMAPI.AccpacReport
 Set rpt = ReportSelect("OEQUOT01[OEQUOT01.RPT]", "      ", "      ")
 Dim rptPrintSetup As AccpacCOMAPI.AccpacPrintSetup
 Set rptPrintSetup = GetPrintSetup("      ", "      ")
 rptPrintSetup.DeviceName = "HP LaserJet P3010 Series UPD PS"
 rptPrintSetup.OutputName = "WSD-ad0e8bc6-396c-4e50-84c7-fab17beaf18a.006a"
 rptPrintSetup.Orientation = 1
 rptPrintSetup.PaperSize = 1
 rptPrintSetup.PaperSource = 15
 rpt.PrinterSetup rptPrintSetup
 rpt.SetParam "PRINTED", "0"              ' Report parameter: 4
 rpt.SetParam "QTYDEC", "0"               ' Report parameter: 5
 rpt.SetParam "SORTFROM", " "             ' Report parameter: 2
 rpt.SetParam "SORTTO", "ZZZZZZZZZZZZZZZZZZZZZZ"   ' Report parameter: 3
 rpt.SetParam "SWDELMETHOD", "3"          ' Report parameter: 6
 rpt.SetParam "PRINTKIT", "0"             ' Report parameter: 7
 rpt.SetParam "PRINTBOM", "0"             ' Report parameter: 8
     ({OEORDH.PRINTSTAT} = 0) OR ({OEORDH.PRINTSTAT} = -1))"   ' Report parameter: 0
 rpt.NumOfCopies = 1
 rpt.Destination = PD_FILE
 rpt.Format = PF_PDF
 rpt.PrintDir = "c:\temp\quote.pdf"
End Sub


Basically this technique will silently export to PDF files. The Format member also accepts PF_RTF which will export silently in RTF format. The file they export to is specified in the PrintDir property. If this is a folder, the filename will be the same as the report name, if it’s a filename, it will be that (make sure you get the extension right).

You can also export silently to HTML format by setting the Destination to PD_HTML. For HTML if there is one file then the PrintDir specifies the filename, but sometimes HTML required multiple files, in which case it will use the PrintDir as a folder name.

These are a few ways you can print reports to a file silently without user intervention.

Secret Parameters

In the recorded macro above, you might notice the strange parameter “@SELECTION_CRITERIA”. If you look in the report, there is no such report parameter. Basically our API lets you set report parameters and then print the report. However there are a few other things you might want to do with the Crystal Reports API. Below is a list of these special parameters that might help you get a grip on some more aspects of the Crystal Reports API:

“@SELECTION_CRITERIA”: PESetSelectionFormula (job, asParamValue). This parameter is changed into the API to set the Selection Criteria in the report.

“@SELECTION_CRITERIA_xxxx”:  where  xxxx is the name assigned to the subreport when it was first created. This call will be translated into

job = PEOpenSubreport( parentJob, xxxx)
PESetSelectionFormula (job, asParamValue)

To set the selection criteria in the designated subreport.

“@SELECTION_ADDCRITERIA”: will add the parameter specified to the selection criteria that exists inside the report.

“@TABLENAME”: The parameter value must be in the form:

“table” “name”

Each instance of “table” will be switched to “name” before each PESetNthTableLocation call. This is done for both the main report and all subreports. You must put the table and name in quotes or the parameter will be rejected. Table names are not treated as case sensitive.

“EMAILSENDTO”:   The call will be set the email address when you are using PD_EMAIL.  This will suppress the popping up of the address book dialog.

“EMAILSUBJECT”: The call will be set the email subject when you are using PD_EMAIL.

“EMAILTEXT”: This parameter will be set the email body when you are using PD_EMAIL.

“EMAILPROFILE”:  This parameter will be set the email profile when you are using PD_EMAIL.

“EMAILPROFILEPWD”: This parameter will be set the email password when you are using PD_EMAIL. This password will be used to sign-in to MAPI.

The following are some parameters that if you create them in your report, then the system will automatically set them without you having to do any programming.

“CMPNAME”: The company name.

“ACCPACUSERID”: The Sage 300 User ID.

“ACCPACSESSIONDATE”: The session date from signon.

“ALIGNMENT”: The current alignment option.

“REGIONALFMT” : The current regional format.


We went through this Crystal API pain once before when Crystal dropped their DLL interface (crpe32.dll). At that point we had to change all API access over to COM. At that point we had similar issues due to various people using the DLL interface and now having to re-code for the COM interface. At that point we did add some functionality to our API, namely we added the TABLENAME special parameter, so that a number of people could start using our API.

Hopefully most people can switch from using the Crystal COM API directly to using the Sage 300 ERP API, however if something is missing, please comment on this blog, so we can consider expanding our API in the future. I’ve already gotten a couple of requests to add silently exporting to Excel format in addition to RTF and PDF. Keep in mind that like we have gone from DLL to COM to .Net, chances are in a few versions, the Crystal API will change again (perhaps to a REST web services API) and we will have to go through this again.


Written by smist08

September 15, 2012 at 7:50 pm

62 Responses

Subscribe to comments with RSS.

  1. will Sage remove the requirement with xxrpt.ini in the future so we can pass parameter directly to Crystal without the need of ini modification?


    September 16, 2012 at 2:51 am

    • This is a good suggestions, noted.


      September 20, 2012 at 3:01 am

  2. Hi,

    Does the Sage 300 ERP 2012 contain full Crystal Report 2011 .NET library? We have a few external applications using Crystal Reports .NET library, and distributing the runtime as a part of standard installation. It will be fine if the runtime will be already there:)


    September 17, 2012 at 6:30 am

    • It should be. We install the standard Crystal Runtime redistributable package and then use the .Net library to run reports ourselves.


      September 17, 2012 at 4:39 pm

  3. Just 3 questions:

    1) We clearly prefer to use the Sage 300 API for reporting, but we sometimes have to add tables to Sage 300 databases. The Sage 300 API seems to have trouble with the data source for these tables, so we have to use the Crystal API directly to get around this. Are we missing something in the Sage 300 API, or does this mean we will have to migrate to a .NET solution?
    2) Some of our customers do not or cannot have a MAPI client, or we have unattended processes resulting in emailed documents. Therefore there is a frequent need for email via SMTP. Will this be available in the Sage 300 API?
    3) Not sure what the @TABLENAME parameter is used for. In what circumstances would it be used? Better still, rather than keep asking here, is there a reference source for this sort of information for all those who use the API but are not on the developer program?


    September 19, 2012 at 10:06 am

    • 1) The intent is that we only set the login information for the tables not in our data dictionary, so that we don’t interfere with other tables (usually existing in another database). This might currently be broken, so beware. This way people can hard code the information for a given site in the report or a data source.
      2) Currently no plans, but probably not a bad idea. This sort of thing is why people want the silent export to file, so they can export the reports from our API then attach and send them through emails throw another means.
      3) This is intended for when you dynamically create tables, so if say you create a new table each day like tmonday, then you can dynamically set the report to the correct table at runtime.


      September 20, 2012 at 3:00 am

  4. Hi Stephen

    Just a quick question on the Crystal Runtime, I thought the new version allowed us to make use of .RPTR files to block modifications by customers/third parties. I’ve tried this but it doesn’t work, I just get an error in Accpac when I try to run the report. Am I missing something?

    I saw this stated in the new release notes but I can’t remember if it’s on launch or due in a future product update for Accpac.


    Andy Pickup

    September 22, 2012 at 10:27 am

  5. Crystal Report Web and Java viewer (v13 & 14) has known issue with displaying unicode for some languages (e.g.: Turkish, Vietnamese, Russian…) while ActiveX viewer still display properly.
    Is it fixed in this 2011? If it’s still there, our clients won’t be able to upgrade to ERP 300 2012 since your viewer will be .NET viewer isnt it?


    September 25, 2012 at 4:51 am

    • I mean .NET and Java viewer..


      September 25, 2012 at 4:52 am

    • We don’t work with those particular languages. But we do provide the Chinese (Simplified and Traditional) versions. These have been fine. For the Web Portal, the version of the Java Report Runtime and HTML viewer are the same as in 6.0A. The new Crystal 2011 .Net viewer is built off of the old ActiveX viewer, so I would hope its ok. I think the Crystal Reports team does a good job of addressing international issues, is I think it will be ok. But as always its best to test out your situation yourself.


      September 25, 2012 at 8:16 pm

  6. A way to programmatically find out the parameters a report has would be useful. We can read the ini file but if the report is not in the ini file (eg a custom report) then we can’t.

    Dan Gibson

    October 12, 2012 at 4:53 am

    • That’s a good point. In the old days,CARET didn’t have a way to do that. But the Crystal API certainly does. Another work around is to run macro recording when you print the report.


      October 12, 2012 at 4:03 pm

  7. We are using an ACCPAC VB macro to produce some custom Crystal reports. In previous versions there would be a pop up box that would show the progress of each crystal report as it was being produced. With sage erp 2012 that screen no longer appears so our users wonder if anything is actually happening when the macro is running. Is there a way to get it back?

    Andy Bruner

    April 10, 2013 at 3:47 pm

    • I passed this posting on to the developers maintaining the Crystal integration since I think this is a bug that needs fixing.


      April 12, 2013 at 5:10 pm

      • thanks!

        Andy Bruner

        April 12, 2013 at 6:01 pm

      • Will there be any way to get an update from the dev team at some point?

        Andy Bruner

        April 15, 2013 at 12:34 pm

      • Just following up again. Can you find out if this will be fixed in the next version? Our users are still complaining to me about it. Thanks.

        Andy Bruner

        September 5, 2013 at 6:53 pm

  8. […] These can be either Crystal Reports of Financial Reports. I blogged on printing through macros here and a bit more information on customizing reports here. Plus from the Business Logic you have […]

  9. What is the best way to run a report from the Sage 300 Desktop that uses stored procedures as a datasource? Currently all I get is a “Cannot open SQL Server” error and the DBSPY shows that it cannot find the table Proc(MyProc;1). if using VBA what is the best way to use as much of standard Sage 300 functions to do this without having to store DB logon details outside of Sage 300?

    Thanks in advance

    Jacques Correia

    July 15, 2013 at 12:37 pm

    • What we do is set the database credentials for any table in our data dictionary. Ie for any table created via our API. Then we leave everything else alone. The intent here is that you can link to other databases or anything else you want. The downside is that you do have to store the DB logon information in the report.


      July 15, 2013 at 3:26 pm

      • Thank you, appreciate the feedback. Sage 300 does not seem to ignore the stored procedure and then fails the complete report. Could be an issue because it is a procedure call. If it ignored it we could go the route of setting logon in the report. I am looking at options for a report launcher that uses windows authentication as I do not want to maintain logon info separately from Sage 300. If I could get access to the SQL logon detail that Sage uses then that would also be an option.

        Jacques Correia

        July 16, 2013 at 12:47 pm

  10. In the above recorded macro to print O/E quotes, how could I alter the macro to allow the user to enter FROMSELECT and TOSELECT parameter values? I tried by creating variables and setting them using InputBox, but ran into problems I can’t solve.

    Chuck Emery

    August 28, 2013 at 4:35 pm

    • There aren’t parameters for FROMSELECT or TOSELECT in this report. You would need to ask for these and get them in variables then build a new @SELECTION_CRITERIA parameter string where these are substituted into the correct places.


      August 28, 2013 at 5:10 pm

  11. The CHM file shows nothing in the right panel on my 64 bit Windows 7 computer. What do I need to add to see it?

    My associate can’t find any Macro information in the Sage 300 User Guides version 5.4 to 6.1, could you be more specific where these chapters can be found?


    Chuck Emery

    August 30, 2013 at 5:00 pm

    • If you look in one of the older versions like 5.5A there is a “Sage Accpac SM User Guide”.pdf which contains a “Chapter 13: Using Macros” and “Chapter 14: Writing Visual Basic Macros”. I think the file is being blocked by Windows security, probably because it sees it has been downloaded from the Internet. Make sure its on a local drive (Windows help files won’t load from a shared or network drive) and perhaps try using a cmd prompt to copy it to another folder (so Windows forgets you downloaded it).


      August 30, 2013 at 7:54 pm

      • I’ve found the 5.5A document. Thanks very much. As to the .CHM file, I have a suspicion that what gets downloaded, ACCPACControlsRef.chm, and ACCPACControlsRef.chw, which are 1 and 0.1 Mb in size, are not the full thing, so no solution putting them anywhere special. What I’d like is the file, which I can see the name of using the URL above, but how can I download it?

        Chuck Emery

        August 31, 2013 at 5:10 pm

  12. I’ve downloaded it with current firefox using the File Download commands on the site. Moved it to folder and extracted ACCPACControlsRef.chw and now see the same error code provided from earlier downloads. In the right hand panel:

    “Navigation to the webpage was canceled

    What you can try:
    Retype the address. ”

    No idea what address to retype or where

    is there an alternative place where a working copy of this file might be available, or is there a supporting website which has been taken down? Sorry for all the trouble.

    Chuck Emery

    August 31, 2013 at 5:17 pm

  13. That does it. At last.

    Thank you

    Chuck Emery

    September 1, 2013 at 3:19 am

  14. I am having trouble with a macro similar to your recorded macro, using Sage ERP 300 2012, and wonder if you can help.

    I have inserted an input box to allow the user to set the starting and ending order numbers, and I wish any previously printed orders to reprint.

    The macro works the first time only, the second and subsequent attempts using the same order numbers yield a blank report and an error: SAGE ERP 300 VBA has encountered a problem and needs to close…

    My feeling is that I need to use the ReInit() function, but not sure where to insert it into the code.

    Sub MainSub()

    ‘ TODO: To increase efficiency, comment out any unused DB links.
    Dim mDBLinkCmpRW As AccpacCOMAPI.AccpacDBLink

    Dim mDBLinkSysRW As AccpacCOMAPI.AccpacDBLink

    ‘ Input Box
    Dim FromNum As String
    FromNum = InputBox(“Starting Order”, “Print Picking Slips”)
    Dim ToNum As String
    ToNum = InputBox(“Ending Order”, “Print Picking Slips”, FromSelect)

    Dim temp As Boolean
    Dim rpt As AccpacCOMAPI.AccpacReport

    Set rpt = ReportSelect(“OEPICK01[C:\USERAPPS\SAGE\SAGE ACCPAC\OE61A\ENG\OEPICKORDER2.RPT]”, ” “, ” “)

    Dim rptPrintSetup As AccpacCOMAPI.AccpacPrintSetup
    Set rptPrintSetup = GetPrintSetup(” “, ” “)
    rptPrintSetup.DeviceName = “Brother HL-2270DW series”
    rptPrintSetup.OutputName = “BRW008092889918”
    rptPrintSetup.Orientation = 1
    rptPrintSetup.PaperSize = 300
    rptPrintSetup.PaperSource = 7
    rpt.PrinterSetup rptPrintSetup
    rpt.SetParam “SELECTBY”, “0” ‘ Report parameter: 2
    rpt.SetParam “SORTBY”, “1” ‘ Report parameter: 3
    ‘rpt.SetParam “FROMSELECT”, “ORD000000000067” ‘ Report parameter: 4
    ‘rpt.SetParam “TOSELECT”, “ORD000000000067” ‘ Report parameter: 5
    rpt.SetParam “FROMLOC”, ” ” ‘ Report parameter: 6
    rpt.SetParam “TOLOC”, “ZZZZZZ” ‘ Report parameter: 7
    rpt.SetParam “PRINTBY”, “0” ‘ Report parameter: 14
    rpt.SetParam “SERIALLOTNUMBERS”, “0” ‘ Report parameter: 15
    rpt.SetParam “PRINTKIT”, “0” ‘ Report parameter: 11
    rpt.SetParam “PRINTBOM”, “1” ‘ Report parameter: 12
    rpt.SetParam “REPRINT”, “1” ‘ Report parameter: 8
    rpt.SetParam “QTYDEC”, “0” ‘ Report parameter: 9 – O/E Sales History:Detail,Sort by Item Number
    rpt.SetParam “COMPLETED”, “0” ‘ Report parameter: 10
    rpt.SetParam “SESHNDL”, “861909” ‘ Report parameter: 13
    rpt.NumOfCopies = 3
    rpt.Destination = PD_PREVIEW
    rpt.PrintDir = “”

    End Sub


    September 5, 2013 at 10:57 pm

  15. Hi Stephen,

    I am trying to use the ODBC.command (Sql command line for datasource) in crystal report for accpac.
    I notice that the datasource of the sql command line do not change by company when running the report from diff company.

    Can this feature in crystal be used in accpac reporting?


    May 4, 2014 at 4:44 pm

    • This is probably something that our API doesn’t know about, so won’t touch it. You might try using @SELECTION_CRITERIA instead.


      May 4, 2014 at 5:23 pm

  16. How could I suppress the generation of an exported PDF file if there are no records?


    May 7, 2014 at 3:14 pm

    • All I can think of is to check beforehand using the API to see if there are any records to be reported and if not then don’t run the report process.


      May 7, 2014 at 3:31 pm

      • Thanks – amd just another thing, I guess I need to then update the record in OEINVH to reflect that the report has been printed?


        May 7, 2014 at 3:33 pm

      • If there is an invoice to be marked as printed, then wouldn’t something be in the PDF?


        May 7, 2014 at 3:37 pm

      • Sorry… I had changed the subject. 🙂 New question, after printing a document that needed printing, (i.e. INVPRINTED (in the case of OEINV) was 0 at running the macro, after the macro runs, I would need to update the INVPRINTED = 1 so that it did not print the next time the macro ran. Sorry for the confusion. And thanks for a great blog. Very useful info.


        May 7, 2014 at 3:41 pm

      • Then yes marking them as printed would be good.


        May 7, 2014 at 3:46 pm

  17. When trying to automate this using a small vb.NET program I am having trouble with the equivilent for these two statements in vb.NET.

    Set rpt = ReportSelect(“OEQUOT01[OEQUOT01.RPT]”, ” “, ” “)


    Set rptPrintSetup = GetPrintSetup(” “, ” “)

    Are you able to help with the proper way to express this in vb.NET ?



    May 7, 2014 at 8:46 pm

    • Worked it out.

      Dim mSession As New ACCPAC.Advantage.Session
      mSession.Init(“”, “XY”, “XY1000”, “61A”)
      mSession.Open(“ADMIN”, “ADMIN”, “SAMINC”, DateTime.Today, 0)

      rpt = mSession.ReportSelect(“OEINV01[OEINV01.RPT]”, ” “, ” “)
      rptPrintSetup = mSession.GetPrintSetup(” “, ” “)


      May 7, 2014 at 11:50 pm

  18. hi Stephen

    please can you help me i tried the below code but give me exception in line 4 when i try to GetPrintSetup

    1 Dim rpt As AccpacCOMAPI.AccpacReport
    2 rpt = Session.ReportSelect(“OEORDACS[OEORDACS.rpt]”, ” “, ” “)
    3 Dim rptPrintSetup As AccpacCOMAPI.AccpacPrintSetup
    4 rptPrintSetup = Session.GetPrintSetup(” “, ” “)



    February 5, 2015 at 6:56 am

    • the exception message :Error Hresult E_FAIL has been returned from a call to a com component.


      February 5, 2015 at 6:58 am

  19. please stephen or anybody can help me?


    February 5, 2015 at 9:10 am

    • I copy/pasted your code into VBA and fixed it up for VBA and then it worked:

      Dim rpt As AccpacCOMAPI.AccpacReport
      Set rpt = ReportSelect(“OEORDACS[OEORDACS.rpt]”, “”, ” “)
      Dim rptPrintSetup As AccpacCOMAPI.AccpacPrintSetup
      Set rptPrintSetup = GetPrintSetup(” “, ” “)

      rpt.PrinterSetup rptPrintSetup

      Not sure if this will help you in your environment.


      February 8, 2015 at 2:45 am

      • thanks for Reply bro


        February 8, 2015 at 11:15 am

  20. I am struggling to find an explanation of the paper size and paper source properties. What do this integers relate to? If they are both set to 1 (which is the first option in crystal and relating to auto) will the report print with the report file settings? Does the paper size in the oerpt.ini override this setting? I was also wondering what the difference between selecting the report using ReportSelect(“OEORDACS[OEORDACS.rpt]”,””,””) and ReportSelect(“OEORDACS”,””,””) which also seems to work. Thanks in advance.

    Sam Elmer

    March 4, 2015 at 12:03 am

  21. Hey Stephen,
    We’ve been writing our own reports using SQL Server Report Server for years; we use these to email to customers as well as for our own staff to analyze our business. Every time we upgrade Accpac we have to check for changes to the schema. I’m wondering if there is a straightforward way to do this – presumably you have documents that track every changed detail of your schema; providing that information would make it that much easier for us to perform our regular upgrades. Did I miss something that is already available?


    March 26, 2015 at 10:53 pm

    • Each version (and some product updates) have a database and report changes document. You should be able to find this under the docs folder.


      March 26, 2015 at 10:57 pm

  22. Hey,

    I seem to be struggling to set the output file name for a PD_EMAIL pdf to send.

    I’d like to set a PO file name e-mailed to PONUMBER.pdf, but it’s coming up as a ~tempNNN.pdf file

    Is there a listing of PD_ table options that might help me?

    Also, is there an easy way to do a printer selection dialog to select which printer will be used? (name, driver and port)


    James C

    July 3, 2015 at 11:30 pm

  23. Hi Stephen,
    Would you be able to share what properties or methods the AccpacCOMAPI.AccpacReport object has please.
    Specifically I’m looking for a method to set a custom column width when exporting a Crystal Report to Excel in VBA.

    I used the Crystal Reports API before Accpac 6.2 and here this was simple.

    report.ExportOptions.DestinationType = crEDTDiskFile
    report.ExportOptions.FormatType = crEFTExcel97
    report.ExportOptions.ExcelExportAllPages = True
    report.ExportOptions.ExcelUseConstantColumnWidth = True
    report.ExportOptions.ExcelConstantColumnWidth = 15000
    report.ExportOptions.ExcelShowGridlines = True

    In Sage 300 ERP 2014 I need ACCPACCOMAPI and I don’t seem to have the same options available.
    When printing the report from Accpac however, I’m able to set a column with and show gridlines etc. as part of the export screen.

    Dim rptObj As AccpacCOMAPI.AccpacReport
    Set rptObj = ReportSelect(“c:\AccpacData\DATA\Custom_report.rpt”, ” “, ” “)

    rptObj.Destination = PD_FILE
    rptObj.Format = PF_XLS
    rptObj.PrintDir = “c:\temp\test.xls”

    Would really appreciate your help.


    October 7, 2015 at 2:36 pm

    • Pardon me to cut in, but for my case

      rpt.NumOfCopies = 1
      rpt.Destination = PD_FILE
      rpt.Format = PF_XLS
      rpt.PrintDir = “C:\tec2.xls”

      This does not work. I got directed to a prompting screen on selection of filetype, location and so on.


      rpt.NumOfCopies = 1
      rpt.Destination = PD_FILE
      rpt.Format = PF_PDF
      rpt.PrintDir = “C:\tec2.pdf”

      works like a charm.

      Please advise

      I am on Sage ERP Accpac 500 (Version 6.0A)


      November 20, 2015 at 2:19 am

  24. Hi Stephen,
    I tried to generate Accpac PDF invoice from Sage CRM. Here below is the testing code. It is running without error but does not generate the PDF file. Is there something I missed or it won’t support it this way?

    sub AccpacRptPdf(Accpac)
    Dim rpt
    Set rpt = Accpac.ReportSelect(“ARDINVO[ARDINVO.RPT]”, ” “, ” “)
    ‘Dim rptPrintSetup
    ‘Set rptPrintSetup = Accpac.GetPrintSetup(” “, ” “)
    ‘rptPrintSetup.DeviceName = “HP LaserJet P205X series PCL6 C”
    ‘rptPrintSetup.OutputName = “”
    ‘rptPrintSetup.Orientation = 1
    ‘rptPrintSetup.PaperSize = 1
    ‘rptPrintSetup.PaperSource = 15
    ‘rpt.PrinterSetup rptPrintSetup
    rpt.SetParam “SELECTBY”, “1” ‘ Report parameter: 9
    rpt.SetParam “FROMBATCH”, “1” ‘ Report parameter: 0
    rpt.SetParam “TOBATCH”, “1” ‘ Report parameter: 0
    rpt.SetParam “FROMENTRY”, “1” ‘ Report parameter: 0
    rpt.SetParam “TOENTRY”, “1” ‘ Report parameter: 0
    rpt.SetParam “FROMCUST”, ” ” ‘ Report parameter: 0
    rpt.SetParam “TOCUST”, “ZZZZZZZZZZZZ” ‘ Report parameter: 0
    rpt.SetParam “FROMDOCNBR”, ” ” ‘ Report parameter: 0
    rpt.SetParam “TODOCNBR”, “ZZZZZZZZZZZZZZZZZZZZZZ” ‘ Report parameter: 0
    rpt.SetParam “FCURNDEC”, “3” ‘ Report parameter: 0
    rpt.SetParam “ADDR01”, “123 Sample Company Plaza” ‘ Report parameter: 2
    rpt.SetParam “ADDR02″, ” ” ‘ Report parameter: 3
    rpt.SetParam “ADDR03″, ” ” ‘ Report parameter: 4
    rpt.SetParam “ADDR04″, ” ” ‘ Report parameter: 5
    rpt.SetParam “CITY”, “Any City” ‘ Report parameter: 6
    rpt.SetParam “STATE”, “Any Province” ‘ Report parameter: 7
    rpt.SetParam “POSTAL”, “V6J 9T3” ‘ Report parameter: 8
    rpt.SetParam “@SELECTION_CRITERIA”, “{ARIBH.CNTBTCH} in 1 to 1 and {ARIBH.CNTITEM} in 1 to 1 and {ARCUS.CATEGORY} = 0 and {ARIBH.ERRBATCH} = 0 and {ARIBH.ERRENTRY} = 0” ‘ Report parameter: 0
    rpt.NumOfCopies = 1
    rpt.Destination = PD_FILE
    rpt.Format = PF_PDF
    rpt.PrintDir = “C:\\01\\quoteaaabb.pdf”
    ‘rpt.PrintDir = “c:\accpac\accpac\data\USER\ADMIN”
    end sub

    Andrew Liu

    November 17, 2015 at 3:59 pm

    • Perhaps debug it in full VB first? I would wonder if the file name should be “C:\01\quote.pdf”? I don’t think you need the double backslashes in VB, only in C. Also make sure the 01 folder exists first.


      November 17, 2015 at 7:20 pm

      • Thank you for reply. I tested in VBA macro and works. The double backslashes is my testing. it was single backslash and not working.

        Andrew Liu

        November 17, 2015 at 7:32 pm

  25. Hi Stephen,
    I am testing using vbscript asp page to generate Accpac report. The code below works without error but does not generate the PDF file. please help what I missed.

    Andrew Liu

    November 17, 2015 at 4:54 pm

  26. Hi

    I am on Sage ERP Accpac 500 (Version 6.0A)

    Mypart of the code for excel is not working Am I missing a line somewhere ?
    rpt.NumOfCopies = 1
    rpt.Destination = PD_FILE
    rpt.Format = PF_XLS
    rpt.PrintDir = “C:\tec2.xls”


    November 20, 2015 at 2:32 am

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: