Monday, December 7, 2015

Export To Excel using Automation Variables / Create Excel in reports using Automation Variables in NAV

There are many ways to create a Excel from Report in the Microsoft Dynamics NAV 2015.

--> Using Excel Buffer table
--> Using Automation variable

Using of  Automation variables is faster way to create Excel in NAV 2015.

Required Global  variables:
Name DataType Subtype Length
xlapp Automation 'Microsoft Excel 15.0 Object Library'.Application
xlbook Automation 'Microsoft Excel 15.0 Object Library'.Workbook
xlsheet Automation 'Microsoft Excel 15.0 Object Library'.Worksheet

xlRange Automation 'Microsoft Excel 15.0 Object Library'.Range
j                  text                



ONPreReport Trigger()
  CREATE(xlapp,TRUE,TRUE);
  xlbook.Visible(TRUE);
  xlbook:=Excel.Workbooks.Add(-4167);
xlsheet:=Excel.ActiveSheet;

  xlsheet.Name := 'Sheet Name';
j='1';
xlsheet.Range('B'+'1').Value :='Name';

xlsheet.Range('A'+'1').Value :=  'Cust No.';
Above Code is common for creating a Excel To insert any values i.e from tables we have 
to write code on OnAfterGetRecord Trigger. As per our customizations

OnAfterGetRecord Trigger()
Simple code just comprising 2 field lets say customer NO and Name.
j := INCSTR(j);
xlsheet.Range('A'+j).Value := Customer."No.";

xlsheet.Range('B'+j).Value := Customer.Name;
----------x---------------x------------------x----------------x



For Customizing Excel add below code as per your requirement
Merge Column in Excel

Sheet.Range('A'+'1' + ':' + 'D'+'1').Merge;
Sheet.Range('A'+'1').Value :=  ' Excel_test';

Fill Colour in Excel Column

Sheet.Range('A'+'1').Interior.Color := 888888;


Font Bold

  Sheet.Range('A'+'1').Font.Bold := TRUE;


Font Italic  Sheet.Range('A'+'1').Font.Italic := TRUE;


Font Size  Sheet.Range('A'+'1').Font.Size := 10;

Border Cell  Sheet.Range('A'+'1').Borders.LineStyle := 0;

 SavaAs Excel
  Sheet.SaveAs('Excel_test.xlsx');

Password Protect Sheet Sheet.Protect('NAV');
--------------------------------------------XXX---------------------------------
For Any Queries please comment, Thank you.