Search an excel worksheet for a string and return the cell coordinates

Scripting Forums AppleScript | Mac OS X
#1

I can do this in Numbers:


tell application "Numbers"
	tell document 1
		tell sheet 1
			tell table 1
				get first cell whose value is "hello"
			end tell
		end tell
	end tell
end tell

And it will return the coordinate of the first cell that has the value “hello”. I will return “E4” or something like that depending on where that string is.

I’m trying to accomplish the same thing in Excel, but it won’t work.


tell application "Microsoft Excel"
	tell workbook 1
		tell worksheets
			tell worksheet 1
				get first cell whose value is "hello"
			end tell
		end tell
	end tell
end tell

The error message I get is “The object you are trying to access does not exist”.

Any ideas?

#2

I don’t use Merdosoft products but I guess that there is an extraneous level of tells in your attempt.

What would return :

tell application "Microsoft Excel"
	tell workbook 1
		log "point 1"
		-- tell worksheets
		log "point 2"
			tell worksheet 1
				log "point 3"
				get first cell whose value is "hello"
			end tell
		-- end tell
	end tell
end tell

Yvan KOENIG running High Sierra 10.13.6 in French (VALLAURIS, France) mardi 16 juin 2020 15:11:04

#3

Either one of these approaches works for me…


tell application "Microsoft Excel"
	
	tell active workbook
		tell active sheet
			set foundCell1 to find used range what "hello"
		end tell
	end tell
	
	set ws to name of worksheet 1
	tell workbook ws
		tell worksheet 1
			set foundCell2 to find used range what "hello"
		end tell
	end tell
	
end tell

I was unable to get this to work:


tell application "Microsoft Excel"
	
	tell workbook 1
		tell sheet 1
			set foundCell1 to find used range what "hello"
		end tell
	end tell
	
end tell
#4

The (whose/where/that) key does not appear to work in Excel. Though I should test it with the the used range keyword as well.

The find used range what searchTerm works fine. If I wrap it in a try catch it will skip over things it can’t find.

I think the main difference with Numbers and Excel is that a Numbers sheet can have multiple tables, while Excel sheets don’t have tables. When doing a search, I always have to specify the used range, otherwise, will try to search through 16 thousand columns and 1 million rows and cause Excel to crash.

#5

Here is a workaround. You should use try block too, because may be isn’t any match for the search text at all in the sheet:

To search in the specified range of the sheet:


tell application "Microsoft Excel"
	activate
	try
		set theRange to find range "A1:B7" search order by rows what "hello"
	on error
		display dialog "No matches founded"
	end try
end tell

To search in the whole sheet:


tell application "Microsoft Excel"
	activate
	try
		find (range "A1:IV65536") search order by rows what "hello"
	on error
		display dialog "No matches founded"
	end try
end tell
#6

What would be the benefit of add the “search order by rows” part?

#7

You can search in the table by moving in 2 directions: vertically - moving along the rows, horizontally - moving along the columns. You must choose one of 2 directions, as there is no direction to go anywhere.

Only the plain list has only 1 direction, and the table (sheet) of Microsoft Excel is list of lists (as it see the AppleScript). So, I choose search direction

#8

Thank you.

This kind of search is much faster than iterating each cell to search for a string.