Walkthrough: Using Automation to Create a Graph in Microsoft Excel

Article
10/18/2021

In this walkthrough, you will transfer data from the G/L Entry table to Microsoft Office Excel and create a graph. This example shows how to handle enumerations by creating a graph in Excel that shows the distribution of personnel expenses by departments. In the Chart of Accounts, you can see that Total Personnel Expenses is the total of accounts 8700 to 8790. In the Dimension Value table, you can see that there are three departments: ADM, PROD, and SALES. You will create a codeunit that retrieves this data from the G/L Entry table, transfers it to Excel, and creates a graph that is similar to the one that is shown in the following figure:

You will run the codeunit directly from Object Designer. In a real application, you would call it from an appropriate place, such as from a menu in the Chart of Accounts window.

About This Walkthrough

This walkthrough illustrates the following tasks:

Creating a codeunit that declares the Automation variables that are required for using Excel Automation.
Adding C/AL code to the codeunit to run the Automation object that opens Excel.
Adding C/AL code to the Automation codeunit to transfer data from a table record to Excel.
Adding C/AL code that creates a graph in Excel.

Prerequisites

To complete this walkthrough, you will need:

Microsoft Dynamics NAV 2013 with a developer license.
The CRONUS International Ltd. demo data company.
Microsoft Office Excel 2010, Microsoft Office Excel 2007, or Microsoft Excel 2003.
The language in the regional settings of your computer matches the language version of Microsoft Office Excel.

This is a known issue. For more information, see Bug: "Old Format or invalid type library" in the Microsoft Support site.

Creating the Codeunit and Declaring Variables

To implement Automation in a codeunit, you define the Automation variables. To define an Automation variable, you specify an Automation server and the Automation object.

To create the codeunit and declare variables

In Object Designer, choose Codeunit, and then choose the New button to create a new codeunit.
On the View menu, choose C/AL Globals.

On the Variables tab, add the following variables:

Note

For the Automation data type variables, the subtype Microsoft Excel 11.0/12.0 Object Library defines the Automation server, and the class specifies the Automation object of the Microsoft Excel 11.0/12.0 Object Library. Microsoft Excel 11.0 or 12.0 Object Library.

Name	Data type	Subtype	Class
GLEntry	Record	G/L Entry
xlApp	Automation	Microsoft Excel 11.0, 12, or 14.0 Object Library	Application
xlBook	Automation	Microsoft Excel 11.0, 12, or 14.0 Object Library	Workbook
xlSheet	Automation	Microsoft Excel 11.0, 12, or 14.0 Object Library	Worksheet
xlChart	Automation	Microsoft Excel 11.0, 12, or 14.0 Object Library	Chart
xlRange	Automation	Microsoft Excel 11.0, 12, or 14.0 Object Library	Range

Close the C/AL Globals window.

Adding the Code

Now you add the code for the codeunit.

To add the code

In the C/AL Editor, set the key for the G/L Entry table, and then use SETFILTER to select the accounts by adding the following code to the OnRun trigger.

GLEntry.SETCURRENTKEY("G/L Account No.","Business Unit Code", 
"Global Dimension 1 Code","Global Dimension 2 Code","Posting Date");
GLEntry.SETFILTER("G/L Account No.",'8700..8790');

Create an instance of Excel by adding the following code.
```
CREATE(xlApp, FALSE, TRUE);
```
Add a new workbook to Excel.
```
xlBook := xlApp.Workbooks.Add(-4167);
xlSheet:= xlApp.ActiveSheet;
xlSheet.Name := 'Personnel Expenses';
```
The following describes the code:
- In the first line, you use the Add method of the Workbooks collection to return a new workbook. The attribute -4167 is the enumerator value of worksheets as they apply to Workbook objects. For more information, see About the Enumerator Value.
- In the second line, you use the ActiveSheet property of the Application class to ensure that what is done next affects the active sheet of the new workbook.
- In the third line, you use the Name property to name the sheet.

About the Enumerator Value

In the preceding procedure, the Add method includes the attribute -4167. In the Microsoft Excel Visual Basic online Help, the Add method as it applies to the Workbooks object takes one Template argument, which is of type VARIANT. The description in the Help says:

If this argument is a constant, the new workbook contains a single sheet of the specified type. Can be one of the following: XlWBATemplate constants: xlWBATChart, xlWBATExcel4IntlMacroSheet, xlWBATExcel4MacroSheet, or xlWBATWorkSheet.

Instead of passing xlWBATWorkSheet as the Template argument, you are passing the number -4167. In this case, the Template argument, which is of type VARIANT, is an enumeration. Enumerations are either USERDEF types or not. This is not a USERDEF type, so it looks like a VARIANT in the C/AL Symbol Menu. You have to look in the Microsoft Excel Visual Basic Help to figure out that it is actually an enumeration. Arguments can be constants with names like xl* in Excel, wd* in Word, and ol* in Outlook. In Microsoft Dynamics NAV, you cannot use the symbolic name xlWBATWorkSheet. You have to use the enumerator -4167.

To find an enumerator value

Open Excel and do one of the following steps to open the Visual Basic Editor.
- In Excel 2010, on the Developer tab, choose Visual Basic.
  
  Note
  
  If you do not see the Developer tab, then in the File menu, choose Options, and then Customize Ribbon. Under Customize the Ribbon, choose Developer, and then choose OK.
- In Excel 2007, on the Developer tab, choose Visual Basic.
  
  Note
  
  If you do not see the Developer tab, then choose the Microsoft Office Button in the upper left corner, and then choose Excel Options. In the Popular category, under Top options for working with Excel, select the Show Developer tab in Ribbon check box, and then choose OK.
- In Excel 2003, on the Tools menu, choose Macro, and then choose Visual Basic Editor.
On the View menu of Visual Basic Editor, choose Object Browser.
In the Project/Library list in the list box in the upper-left corner of the Object Browser, select Excel.
In the Classes list, select XlWBATemplate.
In the Members of ‘XlWBATemplate’ list, select xlWBATWorkSheet. You can see the value in the information pane at the bottom of the Object Browser. In the following example, the value is -4167..

Const xlWBATWorksheet = -4167 (&HFFFFEFB9)

Transferring Data

To transfer the data, you must calculate the data and transfer the results of the calculation.

To transfer data

In the C/AL Editor, on the codeunit, use SETRANGE to filter the entries in the G/L Entry table on the Global Dimension 1 Code field. The first department is ADM (Administration). Then, you use CALCSUMS(Amount) to get the sum for the ADM department. To do this, add the following code.
```
GLEntry.SETRANGE("Global Dimension 1 Code", 'ADM');
GLEntry.CALCSUMS(Amount);
```

To transfer the data to Microsoft Excel, add the following code.

xlSheet.Range('A2').Value := 'Administration';
xlSheet.Range('A3').Value := GLEntry.Amount;

Add this code for the PROD and SALES departments:

GLEntry.SETRANGE("Global Dimension 1 Code", 'PROD');
GLEntry.CALCSUMS(Amount);
xlSheet.Range('B2').Value := 'Production';
xlSheet.Range('B3').Value := GLEntry.Amount;
GLEntry.SETRANGE("Global Dimension 1 Code", 'SALES');
GLEntry.CALCSUMS(Amount);
xlSheet.Range('C2').Value := 'Sales';
xlSheet.Range('C3').Value := GLEntry.Amount;

Save and compile the codeunit.

Creating the Graph

The final step is to create the graph. You will use the ChartWizard method to create a 3-D pie chart. This is a fast and simple way to do it. You can more tightly control the design of the graph by setting it up using the methods and properties of the various Chart objects, such as ChartArea and Legend.

To create the graph

In the C/AL Editor, on the current codeunit, define a range for the data in the graph.
```
xlRange := xlSheet.Range('A2:C3');
```

Add a new chart sheet and give it a name.

xlChart := xlBook.Charts.Add;
xlChart.Name := 'Personnel Expenses - Graph';

Create the graph.

xlChart.ChartWizard(xlRange,-4102,7,1,1,0,0,'Personnel Expenses');

Use the following optional arguments in the ChartWizard method.

Argument	Description	Value in method call
Source	The range that contains the source data for the new chart.	xlRange – The object returned by xlSheet.Range(‘A2:C3’).
Gallery	The chart type.	-4102 – The enumerator for the xl3DPie XlChartType enumeration.
Format	The option number for the built-in auto formats.	7
PlotBy	An integer specifying whether the data for each series is in rows or columns.	1 – The enumerator for the xlRows XlRowCol enumerator.
CategoryLabels	An integer specifying the number of rows or columns within the source range that contains category labels.	1 – There is one row with category labels (the department names).
SeriesLabels	An integer specifying the number of rows or columns within the source range that contains series labels.	0 – There are no series labels in your data.
HasLegend	TRUE to include a legend.	0
Title	VARIANT with the title of the chart.	You pass a string such as ‘Personnel Expenses’.

Make Excel visible by adding the following code.
```
xlApp.Visible := TRUE;
```
Excel produces a General Protection Fault error when you close a new Excel worksheet that is created when Excel is invisible. To resolve this, you can make Excel visible immediately after you create a new worksheet. You can also make Excel visible just before you create a new Excel worksheet and then make it invisible again immediately after creating the new Excel worksheet. In this case, you would add the following code.
```
xlApp.Visible := TRUE;
xlBook := xlApp.Workbooks.Open(FileName);
xlApp.Visible := FALSE;
```

Saving and Running the Codeunit

You can test the codeunit for creating the graph by running the codeunit from Object Designer.

To save and run a query

On the File menu, choose Save.
In the Save As window, enter an ID and name, and then choose the OK button.
In Object Designer, select the codeunit, and then choose the Run button.

The Microsoft Excel graph should appear.

Note

If you get an error states Old format or invalid type library, then make sure that the language in the regional settings of your computer matches the language version of Microsoft Excel. For more information, see Bug: "Old Format or invalid type library".

Share via