Excel COM find across all worksheets

[evilC]

Hi All,
I am new to the world of COM, and I need to work out how to search across all sheets within a given file.

Here is the code I am using to search at the moment:

Code: Select all

LinkedExcel := Excel_Get()
LinkedExcel.Columns().Select
LinkedExcel.Selection.Find("blah", LinkedExcel.ActiveCell).Select

[...]

; =============================================================================================================
; 3rd party functions

Excel_Get(WinTitle="ahk_class XLMAIN") {
	; by Sean and Jethrow, minor modification by Learning one
	; http://www.autohotkey.com/forum/viewtopic.php?p=492448#492448
	ControlGet, hwnd, hwnd, , Excel71, %WinTitle%
	if (!hwnd){
		return
	}
	Window := Acc_ObjectFromWindow(hwnd, -16)
	Loop {
		try {
			Application := Window.Application
		} catch {
			ControlSend, Excel71, {esc}, %WinTitle%
		}
	} Until !!Application
	return Application
}

[TLM]

with com things can ge greatly simplified
for example ( see attached xl book ):

Code: Select all

book := a_desktop "\111.xls"

msgbox % GetCell( book, 2, "C3" )

GetCell( file, sheet, range )
{   
    return ComObjGet( file ).Sheets( sheet ).Range( range ).Value 
}

you can then use GetCel()l in a loop to search across as many sheets and cells as you like

Code: Select all

Loop
{
    msgbox % GetCell( book, 2, "C" a_index ) ; this will search sheet 2, column C
}

it can obviously be totally customized..
hth

[evilC]

Yeah, I worked out a way of doing it like that:

Code: Select all

				Loop % LinkedWorkbook.Sheets.Count {
					res := LinkedWorkbook.Sheets(A_Index).Cells.Find(ScanList[1])
					ct := res.Count

					if (ct){
						LinkedWorkbook.Sheets(A_Index).Activate
						res.Select
						HighBeep()
						break
					}
				}
				ScanList.Remove(1)

I was hoping, however, that there was a way of doing it without having to enumerate through all the sheets...