Edit MS Excel cell data using NAV 2009 C/AL code

Well I have imported Excel Data to the NAV Table and Exported data from NAV to Excel for so many times as required.

But here i would like to share Editing excel cell data and saving that file which has multiple sheets through NAV C/AL code.

Sample Code(Tested & Approved):

Variables:
Name DataType Subtype Length
SheetName Text 250
XlWrkSht Automation Unknown Automation Server.Worksheet
XlRange Automation Unknown Automation Server.Range
XlApp Automation Unknown Automation Server.Application
XlWrkBk Automation Unknown Automation Server.Workbook

XlWrkshts Automation Unknown Automation Server._Worksheet

CREATE(XlApp,TRUE,TRUE);
XlApp.Workbooks.Open('C:\Sample\Test.xlsx');  //Excel Sheet fully qualified path
XlWrkBk := XlApp.ActiveWorkbook;
XlWrkshts := XlWrkBk.Worksheets.Item(1);       //selecting the particular sheet among multiple Worksheets
SheetName := XlWrkshts.Name;
XlWrkSht := XlWrkBk.Worksheets.Item(SheetName);
XlRange := XlWrkSht.Range('AH7');                   //select the particular cell
XlRange.Value := 'updated Test Value';               //Cell value will be updated with the 'updated Test Value'
XlWrkBk.Close(TRUE);                                    //file will be saved and closed.
XlApp.Quit;

CLEAR(XlApp);

Emailing through NAS in NAVISION 4.0 SP3

FYI..

NAV 4.0 SP3 did not  have email functionality through SMTP.(no where found it in NAV4).

Here we have sample code which is tested and worked fine for me.

Variable:

Name DataType Subtype Length

objEmailConf Automation 'Microsoft CDO for Windows 2000 Library'.Configuration

objEmail Automation 'Microsoft CDO for Windows 2000 Library'.Message

flds Automation 'Microsoft ActiveX Data Objects 2.5 Library'.Fields

fld Automation 'Microsoft ActiveX Data Objects 2.5 Library'.Field

Sample Code:

 IF ISCLEAR(objEmailConf) THEN CREATE(objEmailConf); 

 flds := objEmailConf.Fields;

fld := flds.Item('http://schemas.microsoft.com/cdo/configuration/smtpserver'); 

fld.Value('Smtp Server'); //here EX:smtp.gmail.com 

fld := flds.Item('http://schemas.microsoft.com/cdo/configuration/smtpauthenticate') ;

fld.Value('1'); //we have 3 oprions.1 means basic one means it require authentiaction
fld := flds.Item('http://schemas.microsoft.com/cdo/configuration/sendusing');
fld.Value('2');

fld := flds.Item('http://schemas.microsoft.com/cdo/configuration/sendusername') ; fld.Value('xyz@urdomain.com');//sender user name 

fld := flds.Item('http://schemas.microsoft.com/cdo/configuration/sendpassword') ;

fld.Value('password'); //sender password

fld := flds.Item('http://schemas.microsoft.com/cdo/configuration/smtpserverport'); 

fld.Value('25'); //Dafault Port Number is 25

fld := flds.Item('http://schemas.microsoft.com/cdo/configuration/smtpusessl');

fld.Value('TRUE');      //Enable SSL if your smtp server allow only Https protocaol

flds.Update;

IF ISCLEAR(objEmail) THEN CREATE(objEmail); 

objEmail.Configuration := objEmailConf;

objEmail.From := 'Sender Name'; 

objEmail."To" := 'Reciepent Email Id';

objEmail.Subject := 'Text Subject';

objEmail.TextBody := 'Test Body';

objEmail.Send; 

Downloading Files without user interaction in the NAV Role Tailored Client

Working with three tier means you can do File operations in only the Machine which has NAV Service.If you have to move files to the client machine then we go for Download or Streaming Procedure.

But here we have a limitation like user interaction is required.

This procedure works even for the Network Path too.(I mean three tiers installed in different machines environment,NAV 2007 R2 has issue in handling with Network paths.)

In order to escape  the interaction from user we have an option like VB Script.

We can use as following Script which runs a Report and save the generated file in the client location.

Sample Code:

Name          DataType        Subtype                                              Length

objScript       Automation   'Microsoft Script Control 1.0'.ScriptControl

CR                   Text                                                                                1

Report.SAVEASPDF(Number, TEMPORARYPATH+FileName [, Record]) //File location can be only                                                                                                                           service installed machine

IF CREATE(objScript,TRUE,TRUE) THEN

BEGIN

  CR := ' '; CR[1] := 13;

  objScript.Language := 'VBScript';

  objScript.AddCode(

  'function DownloadToClient(fromFile, FileName)'+CR+                     //DownloadToClient-is a User                                                                                                                                 defined function   

  'set fso = createobject("Scripting.FileSystemObject")'+CR+

  'set x = createobject("Scriptlet.TypeLib")'+CR+

  'path = fso.getfolder("'Required Client Location'")'+CR+

  'toPath = path'+CR+

  'fso.MoveFile fromFile, toPath+"\"+toFile'+CR+                                         

  'RenameTempFile = toPath'+CR+

  'end function');

   objScript.Eval('DownloadToClient("'+ClientFileName+'","'+ToFile+'")');  //executing above created function

  END;

                                                                                                       

QR Code in NAVISION

The QR code, similar to a barcode, is an example of an information matrix. However a significant difference in the two is that while a barcode only holds information nicely in the horizontal direction, a QR can do so vertically as well. This is why QR codes are referred to as two-dimensional, because they carry information both vertically and horizontally. Another direct result to this is greater potential to carry information in a smaller space. Compared to a barcode, it’s no competition at all.

To implement it Click here .

Appending Data to the end of the File in NAV

Variables:

Name                   DataType             

ExportFile            File

Sample Code:

  ExportFile.TEXTMODE(TRUE);

  ExportFile.WRITEMODE(TRUE);

  ExportFile.OPEN(FileName);   //here file name is existed file's fullpath along it's name

  ExportFile.SEEK(ExportFile.LEN);

  ExportFile.WRITE('Hello Navision');

Output:

Specified file will opened in text mode and cursor will reach the end of the file.

Piece of the text(Hello Navision) will be added to the specified file.

Lookup Vs Drill Down

In Nav We have Very easy navigation facilities like Lookup & Drill Down Functionalities.
Lookup:
              The 'Lookup' Word it self says everything,which it shows the specified data of the another table.
Drill Down:
               It's the eye opener,even i say it clears Cloud Regarding Totals.Actually it Works on Flow Field and Enables us to give Detailed information.