TIP #2

Reading Excel file into table using simple setps

Variables:

XlApp      = Automation = 'Microsoft Excel 14.0 Object Library'.Application
Xlbook     = Automation = 'Microsoft Excel 14.0 Object Library'.Workbook
XlSheet    = Automation = 'Microsoft Excel 14.0 Object Library'.Worksheet
NoOfRows   = Integer
Data file  = Record Variable
Text001    = Sales Data         @2@@@@@@@@@@\\ 
Window     = Dialog
i          = Integer

Code:

CLEAR(XlApp);
IF NOT EXISTS("Data File") THEN
  ERROR('The Specified file %1 not exist',Rec."Data File");
Window.OPEN('#1#############################\\\'+Text001);
CREATE(XlApp,TRUE);
XlApp.Workbooks.Open("Data File");
Xlbook := XlApp.ActiveWorkbook;
XlSheet := Xlbook.Worksheets.Item(1);
NoOfRows := XlSheet.UsedRange.Rows.Count;
Window.UPDATE(1,STRSUBSTNO('Integrating Data...'));

// i=2 means I am reading my excel from second row onwards
FOR i := 2 TO NoOfRows DO BEGIN
Rec."Entry No.":=i-1;
EVALUATE(Rec."Item No.",FORMAT(XlSheet.Range('A'+FORMAT(i)).Value));
EVALUATE(Rec."Name",FORMAT(XlSheet.Range('B'+FORMAT(i)).Value));
EVALUATE(Rec."UOM",FORMAT(XlSheet.Range('C'+FORMAT(i)).Value));
EVALUATE(Rec."Qunatity",FORMAT(XlSheet.Range('D'+FORMAT(i)).Value));
EVALUATE(Rec."Price",FORMAT(XlSheet.Range('E'+FORMAT(i)).Value));
EVALUATE(Rec."Amount",FORMAT(XlSheet.Range('F'+FORMAT(i)).Value));
END;

Xlbook.Close(TRUE);
CLEAR(XlSheet);
XlApp.Quit;
CLEAR(XlApp);
Window.close;


-- Happy Reading