VBA – Calling a Report using Visual Basic for Applications

David Meego - Click for blog homepageThis is a reposting of an article I originally wrote on my Developing for Dynamics GP blog.

I was recently asked about a forum post of mine from a few years ago. It was about how to call a Dexterity report from Visual Basic for Applications (VBA).  I checked the blog and could not find a post that showed the code in its simplest form.  So here one is…

This technique used the unsupported method of running Dexterity sanScript by calling the Continuum Integration Library. To use this code you must have VBA or at least the customisation site license registered.

The code can be used to run any report (built-in or custom) that does not use a temporary table.  If the report does use a temporary table, then when you run the report without the supporting Dexterity code, the table will be empty and so the report will be missing data.

Here is an example of the code (including getting the Named Printer for Custom Reports).
Example VBA Code

'Dim CompilerApp As New Dynamics.Application
Dim CompilerApp As Object
Dim CompilerMessage As String
Dim CompilerError As Integer
Dim Commands As String' Create link without having reference marked
Set CompilerApp = CreateObject("Dynamics.Application")Commands = ""

' Get Named Printers settings
Commands = Commands & "local 'Printer Settings' NamedPrinter; " & vbCrLf
Commands = Commands & "NamedPrinter = ST_Set_To_Default_Printer(8, ST_SY_CUSTOM); " & vbCrLf

' Run the report with optional restriction, index and legends
Commands = Commands & "run report with name ""Custom Report Name Here"" " & vbCrLf
'Commands = Commands & " with restriction 'Field Name' of table Table_Name > 0 " & vbCrLf
'Commands = Commands & " by number 1" & vbCrLf
'Commands = Commands & " legends ""Test Legend"" " & vbCrLf

' Swap comments on line below to send report direct to printer instead of screen
Commands = Commands & " destination true, false " & vbCrLf
'Commands = Commands & " destination false, true " & vbCrLf

Commands = Commands & " printer NamedPrinter " & vbCrLf
Commands = Commands & " in dictionary 0; " & vbCrLf
'MsgBox Commands

' Set Context
'CompilerApp.CurrentProductID = 0 ' 0 = Dynamics
'CompilerApp.CurrentProduct = CompilerApp.CurrentProduct & "!Modified"

' Execute SanScript
CompilerError = CompilerApp.ExecuteSanscript(Commands, CompilerMessage)
If CompilerError <> 0 Then
  MsgBox CompilerMessage
End If

To define which dictionary the report is in adjust the “in dictionary” clause of the code.  To change the context that the entire Dexterity pass through script is executed in un-comment the lines following the “Set Context” comment and adjust accordingly. The syntax for restrictions, sorting by indexes and adding legends are included in the template code and just need to be un-commented.

Have a look at these related posts for more Report Writer and Named Printers & VBA related information:

Hope you find this information useful.


15-Apr-2010: To make the restriction work based on a field on a window, you can either use Dexterity (only if the field exists on the original window) or VBA (must add field to Visual Basic). For example:

Dexterity (table and window field datatypes match):

Commands = Commands & ”  with restriction ‘Field Name’ of table Table_Name = ‘Field Name’ of window ‘Window Name’ of form ‘Form Name’ ” & vbCrLf 

VBA (string field):

Commands = Commands & ”  with restriction ‘Field Name’ of table Table_Name = “”” & VBA_Field & “”” ” & vbCrLf

VBA (numeric field):

Commands = Commands & ”  with restriction ‘Field Name’ of table Table_Name = ” & VBA_Field & ” ” & vbCrLf

// Copyright © Microsoft Corporation.  All Rights Reserved.
// This code released under the terms of the
// Microsoft Public License (MS-PL, http://opensource.org/licenses/ms-pl.html.)

This article was originally posted on the Developing for Dynamics GP Blog and has been reposted on http://www.winthropdc.com/blog.

16 thoughts on “VBA – Calling a Report using Visual Basic for Applications

  1. That’s great David…good on ya…but how can i call the cutom report from a print button on a dynamics GP window.The report should be restricted by one of the fields appearing on this window.


  2. When attaching the code above to a print button on a window ,the code doesn’t recognize the table name.an error comes up "syntax error" when clicking on the print button.
    I am trying to have a custom report printed according the data displayed in the window (which is being saved to a work table).
    I also tried to restrict the report by changing the table field to the window field as suggested by patrick but that didn’t work either.


  3. My fault – I didn’t look at the example close enough and I mislead you a bit in my response.
    The "field name of table" part has to stay since that is the restriction of the GP table itself and used in the report.  It is the expression that is changed.  In the sample, the value was arbitrarily set to ">0", you need to modify the expression to be your window field.
    So you need to wrap your window field value into the expression so that the value of the window field is now part of the string itself to be passed to the report in Dexterity.
    While David likes to use pass through sanScript from VBA (because it can be very useful), you really have to be fluent in Dexterity and a somewhat fluent in VBA in order to use any of these methods beyond just "cut and paste".


  4. Well,thanks patrick.
    I really like david’s script.
    I have created a vb form with one text field and I need to have the report restricted by the value entered in this field.
    I am really keen to learn how this works .Do you recommend any resources I should get back to.


  5. Hi Mike
    I have updated the post with example methods of setting the restriction based on a window field, either using Dexterity or VBA.
    To learn more about Dexterity, see:
    To learn more about Visual Basic for Applications, see the training materials, links at bottom of this page:
    Hope this helps.


  6. Hi there I have used the code above to call either one report (RM Blank Document) or another (RM Blank Document1)  based on the Document Type on the Receivables Transaction Entry screen but the report is coming out blank. I have used restrictions to pass document number.   Can anybody help?  See code below.
     'Dim CompilerApp As New Dynamics.Application   Dim CompilerApp As Object   Dim CompilerMessage As String   Dim CompilerError As Integer   Dim Commands As String
       ' Create link without having reference marked   Set CompilerApp = CreateObject("Dynamics.Application")   Commands = ""
       ' Get Named Printers settings   Commands = Commands & "local 'Printer Settings' NamedPrinter; " & vbCrLf   Commands = Commands & "NamedPrinter = ST_Set_To_Default_Printer(8, ST_SY_CUSTOM); " & vbCrLf
       ' Run the report with optional restriction, index and legends   If DocumentType = "4" Then       Commands = Commands & "run report with name ""RM Blank Document1"" " & vbCrLf   Else       Commands = Commands & "run report with name ""RM Blank Document"" " & vbCrLf   End If   Commands = Commands & "  with restriction 'Document Number' of table RM_OPEN_TEMP = """ & Number & """" & vbCrLf   Commands = Commands & "  by number 1" & vbCrLf   'Commands = Commands & "  legends ""Test Legend"" " & vbCrLf
       ' Swap comments on line below to send report direct to printer instead of screen   Commands = Commands & "  destination true, false " & vbCrLf   'Commands = Commands & "  destination false, true " & vbCrLf   Commands = Commands & "  printer NamedPrinter " & vbCrLf   Commands = Commands & "  in dictionary 0; " & vbCrLf
       'MsgBox Commands   ' Set Context   CompilerApp.CurrentProductID = 0 ' 0 = Dynamics   CompilerApp.CurrentProduct = CompilerApp.CurrentProduct & "!Modified"
       ' Execute SanScript   CompilerError = CompilerApp.ExecuteSanscript(Commands, CompilerMessage)
       If CompilerError <> 0 Then       MsgBox CompilerMessage   End If
    End Sub


  7. Hi Jo
    Your problem is that the report's main table is a temporary table which requires code to populate it with data.  As the main table contains no records, the report will always be blank.  
    Note that pass through sanScript runs as though it is a global procedure with no parameters.  This means that no table buffers can be passed, which in turn means that any temporary tables referenced will be a new separate instance of that temporary table which will contain no data unless your code populates it.
    Have a look at this sample which populates a temp table.
    Make sure that when populating the temp table, that all the fields used by the report have values and not just the key fields.
    Hope this helps


  8. I need to execute the report from Dex, but with diferent restriction / sort orders.
    I have to execute the run report dynamically like in VBA or there is any other way?


  9. Hi Ariel
    Normally, running a report with different settings is handled using variables (to specify keys. etc.) and case or if statements to define different restrictions.
    You can also build the run report command as a string or text field dynamically and then use the execute() command to run the code at runtime, however, this method can be harder to debug and maintain moving forward.


  10. Thanks David for your answer.
    I've tried the execute method, but I get an empty report.
    What doy you mean with running the report using variables. Can you send me an example.
    Thanks a lot, Ariel


  11. Hi Ariel
    If your report is based on a temporary table, you will need to populate records into that table in your passthrough script.
    Have a look at the Purchasing Terms and Conditions example (link on this post).


  12. Hi David,
    I am trying to print Purchase Order through GP Add-in (new menu item under Additional menu) using c#. I am using sanScript to run "run report with name" command. I have even populated 2 fields PO number and PO doument print sequence of temp table "POP_PrintDocList_TEMP". But, the report is not showing all the information like PO line details, Vendor Ship to address, etc. Can you please help me on this? If you could provide me sample code to populate all the fields of "POP_PrintDocList_TEMP", then that would be great.


  13. I've tried this sanScript code from VBA and it works great to fire off a custom report for SOP from a button.  The report I have is really an additonal report that could be added to the Sales Document Print Options (like Invoice, Picking Tickets, and Packing Slips).  The problem is if I select all these reports to run to the screen  they stack up and don't actually run when you OK their destination but all run at the end of the process.  When I run my report via sanScript though it runs immediately.  Is there a way to make it delay and run with the others reports?


Please post feedback or comments

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

WordPress.com Logo

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

Google photo

You are commenting using your Google 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.