Dynamics NAV 2013R2 Excel Buffer Part 2
This Post is the second in a series of three on using the Microsoft Dynamics NAV Excel Buffer, if you have not read part one you can Read it Here, in this post I will look at creating a Report that I can use as a Template to enable me to import data from an Excel Worksheet, there is a “Download Button” at the foot of this post for the report in text format.
Creating a Template Report
There are no Layout Sections in the report therefore I will not need to have Visual Studio installed, I created a new Report with no data item, I will be doing all Processing in the On Pre-Report Trigger Section, I use the same Variable and Function Naming from the other Standard Dynamics NAV 2013R2 reports that Import from Excel.
In the report properties I set the Processing Only Flag to Yes.
C/AL Globals
For this report I create a few C/AL Globals, I prefer to make the Excel Buffer a Temporary Variable but it is not required just a personal choice as I have worked on databases with Custom Objects and have found the Excel Buffer populated with Old Data.
I use Text Constants to display a Progress Bar, to Me there is nothing more annoying that waiting for a process to run while not knowing how far it has got.
The last global is a Function for Code to process the Excel Buffer after it has been populated.
Request Page
To import the data from Excel I need to add a Local Variable and Code to the Request Page, the Variable I add will manage the Excel file, this Variable is a Local Variable in the On Query Close Page Trigger.
The Code in the “On Query Close Page” trigger is run when The User selects OK from the Request Page, a File Dialogue Opens to Select the File, returning the Server File Name to a Variable, then displaying a List of Worksheets to Select the Excel Worksheet into the Sheet Name Variable.
Report Code
The Server File Name and Sheet Name are used by the Excel Buffer Functions to Open the Worksheet and import the selected Excel Worksheet Cells into the Excel Buffer Table.
The last Action in this post is to add some Code in the AnalyzeData Function, this Code looks to see if there are any Rows to process starting from Row two, skipping the first row as this has the Column Headers.
Part 3
In Part Three I will show how I use a copy of the report to import data from Excel into the “Item Journal Line” you can read Part Three Here.