Example to Search through Excel and Find Items and Cells

Keywords: 	  search Excel OLE

Nice example of how to search through an Excel Spreadsheet and find certain items and cells.

Here's the Excel file:

find.xls

And here's the script:

; Searching part of a list through an Excel file
;*************************************************
DB=ObjectOpen("Excel.Application")
DB.Visible=@TRUE
DB.UserControl=@TRUE
oAPP=DB.Workbooks
oAPP.Open("c:\temp\find.xls")

; if FULL_PC_LIST=the following, it's ok with the last instruction DB.goto("R10C1")
FULL_PC_LIST="ABC,ABV,JMV,PER,DEV,CSA,GHI,JPD,MIV,SEC,xxx"			; ok working

; if FULL_PC_LIST=the following, we got AN ERROR with the last instruction DB.goto("R10C1")
;FULL_PC_LIST="ABC,xxx,ABV,JMV,PER,DEV,CSA,GHI,JPD,MIV,SEC"			; KO DOES NOT WORK ! ! ! 

XLVALUES=-4163
CELLRANGE=DB.Range("A1:A1000")
ADD_TO_XL=""
Y=ItemCount(FULL_PC_LIST,",")
For I=1 to ItemCount(FULL_PC_LIST,",")
	BoxText("Update Excel table (Step 1) %I%/%Y%")
	X=ItemExtract(I,FULL_PC_LIST,",")
	FINDING=CELLRANGE.Find(X :: lookin=XLVALUES)
	if FINDING==0
		message(X,"not found")
	else
		message(x,"found")
	endif
next I

message("Going to cell","R20C1")
DB.Goto("R20C1")

Article ID:   W14932

File Created: 2017:08:29:11:58:46

Last Updated: 2017:08:29:11:58:46

Database Search

TechHome

Example to Search through Excel and Find Items and Cells