Hi all,
I’ve not scripted Excel before and would like to be able to count the number of cells that contain a value then replace that value with another and lastly display a dialog letting the user know the amount of cells that have had a value replaced, here is what I have so far:
on open thefile
set thefilealias to thefile as alias
tell application "Finder" to set original_name to the name of thefilealias
set the_path to (path to desktop as string) & characters 1 thru -5 of original_name & ".txt" as string
tell application "Microsoft Excel"
launch
open thefile
select column 6
set POs to count of cells of selection whose content = "PO"
replace selection what "PO" replacement "PR" with match case
save active workbook in the_path as text Mac file format
close active workbook saving no
end tell
display dialog POs & "POs have been replaced!"
end open
My problem is that I’m not sure of the syntax for counting cells that contain a value. If I remove the set POs line and display dialog line the script replaces all occurences of PO with PR in column 6 without error. I just need help with counting the number of cells that have had values replaced.
Thanks,
Nik
Hi Nik,
Not sure if this will point you in the right direction.
When I execute this on a test worksheet the count of POs is returned.
set poCount to 0
tell application "Microsoft Excel"
set startRow to 1
set endRow to 53
set code_list to value of range ("A" & startRow & ":A" & endRow)
repeat with i in code_list
if (i as text) = "PO" then set poCount to poCount + 1
end repeat
end tell
poCount
Hi Nick,
That seems to be working great. I going to try and incorporate it into my script and I’ll post it back when I’m done! One last question, I noticed you set a variable for the endRow, if I dont know before hand how many rows there are in the workbook do you know any way of telling?
Many Thanks for your help,
Nik
Hi Nick,
Here’s my finished script. I’ve devised a Handler for checking the amount of rows to be processed. I’m sure it’s not the best way to do it so if you have any suggestions for a better way of processing then please let me know.
Thanks again,
Nik
global endRow
on open thefile
set poCount to 0
set thefilealias to thefile as alias
tell application "Finder" to set original_name to the name of thefilealias
set the_path to (path to desktop as string) & characters 1 thru -5 of original_name & ".txt" as string
tell application "Microsoft Excel"
launch
open thefile
my countRows()
select column 6
set startRow to 1
set code_list to value of range ("F" & startRow & ":F" & endRow)
repeat with i in code_list
set x to i as text
if x = "PO" then set poCount to poCount + 1
end repeat
set poCount to poCount as string
replace selection what "PO" replacement "PR" with match case
save active workbook in the_path as text Mac file format
close active workbook saving no
end tell
display dialog (endRow as string) & " rows searched" & return & poCount & " POs have been replaced!" with icon 2
end open
on countRows()
tell application "Microsoft Excel"
select column 6
set endRow to 0
set Fnumber to 1
set Fvalue to "start"
repeat until Fvalue = ""
set Fvalue to value of range ("F" & Fnumber)
set Fnumber to Fnumber + 1
set endRow to endRow + 1
end repeat
end tell
end countRows
I’ve tried a few of the code snippets and they work pretty well.
Assuming you’re working with columns that don’t have any empty cells, apart from the one at the end, they return the reference of the empty cell. If you do have empty cells then building in something that checks the next few cells after the empty one would be an option.
Hope they’re of use.
Regards,
Nick
EDIT: I see you got there just before me. Sorry for the delay, been a little busy.
Hi Nik,
Having looked at both sets of code the main difference I can see is that the code posted on the other thread uses Excel’s ‘find’ command which is explained in detail on pages 355-357 of the Excel 2004 Scripting Reference.
If there are quite a few cells to search this may work quicker.
I’ve encountered this command before when writing some VBA for Excel.
