Subscribe via RSS Feed Connect on LinkedIn

Dynamics NAV 2013R2 Excel Buffer Part 2

18th October 2014 0 Comments

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.

Post 320

 

In the report properties I set the Processing Only Flag to Yes.

Post 321

 

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.

Post 305

 

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.

Post 306

 

The last global is a Function for Code to process the Excel Buffer after it has been populated.

Post 307

 

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.

Post 309

 

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.

Post 310

Post 304

 

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.

Post 311

 

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.

Post 322

 

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.

[download id=”280″]

Please Share this Post:

Leave a Reply

Spam protection by WP Captcha-Free