Skip to main content

Launch the Jet Excel add-in from Dynamics NAV Classic Client

Intermediate

Overview

If you are a Dynamics NAV developer and have appropriate development granules, you can launch Excel from the NAV Classic Client, update report options, refresh the report, and print.

You must have the OCX granule and either Application Builder or Report Designer

Below is a code-unit example of how to do this.  The same code could be written inside a form or a Dynamics NAV report.

Global Variables

Name Data Type Subtype
XL Automation 'Microsoft Excel 10.0 Object Library'.Application
Workbook Automation 'Microsoft Excel 10.0 Object Library'.Workbook
Worksheet Automation 'Microsoft Excel 10.0 Object Library'.Worksheet

Step-by-Step Process

  1. Start Excel

    OnRun()

    {To reuse existing instances of Excel, use CREATE(XL)}

    IF CREATE(XL, TRUE) THEN BEGIN
  2. Make Excel visible and interactive

    {Workbooks don't necessarily start in a mode that is visible or interactive.

    Since the user will interact with the workbook, you need to set the following two values:}

    XL.Interactive := True;
    XL.Visible := True;
  3. Open the Jet add-in

    {Add-ins do not automatically open when using automation, so the Jet Excel add-in

    must be opened manually.  You must also open any other add-ins that you need}

    XL.Workbooks.Open('C:\Program Files (x86)\JetReports\jetreports.xlam');
  4. Open the report workbook

    {provide the full path name for the report to be opened}

    Workbook :=XL.Workbooks.Open('C:\MyReports\ReportName.xlsx');
  5. Update the report options

    {Update the report options.  The option values are in single-cell named ranges}

    Workbooks.Names.Item('PeriodType').RefersToRange.Value := 'Week';
    Workbook.Names.Item('DateFilter').RefersToRange.Value := '1/1/18..3/31/18';
  6. Run the Jet report

    In Jet Reports versions 2018 R2 (18.1) and higher, the menu option is named RUN

    {run the Jet report}

    XL.Run('JetMenu','Run');

    In earlier versions, the menu option is named REPORT

    {run the Jet report}

    XL.Run('JetMenu','Report');

    In VERY early versions (prior to Jet Reports 2009), you must also run the EVENTS macro:

    XL.Run('EVENTS');
  7. Print the Worksheet which contains the report

    {In some cases, not all of the below steps may be desired}

    {Choose a worksheet and launch the print preview (the user can then print the report, if desired)}

    Worksheet := Workbook.Worksheets.Item('Report');
    Worksheet.PrintPreview;

    {Avoid the message asking if the workbook should be saved by marking it as already saved}

    Workbook.Saved = TRUE;

    {Be careful with this. This step assumes that the workbook does not actually need to be saved}

  8. Close Excel

    {Close Excel and end the code unit}

    XL.Quit;
    End;

Related Articles

Was this article helpful?

We're sorry to hear that.