0

Launching Jet Reports from Navision

Jet Reports Historic Posts

July 11, 2008 08:47

None

Follow

Hello

I’m trying to launch JetReports from Navision, update the named ranges (for filtering), refresh the report and show it to the user.

This is my code:


fctRunJet() //XL Automation 'Microsoft Excel 12.0 Object Library'.Application //Workbook Automation 'Microsoft Excel 12.0 Object Library'.Workbook //Worksheet Automation 'Microsoft Excel 12.0 Object Library'.Worksheet IF CREATE(XL, TRUE) THEN BEGIN XL.Interactive := TRUE; XL.Visible := TRUE; XL.Workbooks.Open('c:\Program Files\JetReports\JetReports.xlam'); Workbook := XL.Workbooks.Open('c:\test.xlsx'); Workbook.Names.Item('namedrange').RefersToRange.Value := FORMAT("No."); //”Sales Header”.”No.” XL.Run('JetMenu','Report'); XL.Run('Events'); END;

This is my testreport’s NL function:

=NL("rows";"sales header";"No.";"No.";$D$3)

I see the Report do what it is supposed to do, but after calculating I get this error:

The formula on sheet "result" in address $D$6 cannot be used with NL(Rows), NL(Columns), or NL(Sheets)…

Does anyone has good code to launch JetReports from the Sales Order form, update a named range for filtering, refresh the report and show it to the user who can work with the report and save it if necessary?


Thanks in advance,
Karel

• Facebook
• Twitter
• LinkedIn

6 comments

• 0
  
  Jet Reports Historic Posts July 11, 2008 08:51

  I want to add a question to this thread:
  
  How do i build this code to work in an environment with multiple versions (XP, 2007) and languages (NL, FR) of Excel?

• 0
  
  Jet Reports Historic Posts July 11, 2008 09:23

  looks pretty much ok to me…
  
  What is the value in D3? Does it look good?
  
  For use in another version, just save it as a 2003 excel (.xls) and you'll be fine.
  
  For use with diffirent languages is another matter:
  English works fine for most Excel (sum, avg,…). With Jet, however, it is possible that if you install another language jetreports, the report won't work (I always install english unless asked otherwise).

• 0
  
  Jet Reports Historic Posts July 11, 2008 09:54

  $D$3 is a NP(eval) function to get the value from the named range.

• 0
  
  Jet Reports Historic Posts July 11, 2008 09:58

  According to the error message, the one that goes wrong is (=NL("rows";"sales header";"No.";"No.";$D$3))?
  Since it's such an easy formula, the only thing that could be wrong is the filter ($D$3).
  
  You should just have a Sales Header No. there…

• 0
  
  Jet Reports Historic Posts July 11, 2008 10:37

  There is nothing wrong with the filter…. i get the same error message when i use this function:
  
  =NL("rows";"sales header";"No.";"No.";2001)
  
  (where 2001 is a Sales Header No.)
  
  
  The problem in not in the JetReport anyway, since I can run it manually without problems…. it only starts acting up when i run it from NAV…

• 0
  
  Jet Reports Historic Posts July 11, 2008 11:16

  I came up with a new idea, which is working great: no more excel automation, but using a dynamically generated batch file and Autopilot:
  
  //Batch File handling filBat.TEXTMODE(TRUE); filBat.WRITEMODE(TRUE); IF FILE.EXISTS(txtBatch) THEN FILE.ERASE(txtBatch); filBat.CREATE(txtBatch); //Generate content of Batch File: txtContent := txtBaseDir + 'AutoPilot.exe' + ' /M UPDATE' + ' /I ' + txtInput + ' /O ' + txtOutput + ' /L ' + txtLog + ' /P ' + txtNamedRange1 + ' ' + FORMAT("No."); //Write the Batch file to disk filBat.WRITE(txtContent); filBat.CLOSE; //Run the Batch file (shell) intOK := fctDosShell(txtBatch,1,TRUE); //Open the report when it's finished HYPERLINK(txtOutput);