Search an entire Excel workbook, not just one worksheet

Scripting Forums AppleScript | Mac OS X
#1

Hello,

I’ve learned how to search a worksheet and return the cell name with something like this:


tell application "Microsoft Excel"
	tell workbook 1
		tell worksheet 1
			find used range what "hello1"
		end tell
	end tell
end tell

The above script would return range “[Book1]Sheet1!$E$6”

Which tell us which sheet and which cell.

The limitation of this is that it can only search one worksheet at a time. Excel itself is able to search an entire workbook for a value. Is there a way to do this with AppleScript?

I’ve been getting around this by looping through each sheet and doing the search for each sheet, but I imagine it would be faster if there’s a way to search an entire workbook, instead of just a sheet.

#2

I don’t own Merdosoft products but it would cost nothing to execute:

tell application "Microsoft Excel"
	tell workbook 1
		-- tell worksheet 1
			find used range what "hello1"
		-- end tell
	end tell
end tell

Yvan KOENIG running High Sierra 10.13.6 in French (VALLAURIS, France) mardi 23 juin 2020 13:32:35

#3

Not as far as I can tell. The main stumbling block is that the find command takes a required parameter indicating the range to be searched and ranges cannot span multiple worksheets. I think looping through the worksheets is your only option.

If it’s any consolation, you can’t do it with VBA either, so it’s not just an AppleScript limitation.

It might be possible using GUI scripting to access the actual Find dialog, but I doubt it would be worth the effort.