Reading a specific Excel Worksheet

Kylea

Hi,

I have a Report that reads in a spreadsheet to create a PO. Unfortunately the template that is used sometimes gets its worksheet name changed.

Is there a way to read the Excel Spreadsheet by Worksheet Number instead of by Name (refer T370 Excel Buffer).

Unfortunately the help in Navision for any Excel related functions is seriously lacking.

Thanks


Kylea

ara3n

Hello Kylea.

To read from excel file. the following two functions are called


ExcelBuf.OpenBook(FileName,SheetName);
ExcelBuf.ReadSheet;


Now in order to read based on worksheetnumber I would do the following


ExcelBuf.OpenBook('c:\temp\myexcelfile.xls','1');
ExcelBuf.ReadSheet;


Offcourse this won't work so we have to do the following change in excel buffer table


in function OpenBook add the following code.


WHILE (i <= EndOfLoop) AND (NOT Found) DO BEGIN
XlWrkshts := XlWrkBk.Worksheets.Item(i);
IF SheetName = XlWrkshts.Name THEN
Found := TRUE;
i := i + 1;
END;

//S001 New code begin
if (not found) and evaluate(i,SheetName) then begin
XlWrkshts := XlWrkBk.Worksheets.Item(i);
found := true;
end;

//S001 New Code Finish

IF Found THEN
XlWrkSht := XlWrkBk.Worksheets.Item(SheetName)