Hi John,
Lets try this code, may be its solve your issue. I have also attached excel file.
OleObject lole_MainWorkBook, lole_sheet, lole_cell
Long ll_last, ll_ctr, ll_result
Any la_col
String ls_Address, ls_FirstAddress, ls_SheetName
lole_MainWorkBook = CREATE OLEObject
ll_result = lole_MainWorkBook.ConnectToObject("D:\TestPB\sample.xls")
If ll_result <> 0 then
MessageBox( "Error", 'Error running MS Excel api.')
Destroy lole_MainWorkBook
Else
lole_MainWorkBook.Activate
ll_last = lole_MainWorkBook.Worksheets.count
//Code run for all sheet in a workbook
For ll_ctr = 1 to ll_last
lole_sheet = lole_MainWorkBook.WorkSheets(ll_ctr)
lole_sheet.Activate()
ls_SheetName = String(lole_sheet.name)
//Search '123' from excel
lole_cell = lole_sheet.Cells.Find("123")
If Not IsNull(lole_cell) Then
ls_FirstAddress = String(lole_cell.Address)
MessageBox("ls_FirstAddress", ls_SheetName + ' - ' + ls_FirstAddress)
ls_Address = ""
//Running into do while loop until got the first address
Do While (ls_FirstAddress <> ls_Address)
//Find next if no match found it will return the first match address
lole_cell = lole_sheet.Cells.FindNext(lole_cell)
ls_Address = String(lole_cell.Address)
If ls_FirstAddress <> ls_Address Then MessageBox("ls_Address", ls_SheetName + ' - ' + ls_Address)
Loop
End If
Next
End If
thanks a lot , but the problem is exactly how do i get the return value (column name) from the Find function in a pb variable ? I tried with the any variable, but the messagebox always shows me my search string and not the found column ?
....
ll_last = lole_MainWorkBook.Worksheets.count
lole_MainWorkBook.Activate
lole_sheet = lole_MainWorkBook.WorkSheets(ll_ctr)
lole_sheet.Activate()
any_a = lole_sheet.Cells.Find('#STARTPOSITION')
If IsNull(any_a) Then
messagebox("Nothing Found...","null values")
Else
messagebox("String found", string(any_a)) // any_a will always have the search string , in my example #STARTPOSITION and not the column name ?
End If
....
To get the column or row you have to access the properties of this object.
https://docs.microsoft.com/de-de/office/vba/api/excel.range%28object%29
e.g.
oleobject lole_cell
lole_cell = lole_sheet.Cells.Find("...")
IF NOT IsNull (lole_cell) THEN
MessageBox ("Col", lole_cell.Column) // Column property returns the column number (1 = A, 2 = B, ...)
END IF