HI folks,
this is my second applescript. it takes the data generated by my first script (also posted on Code Exchange) and alters the units so that they all read kg and Bq (Becquerels) rather than pg,ng, mBq etc. Of course, by changing the units I also needed to update the values in the sheet to reflect the changes. In cases were I wanted to switch from e.g. nanograms to kilograms I have used a multiplier equivalent to 1.0E-(9+3) and for stepping from nBq to Bq I have used a multiplier equivalent to 1.0E-9.
interesting developments for me in this script were the use of records and the avoidance of using autofilter to find the required values.
hope someone finds this script useful
set theFolder to choose folder -- nifty line; asks user to choose folder to work from
tell application "Finder"
set fileList to every file of theFolder
repeat with filename in fileList -- step through all files in user defined folder
open filename -- note that the file is opened by Finder rather than Excel. this is a quirk I haven't been able to get around.
tell application "Microsoft Excel"
activate
tell active sheet
clear range formats used range
end tell -- clears formatting of active sheet. reduces formatting issues when concatenating further in the script.
set found_unit_row to (find row 11 what "unit")
set {tid, AppleScript's text item delimiters} to {AppleScript's text item delimiters, {"$"}}
set column_letter to second text item of (get address of found_unit_row)
set header_row to get end range "d:d" direction toward the bottom -- gets the end of the range, direction right.
print "*** the header row is " & (get address header_row)
set AppleScript's text item delimiters to tid
print "*** column letter is " & column_letter
select found_unit_row
set pg to {name:"pg", unit:"kg", multiplier:1.0E-15}
set ng to {name:"ng", unit:"kg", multiplier:1.0E-12}
set nBq to {name:"nBq", unit:"Bq", multiplier:1.0E-9}
set ug to {name:"ug", unit:"kg", multiplier:1.0E-9}
set uBq to {name:"uBq", unit:"Bq", multiplier:1.0E-6}
set mg to {name:"mg", unit:"kg", multiplier:1.0E-6}
set mBq to {name:"mBq", unit:"Bq", multiplier:1.0E-3}
set g to {name:"g", unit:"kg", multiplier:1.0E-3}
set unitlist to {mg, pg, ng, nBq, ug, uBq, mg, mBq, g}
try
set startCol to ""
set endCol to ""
set foundColumns to false
repeat with unit in unitlist
set currentUnit to name of unit
set newUnit to unit of unit
set multiplier to multiplier of unit
print "*** the current unit is " & currentUnit
print "*** the new unit will be " & newUnit
print "*** the current multiplier is " & multiplier
set findNext to true
--autofilter range range "A11:z50" field 4 criteria1 currentUnit
--copy multiplier to clipboard then to worksheet
tell application "Finder" to set the clipboard to multiplier as string
print "*** clipboard now contains " & (the clipboard)
select cell "e1"
tell application "System Events" to keystroke "v" using {command down}
select cell "e1"
tell application "System Events" to keystroke "c" using {command down}
repeat while findNext is true
try
print "*** current filter value is " & currentUnit
-- find first instance of this unit
set foundCell to (find range (column_letter & ":" & column_letter) what currentUnit look at whole)
set startRow to get first row index of foundCell
print "*** row is " & startRow
if foundColumns is false then
-- do this only first time
-- get column to the right of the found unit cell
set foundAddress to ((get address without column absolute and row absolute) of foundCell)
set foundColIndex to get (first column index of foundCell) + 1
set foundRow to get first row index of foundCell
set startCell to cell foundRow of column foundColIndex
set startAddress to ((get address without column absolute and row absolute) of startCell)
set startCol to get first text item of startAddress
print "*** start column is " & startCol
-- get far right cell address
set theRight to get end range startAddress direction toward the right -- gets the end of the range, direction right.
set rightAddress to ((get address without column absolute and row absolute) of theRight)
set endCol to get first text item of rightAddress
print "*** right end column is " & endCol
set foundColumns to true
end if
paste special range (startCol & startRow & ":" & endCol & startRow) operation paste special operation multiply
set value of foundCell to newUnit
print "XXXXX_______ end of script"
on error errorMsg
print "an error occurred: " & errorMsg
set findNext to false
end try
end repeat
end repeat
on error errorMsg
print "an error occurred: " & errorMsg
end try
--end tell
end tell
end repeat
end tell