0

Excel macros

Jet Reports Historic Posts

May 27, 2009 18:19

None

Follow

Hi there.

I'm wondering if there is anybody out there who as gotten very creative with Jet and worked out a way to automatically run a VB macro after Jet has executed? (without a separate scheduled task to just run the macro from the output file).

cheers, Mark

• Facebook
• Twitter
• LinkedIn

6 comments

• 0
  
  Jet Reports Historic Posts June 02, 2009 20:54

  Anybody have any ideas at all?
  
  So far the best I can come up with is built in Excel events and a macro named 'auto_run'. I think I can make these work for me, but if there was some neat way in Jet to do it, that would be awesome.

• 0
  
  Jet Reports Historic Posts June 03, 2009 15:18

  What I've seen done is to a macro run the Jet Reports and then do what it needs to do after the Jet Reports is complete.

• 0
  
  Jet Reports Historic Posts June 03, 2009 15:43

  Hey chuck - do yo have an example of this or remember where you saw it?
  
  cheers

• 0
  
  Jet Reports Historic Posts June 03, 2009 17:23

  Sorry, I don't remember where I saw it, but here is some code from the support site that shows using VBA to open and run the report.
  
  http://support.jetreports.com/index.php?_m=knowledgebase&_a=viewarticle&kbarticleid=316
  
  Sub JetUpdate() Dim XLApp As Object Set XLApp = New Excel.Application 'Workbooks don't necessarily start 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 'Add-ins do not automatically open when using automation, so the Jet Reports add-in 'must be opened manually. You must also open any other add-ins that you need. XLApp.Application.Workbooks.Open ("C:\program files\JetReports\jetreports.xla") 'Open the report workbook XLApp.Application.Workbooks.Open ("ReportName.xls") 'Update the report options. The option values are in single cell named ranges. XLApp.Application.Workbooks("ReportName.xls").Names.Item("DateFilter").RefersToRange.Value = _ "1/1/2004..3/31/2004" 'Run the jet report. 'IMPORTANT NOTE: Jet Reports intends to keep the below command working in future versions 'of Jet Reports. All other menu commands might change. They might work now, but could 'break in future version. Please do not use any other Jet Reports commands in your software. XLApp.Application.Run "JetReports.xla!JetMenu", "Report" 'In some cases all the below steps may not be desired. 'Choose a worksheet and launch print preview. The user can cause the report to print if desired. XLApp.Application.Workbooks("ReportName.xls").PrintPreview 'Avoid the message asking if the workbook should be saved by marking it as already saved. 'Be careful with this. You are assuming that the workbook does not need to be saved since it is a report 'template. XLApp.Application.Workbooks("ReportName.xls").Saved = True 'Close Excel XLApp.Quit End Sub

• 0
  
  Jet Reports Historic Posts August 31, 2009 04:27

  i have run into a problem when using the code.
  
  everything work fine except it opens to input box that says "Run Report Now" instead of actually reporting.
  
  is there a code line that could be added to do this?
  
  -Bromy

• 0
  
  Jet Reports Historic Posts August 10, 2011 07:52

  I believe that is possible if you enter this in the "This Workbook"-section of the VBA-part in your XLSM-file:
  
  Private Sub Workbook_BeforeClose(Cancel As Boolean)
  
  Call <<MACRONAME>>
  
  End Sub