Sign Up for Training |
Jet Express Support |
Jet Reports Company Site
Jet Reports Financials
Jet Professional
Jet Enterprise
Downloads
Community
Submit a Request
Give Feedback

Launching Jet Reports from VBA


Intermediate

Related Articles...

Overview

If you write macros in Excel or want to launch Jet from another application, you can launch Excel, update report options, refresh the report and print.  Below is some sample code that demonstrates how to do this.

Step-by-Step Process

  1. Start Excel

    Create a new macro subroutine and add an Object variable to allow you to create a new Excel instance.

    Sub JetUpdate()

    Dim XLApp as Object

    Set XLApp = New Excel.Application 

  2. Make Excel visible and interactive

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

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

    XLApp.Visible = True

    XLApp.Interactive = 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.

    XLApp.Application.Workbooks.Open ("C:\Program Files (x86)\JetReports\jetreports.xlam")

  4. Open the report workbook

    XLApp.Application.Workbooks.Open ("C:\MyReports\ReportName.xlsx")

  5. Update the report options

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

    XLApp.Application.Workbooks("ReportName.xlsx").Names.Item("DateFilter").RefersToRange.Value = "1/1/2018..3/31/2018"

  6. Run the Jet report

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

    XLApp.Application.Run "JetReports.xlam!JetMenu", "Run"

     

    In earlier versions, the menu option is named REPORT

    XLApp.Application.Run "JetReports.xlam!JetMenu", "Report"

  7. Print the Worksheet which contains the report

    If you want to help with viewer in printing the report, choose a worksheet and launch the print preview (the user can then print the report, if desired).

    XLApp.Application.Workbooks("ReportName.xlsx").PrintPreview


    To avoid the message asking if the workbook should be saved, you can mark it as already saved.

    XLApp.Application.Workbooks("ReportName.xlsx").Saved = TRUE

    Be careful with this. You are assuming that the workbook does not actually need to be saved.
  8. Close Excel

    Close Excel and end your macro

    XLApp.Quit

    End Sub


Was this article helpful?
0 out of 0 found this helpful
Have more questions? Submit a request

Comments