VBA - Calling a Report using Visual Basic for Applications

David Meego - Click for blog homepageI 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:

Hybrid - Purchasing Terms & Conditions Example 

Hybrid - Adding Named Printers control to Reports using VBA

Hope you find this information useful.

David

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, https://opensource.org/licenses/ms-pl.html.)