Excel FIND function 1

[jworley]

Hi,

I've created a macro in Excel that uses the find function, here is the code it produces:

Cells.Find(What:="Saunders", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

How can I translate this to VFP code for automation purposes? I have included the Excel header file, and can open the worksheet fine, no problems. Any help is much appreciated.

Jim Worley
jim@aits-uk.net

http://www.aits-uk.net

 

[JRB-Bldr]

You can substitute in your own variable names, but this worked for me just now...

tmpsheet = CREATEOBJECT('excel.application')
oExcel = tmpsheet.APPLICATION
oExcel.SheetsInNewWorkbook = 1
oExcel.Workbooks.CLOSE
oExcel.Workbooks.ADD

At this point in manually inserted a 'To Find' value into a cell.

oExcel.Cells.Find("George")

Successfully located the appropriate cell.

oExcel.Quit
RELEASE oExcel

Good Luck,
JRB-Bldr

 

[jworley]

Thanks - I'm working with an existing sheet. I've tried your approach but it doesn't seem to work. Any idea what I may be doing wrong?

worksheet = createobject('excel.application')
oExcel = worksheet.APPLICATION
oExcel.Workbooks.open('C:\LAT Excel Demo\excel\test.xls',,.F.)
oExcel.VISIBLE = .t.
oExcel.Cells.Find("Saunders")
*!*	oExcel.Quit
*!*	RELEASE oExcel

Jim Worley
jim@aits-uk.net

http://www.aits-uk.net

 

[JRB-Bldr]

Admittedly my previous test above was simplistic.
Probably too simplistic.

Unfortunately I have not had the time to look into the Excel Automated FIND conversion in greater depth YET.

Until either:
1. I get more time
2. or until someone else posts a GOOD answer,
you might want to consider what I often use to FIND cell contents within an Excel spreadsheet.

tmpsheet = CREATEOBJECT('excel.application')
oExcel = tmpsheet.APPLICATION
oExcel.SheetsInNewWorkbook = 1
oExcel.Workbooks.CLOSE
oExcel.Workbooks.ADD
xlBook = oExcel.ActiveWorkbook.FULLNAME
xlSheet = oExcel.activesheet

* --- Assuming that I know the contents are in Row r or Column c ---

FOR mnRow = 1 TO 255 && Or whatever top end
mtValue = xlSheet.Cells(mnRow, 5).VALUE && Known Column 5

* --- In This Instance Looking For Date Match ---
mcDate = DTOC(TTOD(mtValue))

IF mcDate = mcCallDate && mcCallDate is 'To-Find' Value
* --- Save Off Matching Row Numerical Value ---
xlFoundRow = mnRow
EXIT
ENDIF
ENDFOR

* --- Now Convert Numerical Row/Column To Alpha Values ---
< etc. >

oExcel.Quit
RELEASE oExcel

Obviously by nesting two FOR/ENDFOR loops you can search both Rows & Columns.

Good Luck,
JRB-Bldr