Import from Excel with Automation variables

Actulally this i tried and get success in NAV 2009 i hope this will work in future version also

Write this code in Report with empty DataItem because is processing only purpose

Import from Excel

Variables :

Name          DataType      Subtype        Length

SheetName   Text                250

FileName      Text                250

XlWrkSht     Automation Unknown Automation Server.Worksheet     

XlRange       Automation Unknown Automation Server.Range  

xa                Automation Unknown Automation Server.Application    

XlWrkb        Automation Unknown Automation Server.Workbook     

XlWrkshts    Automation Unknown Automation Server._Worksheet  

Maxi            Integer                     

Maxj            Integer                     

i                   Integer                     

Window       Dialog                       

CellValue      Text                250

j                   Integer                     

CommonDialogMgt           Codeunit       Common Dialog Management  

ExcelBuf      Record           Excel Buffer 

Ct                Integer                     

ExportToExcel         Boolean 

                   

Step-1 :  Create a Method OpenBook()

  IF FileName = '' THEN

    ERROR(Text001);

IF SheetName = '' THEN

  ERROR(Text002);

IF NOT EXISTS(FileName) THEN

  ERROR(Text003,FileName);

IF NOT CREATE(xa,TRUE,TRUE) THEN

  ERROR(Text000);

xa.Workbooks.Open(FileName);

XlWrkb := xa.ActiveWorkbook;

i := 1;

EndOfLoop := XlWrkb.Worksheets.Count;

WHILE (i <= EndOfLoop) AND (NOT Found) DO BEGIN

  XlWrkshts := XlWrkb.Worksheets.Item(i);

  IF SheetName = XlWrkshts.Name THEN

    Found := TRUE;

  i := i + 1;

END;

IF Found THEN

  XlWrkSht := XlWrkb.Worksheets.Item(SheetName)

ELSE BEGIN

  XlWrkb.Close(FALSE);

  xa.Quit;

  CLEAR(xa);

  ERROR(Text004,SheetName);

END;

Step-2 :

Write the following code in ON PreDataItem Section :

OpenBook(FileName,SheetName);

Window.OPEN(

  Text005 +

  '@1@@@@@@@@@@@@@@@@@@@@@@@@\');

Window.UPDATE(1,0);

XlRange := XlWrkshts.Range('c1').SpecialCells(11);

Maxi := XlRange.Row();

Maxj := XlRange.Column();

i := 1;

REPEAT

  CLEAR(CellValue);

  INIT;

  CellValue :=  FORMAT(XlWrkshts.Range('A'+FORMAT(i)).Value);

  CellValue :=  DELCHR(CellValue, '<=>','');

  EVALUATE("Weborder No.",FORMAT(CellValue));

  CLEAR(CellValue);

  CellValue :=  FORMAT(XlWrkSht.Range('B'+FORMAT(i)).Value);

  CellValue :=  DELCHR(CellValue, '<=>','');

  "NAVoredr No." := CellValue;

  CLEAR(CellValue);

  CellValue :=  FORMAT(XlWrkSht.Range('C'+FORMAT(i)).Value);

  CellValue :=  DELCHR(CellValue, '<=>','');

  EVALUATE("Shipping Cost",CellValue);

  CLEAR(CellValue);

  CellValue :=  FORMAT(XlWrkSht.Range('D'+FORMAT(i)).Value);

  CellValue :=  DELCHR(CellValue, '<=>','');

  EVALUATE("Shipping Charge",CellValue);

  INSERT;

  i := i+1;

  Window.UPDATE(1,ROUND(i / Maxi * 10000,1));

UNTIL i > Maxi;

XlWrkb.Close(FALSE);

xa.Quit;

CLEAR(xa);

Window.CLOSE;

Integration Tools

Microsoft Dynamics NAV 2009 provides several integration tools that can be used inside or outside of Microsoft Dynamics NAV to integrate to other applications.

Web Services

Web services are a standardized way for independent software systems to

communicate with one another over standard Internet protocols. Web services architecture is designed for dynamic program-to-program interaction.

Microsoft Dynamics NAV 2009 supports Web services, which makes it easy to integrate Microsoft Dynamics NAV with other systems. This is possible with the introduction of Microsoft Dynamics NAV Server.

There are several types of Web service in Microsoft Dynamics NAV 2009:

•The simplest Web service type is developed by using the page

object. By using the page object, Microsoft Dynamics NAV

constructs a default Web service that has a fixed set of methods.

Generally, it corresponds to general data access, such as get and set

individual values or retrieves and updates lists of values.

•Another Web service type involves including codeunits and the

functions from those codeunits in the Web service.

•The last type of Web service includes the ability to pass complex

data types by using an XMLport object as a parameter in a codeunit

function.

ODBC

Open DataBase Connectivity, or ODBC, is a standard database access method developed by Microsoft. The goal of ODBC is to make it possible to access any data from any application regardless of which database management system (DBMS) is handling the data. ODBC manages the access of data by inserting a middle layer called a database driver between an application and the DBMS.

The purpose of the middle layer is to translate the application’s data queries into commands that the DBMS understands. For this to work, both the application and the DBMS must be ODBC-compliant; that is, the application must be capable of issuing ODBC commands and the DBMS must be capable of responding to them.

C/FRONT

C/FRONT is an application programming interface that can be used to access a Microsoft Dynamics NAV database. C/FRONT facilitates high-level interaction with the Microsoft Dynamics NAV database and enables developers to manipulate any Microsoft Dynamics NAV database.

The central component of C/FRONT is a library of C functions. These functions provide access to every aspect of data storage and maintenance, and enable the integration of both standard and custom applications with a Microsoft Dynamics

NAV database.

C/FRONT comes as a DLL for direct linkage with a program and also as an OCX that a program can use.

OCX

OLE Custom control, or OCX, is an independent program module that can beaccessed by other programs in a Microsoft® Windows® environment. OCXcontrols end with an .ocx extension. OCX controls represent Microsoft’s secondgeneration of control architecture, the first being VBX controls written in VisualBasic.

Both VBX and OCX controls have now been superseded by Microsoft ActiveX®controls. However, ActiveX is backward compatible with OCX controls whichmeans that ActiveX containers such as Microsoft Internet Explorer can execute

OCX components.

There is a vast array of commercially available OCXs that perform a variety of

tasks and developers can produce their own. They can use tools such as

Microsoft Visual C++ or Visual Basic to create OCXs that can be called by

Microsoft Dynamics NAV.

** NAV Terminology

The following shows descriptions of several terms in the third column:

Properties:

Properties control the appearance and behavior of application objects and all sub-objects. Properties are used to control the appearance of data, specify

default values, specify colors, and define relationships.

C/AL:

C/AL is the language used for writing functions in C/SIDE. In the

previous table, C/AL refers to functions written in this language.

Triggers:

When specific things happen to the application objects, the system

automatically activates a trigger. Inside a trigger, developers can add C/AL code

if they want to modify the default behavior of the application object or extend its

functionality.

Fields:

A field is the smallest unit of information in the database. A field

typically stores information such as a name or a number.

Keys:

A key defines the order in which data is stored in the tables. Speed up

searches in tables by defining several keys to sort information in different ways.

Controls:

Controls are objects on a form or report that display data, perform

actions or decorate the form. Typical examples are command buttons and text labels.

Request Form:

A request form is a form used in a report or a dataport. Before a

report or a dataport is run, a request form appears to let the user specify filters and options for the report or the dataport.

Request Page

: A request page is the request form equivalent in the RoleTailored client.

Data Items:

A data item is a building block used for defining a model of data

when creating a report or a dataport. By using a hierarchy of data items,

developers define which data to include in the report. A data item represents a table and when a report is run, the system cycles through the records in the associated table. In a report, a data item can have one or more sections.

Sections:

A section is a substructure of a data item. A section is where controls are placed to display information. Generally, sections are used to define the body, header, and footer in the report.

NodeName:

NodeNames are used to specify the name of a node in an XML

document. The name specified is inserted in theNodeName field of the

XMLport Designer of the element or attribute in question.

NodeTypes:

This property is used to specify whether an XML object is an

element or an attribute.

Menu Node:

A Menu Node can be either a Menu Group or a Menu Item.

Menu Group:

A Menu Group is a collection of Menu Nodes.

Menu Item:

A Menu Item is the lowest level of the menu tree. It is associated

with a specific application object.

Is Microsoft Dynamics NAV Object Oriented ?

Microsoft Dynamics NAV is not object-oriented but object-based. This is an important distinction. In an object-oriented language or environment, developers can create new types of objects based on the ones that are already in the system.
In C/SIDE, developers have eight types of application objects and that is all.Developers can create and use Tables, Forms, Pages, Reports, Dataports,
XMLports, Codeunits, and MenuSuites, but nothing else.
Limiting developers to use these eight objects makes their work faster and more efficient. The biggest benefit from this limitation is stability. It is fairly difficult to create a severe bug in C/SIDE.

PAGE Object

Page and Action Triggers
Microsoft Dynamics NAV 2013

Page triggers allow you to use C/AL code to control the behavior of the system as a result of an event on the page, such as a page opening or a field changing its value. You typically use page triggers for advanced validation and logic.
Page triggers can be divided into three categories:

• General page triggers that apply to the entire page
• Field page triggers that apply to a field control on a page
• Action triggers that apply to an action on a page.

General Triggers
The following table lists triggers that apply to the entire page.

Page trigger name	Executed
OnInit Trigger	When the page is   loaded, but before the controls are available.
OnOpenPage Trigger	When the page is   initialized and the controls are available.
OnClosePage Trigger	When the page about to   close and after OnQueryClosePage Trigger trigger.
OnFindRecord Trigger	When the page is   opened and a record is retrieved from a table.
OnNextRecord Trigger	When the page changes   from displaying one record to another record in a table. For example, on a   Customer card page, this happens when a user selects Next (Ctrl+Page   Down) or Previous (Ctrl+Page Up).
OnAfterGetRecord Trigger	When a record has been   retrieved but not yet displayed.
OnNewRecord Trigger	When a new record has   been initialized but not yet displayed.
OnInsertRecord Trigger	When a new record is   about to be inserted in the table.
OnModifyRecord Trigger	When a record is about   to be modified in the table.
OnDeleteRecord Trigger	When a record is about   to be deleted from the table.
OnQueryClosePage Trigger	When the page is about   to close, but before the OnClosePage Trigger

Field Triggers
The following table describes the triggers that are available on field controls.

Control trigger	Executed
OnValidate (Page fields) Trigger	When the user changes   the value in a field and then selects away from the field so that the field   loses focus.
OnLookup (Page fields) Trigger	When the user requests   a lookup by clicking a field’s lookup button or pressing F4.
OnDrillDown Trigger	When the user requests   a drill-down by choosing the field’s drill-down button or pressing Shift+F8.
OnAssistEdit Trigger	When the user requests   assist-edit by choosing an AssistEdit button or by pressing Shift+F4.
OnControlAddin Trigger	When a control add-in   is initiated on a page.

Action Triggers
The following table lists triggers that apply to actions on a page.

Triggers	Executed
OnAction Trigger	When an action is   initiated on a page.

REPORT object

Report Triggers
The following table lists triggers that apply to the report itself.

Trigger	Executed
OnInitReport Trigger	When the report is   loaded.
OnPreReport Trigger	Before the report is   run, but after the RequestPage has been run.
OnPostReport Trigger	After the report has   run, but not if the report was stopped manually or by the QUIT Function (Report, XMLport).

In Report we define the Data Items and its triggers are as follows
Data Item Triggers
The following table lists triggers that apply to each data item on the report.
The Trigger executing style for Report already i published in my blog .

Trigger	Executed
OnPreDataItem Trigger	Before the data item   is processed, but after the associated variable has been initialized.
OnAfterGetRecord (Data Items) Trigger	When a record has been   retrieved from the table.
OnPostDataItem Trigger	When the data item has   been iterated for the last time.

XML PORT Object

Microsoft Dynamics NAV 2013
From NAV 2013 we can run XML port direclty.
The following triggers apply to XMLports.

XMLport trigger	Executed
OnAfterAssignField Trigger	Executed after a field has been assigned a value and before it   is validated and imported.This   trigger is only used to import data.
OnAfterAssignVariable Trigger	Executed after the value defined in the XML document is assigned   to the text variable.This   trigger is only used to import data.
OnAfterGetField Trigger	Executed after a field is passed to the XML document.This   trigger is only used to export data.
OnAfterGetRecord (XMLports) Trigger	Executed after a record is retrieved from a table and before it   is exported to the XML document.This   trigger is only used to export data.
OnAfterInitRecord Trigger	Executed after a record is loaded.This   trigger is only used to import data.
OnAfterInsertRecord Trigger	Executed after a record has been inserted into a database table.This   trigger is only used to import data.
OnBeforeInsertRecord Trigger	Executed after a record has been loaded and before it is   inserted into a database table.This   trigger is only used to import data.
OnBeforePassField Trigger	Executed before a field is passed to the XML document.This   trigger is only used to export data.
OnBeforePassVariable Trigger	Executed after the source expression has been formatted into a   text variable and before the text variable is passed to the XML document.This   trigger is only used to export data.
OnInitXMLport Trigger	Executes   when the XMLport is loaded and before any table views and filters are set.
OnPreXMLport Trigger	Executed   after the table views and filters are set and before the XMLport is run.
OnPostXMLport Trigger	Executes   after the XMLport is run.
OnPreXMLItem Trigger	Executed after the table is initialized and before you start   exporting data to an XML object. This trigger only applies to XMLport   elements that have a source type of Table.This   trigger is only used to export data.

Query Object

Query Triggers
 It is new Object introduced from Microsoft Dynamics NAV 2013

This topic describes the C/AL triggers that are available for queries. Triggers are typically used to perform calculations and verification. Triggers allow you to control how data is selected and retrieved in a more complex and effective way than you can achieve by using properties.

Query Object Triggers

The following table lists the triggers that apply to the query object.

Trigger	Executes
OnBeforeOpen	Before the   query object is run and the dataset is generated. For example, you can use   the OnBEforeOpen trigger to apply filters using the SETFILTER function.

Code Units

Codeunits  Used to organize and group code which is written by the developers

Codeunit Triggers
Microsoft Dynamics NAV 2013

The following triggers apply to codeunits.

Codeunit trigger name	Executed
OnBeforeTestRun Trigger	Before a   test function of a test codeunit is run.
OnAfterTestRun Trigger	After a   test function of a test codeunit is run.

FORMS

Forms Used to display data to users in the Microsoft Dynamics NAV Classic client. Forms allow users to add records to a table, and to view and modify records.

[ This object is not using from earlier version  of  NAV 2013]


OnInit
OnOpenForm
OnCloseForm
OnQueryCloseForm
OnActiveteForm

Different TABLES in NAV

Microsoft Dynamics NAV has three types of tables that serve special purposes. The types of tables are:

·         Temporary 

·         System 

·         Virtual

Temporary tables are used as a repository for temporary information at run time. System and virtual tables are system generated tables that provide information about the current state of the system.

Temporary Tables

A temporary table is a temporary variable that holds a table. A temporary table is used as a buffer for table data in your C/AL programs. For more information about C/AL, see Introduction to C/AL.

You can use a temporary table just like you use a database table. The differences between a temporary table and a database table are as follows:

·         A temporary table is not stored in the database, but is only held in memory until the table is closed.

·         The write transaction principle that applies to a database table does not apply to a temporary table. For more information, see Write Transactions and Recovery.

Advantage of a Temporary Table

The advantage of using a temporary table is that all the interaction with a temporary table occurs on the client. This reduces the load on both the network and the server.

When you want to perform many operations on the data in a specific table in the database, you can load the data into a temporary table when you modify it. Loading the data into a temporary table speeds up the process because all the operations are performed locally on the client.

Defining and Using a Temporary Table

You must define the temporary table before you can use it in your C/AL code. The variable that holds a temporary table is defined just like any other global or local variable.

To define a temporary table

1.       On the Tools menu, choose Object Designer, and then create a new table.

For information about how to create a table, see How to: Create a Table.

2.       Choose View, and then choose C/AL Globals or C/AL Locals, depending on whether your variable will be global or local.

If you choose C/AL Globals, the C/AL Globals window appears

3.       Enter a name for the temporary table variable, and enter or select Record as the data type. Use the up arrow in the Subtype field to select the table to copy.

4.       With the cursor still on the line that defines the temporary table, choose View, and then choose Properties (Shift+F4) to display the Properties window.

5.       Change the value of the Temporary property to Yes.

After you have created a temporary table, you can use it in your C/AL code. You can apply filters and perform searches just as you can with a database table.

System Tables

System tables are stored in the database just like normal database tables. However, unlike normal database tables, they are created automatically. The information in system tables is closely related to the DBMS, which uses the system tables to manage, for example, system security and permissions in C/SIDE.

You can read, write, modify, and delete the information in system tables.

There are eight system tables in C/SIDE. The following six tables handle system security:

·         User System Table

·         User Role System Table

·         Permission System Table

·         Windows Access Control System Table

The following two tables define data structure:

·         Company System Table

·         ($ T_2000000203 Database Key Groups $)

Virtual Tables

A virtual table contains system information. You cannot change the data in virtual tables. You can only read the information. Virtual tables are not stored in the database but are computed by Microsoft Dynamics NAV at run time.

Using Virtual Tables

You can use the same methods to access information in virtual tables as you use when you are working with ordinary tables. For example, you can use filters to get subsets or ranges of integers or dates from the Integer virtual table or the Date virtual table.

A system administrator usually uses these virtual tables. These tables give the system administrator information about the users who are currently connected to the database and the current state of the system.

The virtual tables provide such information as:

·         Integers in the range –1,000,000,000 to 1,000,000,000.

·         Dates in a given period.

·         Overview of the operating system files.

·         Overview of the logical disk drives.

·         Trace of database requests from your client to the database.

·         Overview of the users who are currently connected to the database.

·         Overview of the operating system files that store the database.

Because virtual tables are not stored in the database, you cannot view them directly. To view a virtual table, you must create a list page based on the virtual table. For more information, see How to: Create a Page to View a Virtual Table.