Find in excel

Belias

i am trying to do a find in excel directly from nav.
i have no prob to open and read the file, i also thought to use excel buffer to find the value i am searching.
the problem is that i have to find the last value, insert one or more lines below (in excel) and then proceed with a different value.
as you can imagine, excel buffer will record only the first situation, but after inserting one line, all the other line numbers will be messed out.
Then i can solve my problem only with the excel find (and then retrieve the line and col numbers)
anybody have an idea?
thanks in advance

Belias

XlApp.Range('D1').EntireColumn.Find('SPTOR').Activate;

to finde 'sptor' in column "D". I tought it was more difficult
now, the second...how can I retrieve the line and column id where i actually am?

Belias

intcolumnno := XlApp.activecell.row;
introwno := XlApp.activecell.column;

with these instructions you can retrieve the number of the column and the row...if you want 'A1' instead of 1 1, you can validate the values in the excelbuffer and then retrieve the xlcolumnid value

Belias

Other problem.
How can i handle the error when excel does not find a value, when i launch the excel find function?
...btw...it's frustrating to talk alone...

Belias

i solved this, too...I was hoping for a better way but no one replied
i've done the trick with the "codeunit.run"...I run the find function in the codeunit(passing xlapp)and I returned the value of the interested row(within a function launched from the Onrun of the codeunit)...
then I called the codeunit with "if" statement....so:
IF Codeunit.Run THEN Record is Found ELSE record is not found;

I will be happy to give a more detailed explanation if someone needs this!

Belias

here's a sample code

CUUtilityErr.FNTGetPar('FINDXLSERR','A1',"No.",XlApp);
IF NOT CUUtilityErr.RUN THEN BEGIN
  INTFirstRow := CUUtilityErr.FNTParBack;
END ELSE
  MESSAGE('VALUE FOUND');

CODEUNIT:

OnRun()
CASE TXTGlobErrType OF
  'FINDXLSERR': BEGIN
    FNTFindExcelError(TXTGlobWhere,TXTGlobWhat);
  END;
END;
FNTFindExcelError(TXTRange : Text[30];TXTCellValue : Text[50]) : Integer
XlApp.Range(TXTRange).EntireColumn.Find(TXTCellValue).Activate;
INTRowNo := XlApp.ActiveCell.Row;
EXIT(INTRowNo);

FNTGetPar(TXTLocErrType : Text[30];TXTLocWhere : Text[50];TXTLocWhat : Text[30];XlLocApp : Automation "Unknown Automation Server.Applic
CASE TXTLocErrType OF
  'FINDXLSERR': BEGIN
    TXTGlobWhat := TXTLocWhat;
    TXTGlobWhere := TXTLocWhere;
    TXTGlobErrType := TXTLocErrType;
    XlApp := XlLocApp;
  END;
END;

FNTParBack() : Integer
CASE TXTGlobErrType OF
  'FINDXLSERR': BEGIN
    EXIT(INTRowNo);
  END;
END;

NixPt

Tnks it works good! =P~

Belias

you're welcome (fortunately you're clever enough to understand the code, because i can't remember what exactly i was doing with it...execpt trying to find a value )

NixPt

i improve a little your code for give me cells and collums position and for merge cells.

My problem was the error doing activate cells, when excel don't find any match.

Belias

yes, i pasted the code without taking a look at it...
the correct syntax is

getpar
if cu.run then  //cell found
  row number of the cell = fntparback

glad to be helpful, anyway