Excel COM find across all worksheets

Get help with using AutoHotkey and its commands and hotkeys
User avatar
evilC
Posts: 4791
Joined: 27 Feb 2014, 12:30

Excel COM find across all worksheets

Post by evilC » 30 Sep 2014, 06:20

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
}

User avatar
TLM
Posts: 1590
Joined: 01 Oct 2013, 07:52
GitHub: TLMcode
Contact:

Re: Excel COM find across all worksheets

Post by TLM » 30 Sep 2014, 09:29

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
Attachments
excel_book.zip
(1.37 KiB) Downloaded 50 times

User avatar
evilC
Posts: 4791
Joined: 27 Feb 2014, 12:30

Re: Excel COM find across all worksheets

Post by evilC » 30 Sep 2014, 09:31

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...

Post Reply

Return to “Ask For Help”