Tuesday, September 29, 2020

#1 2020-06-15 09:07:43 pm

wayland
Member
From:: Nha Trang
Registered: 2020-05-13
Posts: 55

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

I can do this in Numbers:

Applescript:


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.

Applescript:


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?

Offline

 

#2 2020-06-16 07:11:26 am

Yvan Koenig
Member
Registered: 2006-09-14
Posts: 4569

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

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

What would return :

Applescript:

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

Offline

 

#3 2020-06-17 06:30:24 pm

roosterboy
Member
Registered: 2010-04-19
Posts: 26

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

Either one of these approaches works for me...

Applescript:


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:

Applescript:


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

Last edited by roosterboy (2020-06-17 06:32:02 pm)

Offline

 

#4 2020-06-19 01:08:39 am

wayland
Member
From:: Nha Trang
Registered: 2020-05-13
Posts: 55

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

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.

Last edited by wayland (2020-06-19 01:10:21 am)

Offline

 

#5 2020-09-14 12:11:05 pm

KniazidisR
Member
Registered: 2019-03-03
Posts: 1311

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

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:

Applescript:


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:

Applescript:


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

Last edited by KniazidisR (2020-09-14 12:43:18 pm)


Model: MacBook Pro
OS X: Catalina 10.15.4
Web Browser: Safari 13.1
Ram: 4 GB

Offline

 

#6 2020-09-19 03:57:50 pm

wayland
Member
From:: Nha Trang
Registered: 2020-05-13
Posts: 55

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

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

Offline

 

#7 2020-09-19 04:32:22 pm

KniazidisR
Member
Registered: 2019-03-03
Posts: 1311

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

wayland wrote:

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


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

Last edited by KniazidisR (2020-09-19 05:01:02 pm)


Model: MacBook Pro
OS X: Catalina 10.15.4
Web Browser: Safari 13.1
Ram: 4 GB

Offline

 

#8 2020-09-25 03:43:22 am

wayland
Member
From:: Nha Trang
Registered: 2020-05-13
Posts: 55

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

Thank you.

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

Offline

 

Board footer

Powered by FluxBB

RSS (new topics) RSS (active topics)