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;