FIND function in Excel

[vdennis]

As anyone had any luck converting the FIND function in excel to 4GL? What I need is to search a column for a value and if found return the ROW number .
Thanks for taking the time to read this.
-Dennis-

 

[mrobles]

Half Job

Progress
chWorkSheet:Cells:Find('telefono',,-4123, 2,1,1,FALSE,FALSE):SELECT.

This came from the Macro
Cells.Find(What:="telefono", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate

Now the problem is how to get the row number

 

[Stefan]

Sure:

/* enumerations - http://msdn.microsoft.com/en-us/library/bb259481 */


/* xlFindLookIn */
&SCOPED-DEFINE xlValues    -4163
&SCOPED-DEFINE xlFormulas  -4123
&SCOPED-DEFINE xlComments  -4144


/* XlLookAt */
&SCOPED-DEFINE xlWhole     1
&SCOPED-DEFINE xlPart      2


/* XlSearchOrder */
&SCOPED-DEFINE xlByRow     1
&SCOPED-DEFINE xlByColumns 2


/* XlSearchDirection */
&SCOPED-DEFINE xlNext      1
&SCOPED-DEFINE xlPrevious  2




DEF VAR chExcel      AS COM-HANDLE  NO-UNDO.
DEF VAR chWorksheet  AS COM-HANDLE  NO-UNDO.
DEF VAR chCell       AS COM-HANDLE  NO-UNDO.
DEF VAR chCellStart  AS COM-HANDLE  NO-UNDO.


CREATE "Excel.Application" chExcel.


chExcel:Visible = TRUE.


chExcel:Workbooks:Open ( "c:\temp\find.xlsx" ).


chWorkSheet = chExcel:Sheets:Item(1).


chCellStart = chWorksheet:Range( "A1" ).
chCell   =  chWorkSheet:Columns( 1 ):Find( 
               "findme",           /* text to find */
               chCellStart,   /* after */
               {&xlValues},   /* FindLookIn */
               {&xlWhole},    /* LookAt */
               {&xlByRow},    /* SearchOrder */
               {&xlNext},     /* SearchDirection */
               FALSE          /* MatchCase */
            ).




MESSAGE chcell:Row VIEW-AS ALERT-BOX.


RELEASE OBJECT chCellStart.
RELEASE OBJECT chCell.
RELEASE OBJECT chWorksheet.
RELEASE OBJECT chExcel.

 

[vdennis]

Sure:

/* enumerations - http://msdn.microsoft.com/en-us/library/bb259481 */


/* xlFindLookIn */
&SCOPED-DEFINE xlValues    -4163
&SCOPED-DEFINE xlFormulas  -4123
&SCOPED-DEFINE xlComments  -4144


/* XlLookAt */
&SCOPED-DEFINE xlWhole     1
&SCOPED-DEFINE xlPart      2


/* XlSearchOrder */
&SCOPED-DEFINE xlByRow     1
&SCOPED-DEFINE xlByColumns 2


/* XlSearchDirection */
&SCOPED-DEFINE xlNext      1
&SCOPED-DEFINE xlPrevious  2




DEF VAR chExcel      AS COM-HANDLE  NO-UNDO.
DEF VAR chWorksheet  AS COM-HANDLE  NO-UNDO.
DEF VAR chCell       AS COM-HANDLE  NO-UNDO.
DEF VAR chCellStart  AS COM-HANDLE  NO-UNDO.


CREATE "Excel.Application" chExcel.


chExcel:Visible = TRUE.


chExcel:Workbooks:Open ( "c:\temp\find.xlsx" ).


chWorkSheet = chExcel:Sheets:Item(1).


chCellStart = chWorksheet:Range( "A1" ).
chCell   =  chWorkSheet:Columns( 1 ):Find( 
               "findme",           /* text to find */
               chCellStart,   /* after */
               {&xlValues},   /* FindLookIn */
               {&xlWhole},    /* LookAt */
               {&xlByRow},    /* SearchOrder */
               {&xlNext},     /* SearchDirection */
               FALSE          /* MatchCase */
            ).




MESSAGE chcell:Row VIEW-AS ALERT-BOX.


RELEASE OBJECT chCellStart.
RELEASE OBJECT chCell.
RELEASE OBJECT chWorksheet.
RELEASE OBJECT chExcel.

Thanks very much for the information. I had been trying to find something with the COM veiwer tool and was at a lost in 'converting' the VB code into 4gl. (Not enough coffee.) I have three excel files, one with multiable tabs, that I need cross reference and update. This was the missing piece of the puzzle.
-Dennis-

 

[vdennis]

Thanks for the info. See Stefan's reply has it has the ROW problem solved.
-Dennis-