Does anyone know how I can have applescript select a cell based on it’s value i ahve this script mad already
tell application “Microsoft Excel”
activate
activate object workbook “1.xls”
activate object sheet “sheet1”
display dialog “test” default answer “” buttons {“OK”} default button 1
set the Text_pressed to the text returned of the result
activate object cell (result)
end tell
currently if i type c15 for example into the box it selects c15 but is there a way that if I type in blue in the box and one of the cells has the word blue in it that it will select that.
I would greatly appreciate any ideas or help with my issue
I’m not really sure what you’re after here, but if you’re saying what I think you’re saying, you’re going to need a couple of repeat loops that examine various values and jump out once you’ve found what you’re looking for. The code at the bottom does something like this. I checked to make sure it was working first. Sorry for all the extra error checking subroutines below. I use 'em cuz it saves me lots of debugging during construction. If you leave out
if isWholeNumber(r) = false or isWholeNumber(c) = false then
set msg to "The inputs to convertRowColumnNumbersToDollarSignFormat must both be positive integers."
display dialog msg
return
end if -- isWholeNumber(r) = false or isWholeNumber(c) = false
in ‘convertRowColumnNumbersToDollarSignFormat’, you can throw away the last three handlers. This routine isn’t going to be very fast, but if you have a predetemined range, I’m guess you can pull all the data out in one pass and then examine it through a list. I haven’t played with that yet so I can’t give you any more details on it.
Hope this helps.
searchExcelCells("something")
to searchExcelCells(valueToLookFor)
set shName to "sheet 1" -- or name of your sheet
set wbName to "workbook 1" -- or name of your workbook
set foundFlag to false
set i to 1
set n to 25 -- or some row value
repeat until i = n + 1 or foundFlag is true
set j to 1
set m to 25 -- or some column value
repeat until j = m + 1 or foundFlag is true
set cellValue to getDataFromExcelCellByRC(i, j, shName, wbName)
-- might need to process cellValue here
if cellValue = valueToLookFor then
set foundFlag to true
-- maybe do some other things here
end if -- cellValue = valueToLookFor
set j to j + 1
end repeat -- until j = m + 1 or foundFlag is true
set i to i + 1
end repeat -- until i = n + 1 or foundFlag is true
set output to "something to output if you want"
end searchExcelCells
to getDataFromExcelCellByRC(rowNum, colNum, shName, wbName)
-- v1.00
-- Direct Dependence: convertRowColumnNumbersToDollarSignFormat
-- Indirect Dependence: isWholeNumber, isInteger, isRealNumber
set cellStr to convertRowColumnNumbersToDollarSignFormat(rowNum, colNum)
tell application "Microsoft Excel"
set output to value of cell cellStr of sheet shName of workbook wbName
end tell -- "Microsoft Excel"
end getDataFromExcelCellByRC
to convertRowColumnNumbersToDollarSignFormat(r, c)
-- Direct Dependence: isWholeNumber
-- Indirect Dependence: isRealNumber, isInteger
if isWholeNumber(r) = false or isWholeNumber(c) = false then
set msg to "The inputs to convertRowColumnNumbersToDollarSignFormat must both be positive integers."
display dialog msg
return
end if -- isWholeNumber(r) = false or isWholeNumber(c) = false
set c1Char to ""
set i to (c - 1) div 26
if i > 0 then set c1Char to ASCII character (i + 64)
set j to ((c - 1) mod 26) + 1
set c2Char to ASCII character (j + 64)
set x to c2Char
set outStr to "$" & c1Char & c2Char & "$" & r
end convertRowColumnNumbersToDollarSignFormat
to isWholeNumber(n)
-- Direct Dependence: isInteger
-- Indirect Dependence: isRealNumber
set output to isInteger(n)
if output is true and n < 0 then set output to false
get output
end isWholeNumber
to isInteger(x)
-- Direct Dependence: isRealNumber
set output to isRealNumber(x)
if output is true then
if class of x is real then
set output to false
else
set output to true
end if
end if -- output is true
get output
end isInteger
to isRealNumber(x)
-- v1.02
-- Independent
set output to true
set xStr to x as string
set xStrList to characters of xStr
set n to count of xStrList
set i to 0
set decimalCounter to 0
repeat until i ≥ n or output is false
set i to i + 1
set passFlag to false
set ascNum to ASCII number item i of xStrList
if (ascNum ≥ 48 and ascNum ≤ 57) then set passFlag to true
if (ascNum = 46) then
set decimalCounter to decimalCounter + 1
if decimalCounter ≤ 1 then set passFlag to true
end if -- (ascNum = 46) then
if (ascNum = 45) then set passFlag to true
if (ascNum = 43) then set passFlag to true
if (ascNum = 69 and i ≠n) then set passFlag to true
if passFlag is false then set output to false
end repeat
get output
end isRealNumber
I was actually looking for this post about an excel range that I saw earlier so I could save it for later use when I ran into your question and posted the reply above. But this post may give us both a start at pulling out more data all at one time.
Ok . . . now I needed a row or range reference. Turns out I had the correct manual on my computer all along and forgot to look at it. There are much easier ways to do at least some stuff in Excel. This is where you go to download the manual:
I’m used to playing with the macros on Excel which makes you jump through hoops to reference cells by indices. You might not need the ‘convertRowColumnNumbersToDollarSignFormat’ routine because some of the script commands allow you to use indices. For example, to clear cell B3, you can
tell application "Microsoft Excel"
set shName to "sheet name here"
set wbName to "workbook name here"
set refsNwbN to sheet shName of workbook wbName
set value of cell 3 of column 2 of refsNwbN
end tell -- "Microsoft Excel"
To clear all of row 26, you can
clear contents range "26:26" of refsNwbN
But for clearing cells from B24 through C25 for example, this did not work:
clear contents cells 24 thru 25 of columns 2 thru 3 of refsNwbN
while this did:
clear contents range "B24:C25" of refsNwbN
so there still may be some use for the converting routine yet.
I’m using Excel X and the changed the wording of keywords, but you can try something like this:
tell application "Microsoft Excel"
Activate
set r to UsedRange of Worksheet 1
try
set cell_range to (Find r What "blue")
Select cell_range
on error
display dialog "Value not found."
end try
end tell
Look in Excel’s dictionary for something like the Find command. They also change UsedRange, but you can change that to any range of cells.
I just wanted to add that in the Excel2004AppleScriptRef states that the find command takes on several optional values from the last search, so they are best set explicitly in order to to have the find command work as expected.
This will look through all the cell of the Active Sheet, select those that contain only the text “blue” and return the address of those cells.
To find cells that contain “blue”, but are not equal to it, change the look at argument of the first find to part.
tell application "Microsoft Excel"
set Text_pressed to "blue"
set RangeToSearch to get used range of active sheet
try
set recentAddress to ""
set lastCellFound to get (find RangeToSearch what Text_pressed look in values look at whole without match case)
set foundCells to lastCellFound
set firstFoundAddress to get address (lastCellFound)
on error
display dialog "not found"
end try
repeat while recentAddress ≠firstFoundAddress
set foundCells to union range1 foundCells range2 lastCellFound
set lastCellFound to find next RangeToSearch after lastCellFound
set recentAddress to get address lastCellFound
end repeat
select foundCells
return get address foundCells
end tell
