Hi
I have a lot of excel scripts that I would like to change the sort range in. Is it possible to change the sort range in the script using script?
activate object worksheet "hold#2"
sort range "m1" of worksheet "hold#2" key1 (range "m1" of worksheet "hold#2")
what i want is
sort range "k1" of worksheet "hold#2" key1 (range "k1" of worksheet "hold#2")
The script edit dictionary doesn’t have find or replace.
thanks
bills
I’m not exactly sure what you’re asking. I don’t know why you want to sort excel data in applescript… why not use excel? Anyway, if you want a find/replace function in applescript use this…
set myText to "some text to play with"
findReplace(myText, "text", "string")
--> "some string to play with"
on findReplace(theText, findString, replaceString)
if theText contains findString then
set {tids, text item delimiters} to {text item delimiters, findString}
set a to text items of theText
set text item delimiters to replaceString
set b to a as text
set text item delimiters to tids
return b
else
return theText
end if
end findReplace
I think bills has a whole load of scripts which tell Excel to sort a particular range in a worksheet, which now need to be revised. The direct answer to the question, including Hank’s handler, would be something like this:
set searchStr to "sort range \"m1\" of worksheet \"hold#2\" key1 (range \"m1\" of worksheet \"hold#2\")"
set replaceStr to "sort range \"k1\" of worksheet \"hold#2\" key1 (range \"k1\" of worksheet \"hold#2\")"
tell application "Finder" to set scriptFiles to selection as alias list -- or however you get your script file aliases.
tell application "AppleScript Editor"
repeat with thisFile in scriptFiles
set thisScript to (open thisFile)
set scriptText to thisScript's text
set revisedText to my findReplace(scriptText, searchStr, replaceStr)
set thisScript's text to revisedText
close thisScript saving yes
end repeat
end tell
on findReplace(theText, findString, replaceString)
if theText contains findString then
set {tids, text item delimiters} to {text item delimiters, findString}
set a to text items of theText
set text item delimiters to replaceString
set b to a as text
set text item delimiters to tids
return b
else
return theText
end if
end findReplace
Oh, I get it now. 
He wants to replace text in the code of his applescript’s. It has nothing to do with actually sorting excel data which is what I was thinking.
Hi
Thanks, you guys got me on the right tract.
Here is what i eventually came up with, Though the repeat is a little slow.
on replaceInScript(findString, replaceString)
tell application "Script Editor"
tell the front document
set this_text to the contents
set this_offset to the offset of the findString in this_text
if this_offset is not 0 then
set selection to characters this_offset thru (this_offset + (length of the findString) - 1)
set the contents of the selection to the replaceString
end if
end tell
end tell
end replaceInScript
set findString to "sort range \"m1\" of worksheet \"hold#2\" key1 (range \"m1\" of worksheet \"hold#2\")"
set replaceString to "sort range \"k1\" of worksheet \"hold#2\" key1 (range \"k1\" of worksheet \"hold#2\")"
set getPath to "test replace"
tell application "Finder" to set fileList to every file of folder getPath
repeat with i from 1 to count fileList
set thisFile to item i of fileList as text
tell application "Script Editor"
open thisFile as alias
set the selected_text to contents of selection
set the selected_text to the findString
set the findcount to the count of the findString
repeat findcount times
my replaceInScript(findString, replaceString)
end repeat
end tell
end repeat
Thanks again
bills
hi
looks like my repeat is not working properly
I added this
return findcount
it returned 77, should have been 47
changed this
set findString to "\"m1\""
set replaceString to "\"k1\""
returned count was 4, should have been 94
any ideas
thanks bills
set the findcount to the count of the findString
Doing the above only gives you the number of letters in the findString. It doesn’t tell you how many times the findString is found in the entire text.
Nigel and I gave you a working solution. Nigel showed how to get all of the text of a script and I showed how to find/replace in that text. Give it a try.
To clarify the use of ‘text items’, in case you didn’t understand Hank’s handler:
If you set AppleScript’s ‘text item delimiters’ to a piece of text which occurs in another piece of text, the ‘text items’ of the longer text will be the bits in it which occur between instances of the delimiter text. In Hank’s handler, ‘a’ is set to a list of those ‘text items’.
If you coerce a list to text, the items in the list are combined into a single text which is mortared together with instances of the current value of AppleScript’s text item delimiters.
So the usual AppleScript method for find-and-replace, of which Hank’s handler is an example, is:
hi
Hank wrote
I got frustrated and tried something else.
tell application "AppleScript Editor"
repeat with thisFile in scriptFiles
set thisScript to (open thisFile)
set scriptText to thisScript's text--kept getting "The variable thisScript is not defined."
set revisedText to my findReplace(scriptText, searchStr, replaceStr)
set thisScript's text to revisedText
close thisScript saving yes
end repeat
end tell
reread every thing again and realized my mistake
set thisScript to (open thisFile)
set thisScript to (open thisFile as alias)
some times I feel dumber than I look
Nigel wrote
The clarification helped
Thanks
heres the end result
set searchStr to "sort range \"m1\" of worksheet \"hold#2\" key1 (range \"m1\" of worksheet \"hold#2\")"
set replaceStr to "sort range \"k1\" of worksheet \"hold#2\" key1 (range \"k1\" of worksheet \"hold#2\")"
set getPath to "test replace"
tell application "Finder" to set fileList to every file of folder getPath
tell application "Script Editor"
repeat with thisFile in fileList
set thisScript to (open thisFile as alias)
set scriptText to thisScript's text
set revisedText to my findReplace(scriptText, searchStr, replaceStr)
set thisScript's text to revisedText
close thisScript saving yes
end repeat
end tell
on findReplace(scriptText, searchStr, replaceStr)
if scriptText contains searchStr then
set {tids, text item delimiters} to {text item delimiters, searchStr}
set a to text items of scriptText
set text item delimiters to replaceStr
set b to a as text
set text item delimiters to tids
return b
else
return theText
end if
end findReplace
thanks again
bills
Great, you have it working. I understand your issue. It took me a long time to finally figure out that path stuff. I think that may be the most confusing thing for new scripters… paths. There’s so many kinds and it’s confusing. You have strings that are paths and their paths can be separated by colons or slashes… so 2 kinds there. Then you have alias’s, you have Finder paths, then you have file specifications and folder specifications. So that’s a lot to get straight. The problem you had was with the Finder-style paths: file.txt of folder whatever of folder whatever of disk whatever. The problem with them is they only work with the Finder, so to use paths derived by the Finder in another application you have to change them. You’ll notice that Nigel got the selection “as alias list”. That converts them from the Finder style into alias’s right away. Then they’re safe to use outside the Finder. I notice in your code you didn’t do that so that was your issue.