tell application "Microsoft Excel"
activate
open text file filename "x"
activate object workbook "x"
accept all changes active workbook
activate object worksheet 1
Then I paste from Excel workbook to script editor
replace range “A1:G1000” of worksheet “Sheet1” what " 101 " replacement " 4 " search order by rows
replace range “A1:G1000” of worksheet “Sheet1” what " 102 " replacement " 17 " search order by rows
replace range “A1:G1000” of worksheet “Sheet1” what " 103 " replacement " 37 " search order by rows
replace range “A1:G1000” of worksheet “Sheet1” what " 104 " replacement " 28 " search order by rows
replace range “A1:G1000” of worksheet “Sheet1” what " 105 " replacement " 34 " search order by rows
Which compiles to:
replace range "A1:G1000" of worksheet "Sheet1" what "\t101\t" replacement "\t4\t" search order by rows
replace range "A1:G1000" of worksheet "Sheet1" what "\t102\t" replacement "\t17\t" search order by rows
replace range "A1:G1000" of worksheet "Sheet1" what "\t103\t" replacement "\t37\t" search order by rows
replace range "A1:G1000" of worksheet "Sheet1" what "\t104\t" replacement "\t28\t" search order by rows
replace range "A1:G1000" of worksheet "Sheet1" what "\t105\t" replacement "\t34\t" search order by rows
Which does nothing in excel,but if I go into script edit find, \t,leave replace blank and recompile, I get this,which works if Excel find and replace is preset to find entire cells only
replace range "A1:G1000" of worksheet "Sheet1" what "101" replacement "4" search order by rows
replace range "A1:G1000" of worksheet "Sheet1" what "102" replacement "17" search order by rows
replace range "A1:G1000" of worksheet "Sheet1" what "103" replacement "37" search order by rows
replace range "A1:G1000" of worksheet "Sheet1" what "104" replacement "28" search order by rows
replace range "A1:G1000" of worksheet "Sheet1" what "105" replacement "34" search order by rows
This is step 1
Step 2 replaces 34 with 1034, column G is autosum
Question 1: How do I get script to compile right the first time?
Question 2: How do I set Excel find and replace to entire cells only?
I can do it with case by search for rows with match case,
But does’t compile with match cell
Question 3: How do I search column G for sums>or<than and delete entire row? (this one leaves me totally in the dark)
Thanks for any help
bills
p.s. this is all new to me
.
Model: macbook
AppleScript: 2008
Browser: Safari 525.27.1
Operating System: Mac OS X (10.5)
#2) To make it find whole cells only use the “look at” argument (whole and part are the two options.)
tell application "Microsoft Excel"
replace range "A1:A5" what "rat" replacement "cat" look at whole
end tell
#3) you could either loop through cells in the column (bottom up is needed if you are deleting as you go)
OR
you could make a helper column of = (1 / RC7 ) and the use Special Cells to find the errors and delete those rows.
tell application "Microsoft Excel"
set cellCount to count of cells of range "G:G" of active sheet
set valueCutOff to 5
set rowsCount to first row index of (get end cell cellCount of range "G:G" of active sheet direction toward the top)
set lastCol to count of columns of used range of active sheet
set formulaCells to get resize (cell 1 of column (lastCol + 2) of active sheet) row size rowsCount
set formula r1c1 of formulaCells to "=1/(rc7<" & valueCutOff & ")"
try
delete range entire row of (special cells formulaCells type cell type formulas value errors) shift shift up
end try
try
delete range entire column of formulaCells
end try
end tell
tell application "Microsoft Excel"
replace range "A1:A5" what "rat" replacement "cat" look at whole
end tell
this works lovely
thanks
tell application "Microsoft Excel"
set cellCount to count of cells of range "G:G" of active sheet
set valueCutOff to 5
set rowsCount to first row index of (get end cell cellCount of range "G:G" of active sheet direction toward the top)
set lastCol to count of columns of used range of active sheet
set formulaCells to get resize (cell 1 of column (lastCol + 2) of active sheet) row size rowsCount
set formula r1c1 of formulaCells to "=1/(rc7<" & valueCutOff & ")"
try
delete range entire row of (special cells formulaCells type cell type formulas value errors) shift shift up
end try
try
delete range entire column of formulaCells
end try
end tell
I tried this, but only 1 row was deleted, I think it has possibilities,will play with it some more.
thanks bills
This is a tighter version of the routine.
It should keep only those rows whose column G entry is < 5.
I’m using Excel 2004.
tell application "Microsoft Excel"
-- deletes rows whose column G entry is less than valueCutOff
set columnToTest to range "G:G" of active sheet -- adjust
set valueCutOff to 5 -- adjust
set cellCount to count of cells of columnToTest
set usedRanges to used range of worksheet object of columnToTest
set rowsCount to first row index of (get end cell cellCount of columnToTest direction toward the top)
set lastCol to (first column index of usedRanges) + (count of columns of usedRanges)
set formulaCells to get resize (cell 1 of column (lastCol + 2) of active sheet) row size rowsCount
set formula r1c1 of formulaCells to ¬
"=1/(rc" & (first column index of columnToTest) ¬
& "<" & valueCutOff & ")"
try
delete range entire row of (special cells formulaCells type cell type formulas value errors)
end try
try
delete range entire column of formulaCells
end try
end tell
hi
I tried the tighter version, same results, 1 row deleted.
Here is what I ran
tell application "Microsoft Excel"
activate
open text file filename "x"
activate object workbook "x"
accept all changes active workbook
activate object worksheet 1
replace range "A1:G1000" of worksheet "Sheet1" what "101" replacement "4" search order by rows
replace range "A1:G1000" of worksheet "Sheet1" what "102" replacement "17" search order by rows
replace range "A1:G1000" of worksheet "Sheet1" what "103" replacement "37" search order by rows
replace range "A1:G1000" of worksheet "Sheet1" what "104" replacement "28" search order by rows
replace range "A1:G1000" of worksheet "Sheet1" what "105" replacement "34" search order by rows
replace range "A1:G1000" of worksheet "Sheet1" what "201" replacement "11" search order by rows
replace range "A1:G1000" of worksheet "Sheet1" what "202" replacement "15" search order by rows
replace range "A1:G1000" of worksheet "Sheet1" what "203" replacement "16" search order by rows
replace range "A1:G1000" of worksheet "Sheet1" what "204" replacement "31" search order by rows
replace range "A1:G1000" of worksheet "Sheet1" what "205" replacement "39" search order by rows
replace range "A1:G1000" of worksheet "Sheet1" what "301" replacement "8" search order by rows
replace range "A1:G1000" of worksheet "Sheet1" what "302" replacement "24" search order by rows
replace range "A1:G1000" of worksheet "Sheet1" what "303" replacement "26" search order by rows
replace range "A1:G1000" of worksheet "Sheet1" what "304" replacement "41" search order by rows
replace range "A1:G1000" of worksheet "Sheet1" what "305" replacement "2" search order by rows
replace range "A1:G1000" of worksheet "Sheet1" what "401" replacement "13" search order by rows
replace range "A1:G1000" of worksheet "Sheet1" what "402" replacement "19" search order by rows
replace range "A1:G1000" of worksheet "Sheet1" what "403" replacement "30" search order by rows
replace range "A1:G1000" of worksheet "Sheet1" what "404" replacement "32" search order by rows
replace range "A1:G1000" of worksheet "Sheet1" what "405" replacement "38" search order by rows
replace range "A1:G1000" of worksheet "Sheet1" what "501" replacement "25" search order by rows
replace range "A1:G1000" of worksheet "Sheet1" what "502" replacement "33" search order by rows
replace range "A1:G1000" of worksheet "Sheet1" what "503" replacement "23" search order by rows
replace range "A1:G1000" of worksheet "Sheet1" what "504" replacement "36" search order by rows
replace range "A1:G1000" of worksheet "Sheet1" what "505" replacement "45" search order by rows
replace range "A1:G1000" of worksheet "Sheet1" what "1" replacement "1" search order by rows
replace range "A1:G1000" of worksheet "Sheet1" what "2" replacement "2" search order by rows
replace range "A1:G1000" of worksheet "Sheet1" what "3" replacement "3" search order by rows
replace range "A1:G1000" of worksheet "Sheet1" what "4" replacement "1004" search order by rows
replace range "A1:G1000" of worksheet "Sheet1" what "5" replacement "5" search order by rows
replace range "A1:G1000" of worksheet "Sheet1" what "6" replacement "6" search order by rows
replace range "A1:G1000" of worksheet "Sheet1" what "7" replacement "7" search order by rows
replace range "A1:G1000" of worksheet "Sheet1" what "8" replacement "1008" search order by rows
replace range "A1:G1000" of worksheet "Sheet1" what "9" replacement "1009" search order by rows
replace range "A1:G1000" of worksheet "Sheet1" what "10" replacement "10" search order by rows
replace range "A1:G1000" of worksheet "Sheet1" what "11" replacement "11" search order by rows
replace range "A1:G1000" of worksheet "Sheet1" what "12" replacement "1012" search order by rows
replace range "A1:G1000" of worksheet "Sheet1" what "13" replacement "13" search order by rows
replace range "A1:G1000" of worksheet "Sheet1" what "14" replacement "14" search order by rows
replace range "A1:G1000" of worksheet "Sheet1" what "15" replacement "15" search order by rows
replace range "A1:G1000" of worksheet "Sheet1" what "16" replacement "16" search order by rows
replace range "A1:G1000" of worksheet "Sheet1" what "17" replacement "17" search order by rows
replace range "A1:G1000" of worksheet "Sheet1" what "18" replacement "18" search order by rows
replace range "A1:G1000" of worksheet "Sheet1" what "19" replacement "1019" search order by rows
replace range "A1:G1000" of worksheet "Sheet1" what "20" replacement "20" search order by rows
replace range "A1:G1000" of worksheet "Sheet1" what "21" replacement "21" search order by rows
replace range "A1:G1000" of worksheet "Sheet1" what "22" replacement "1022" search order by rows
replace range "A1:G1000" of worksheet "Sheet1" what "23" replacement "23" search order by rows
replace range "A1:G1000" of worksheet "Sheet1" what "24" replacement "24" search order by rows
replace range "A1:G1000" of worksheet "Sheet1" what "25" replacement "1025" search order by rows
replace range "A1:G1000" of worksheet "Sheet1" what "26" replacement "26" search order by rows
replace range "A1:G1000" of worksheet "Sheet1" what "27" replacement "27" search order by rows
replace range "A1:G1000" of worksheet "Sheet1" what "28" replacement "28" search order by rows
replace range "A1:G1000" of worksheet "Sheet1" what "29" replacement "29" search order by rows
replace range "A1:G1000" of worksheet "Sheet1" what "30" replacement "30" search order by rows
replace range "A1:G1000" of worksheet "Sheet1" what "31" replacement "31" search order by rows
replace range "A1:G1000" of worksheet "Sheet1" what "32" replacement "32" search order by rows
replace range "A1:G1000" of worksheet "Sheet1" what "33" replacement "1033" search order by rows
replace range "A1:G1000" of worksheet "Sheet1" what "34" replacement "34" search order by rows
replace range "A1:G1000" of worksheet "Sheet1" what "35" replacement "1035" search order by rows
replace range "A1:G1000" of worksheet "Sheet1" what "36" replacement "36" search order by rows
replace range "A1:G1000" of worksheet "Sheet1" what "37" replacement "37" search order by rows
replace range "A1:G1000" of worksheet "Sheet1" what "38" replacement "38" search order by rows
replace range "A1:G1000" of worksheet "Sheet1" what "39" replacement "1039" search order by rows
replace range "A1:G1000" of worksheet "Sheet1" what "40" replacement "40" search order by rows
replace range "A1:G1000" of worksheet "Sheet1" what "41" replacement "41" search order by rows
replace range "A1:G1000" of worksheet "Sheet1" what "42" replacement "1042" search order by rows
replace range "A1:G1000" of worksheet "Sheet1" what "43" replacement "43" search order by rows
replace range "A1:G1000" of worksheet "Sheet1" what "44" replacement "44" search order by rows
replace range "A1:G1000" of worksheet "Sheet1" what "45" replacement "45" search order by rows
replace range "A1:G1000" of worksheet "Sheet1" what "46" replacement "1046" search order by rows
replace range "A1:G1000" of worksheet "Sheet1" what "47" replacement "47" search order by rows
tell application "Microsoft Excel"
-- deletes rows whose column G entry is less than valueCutOff
set columnToTest to range "G1:G1000" of active sheet -- adjust
set valueCutOff to 4000 -- adjust
set cellCount to count of cells of columnToTest
set usedRanges to used range of worksheet object of columnToTest
set rowsCount to first row index of (get end cell cellCount of columnToTest direction toward the top)
set lastCol to (first column index of usedRanges) + (count of columns of usedRanges)
set formulaCells to get resize (cell 1 of column (lastCol + 2) of active sheet) row size rowsCount
set formula r1c1 of formulaCells to ¬
"=1/(rc" & (first column index of columnToTest) ¬
& "<" & valueCutOff & ")"
try
delete range entire row of (special cells formulaCells type cell type formulas value errors)
end try
try
delete range entire column of formulaCells
end try
end tell
end tell
here is what my event log gave
I appeiciate effort and help your giving me. don’t have much time to play with this untill friday.
thanks again
bills
The routine assumes that columnToTest is the entire column G:G and that G65536 is empty.
In the above routine, if G1000 is not empty, you could be getting that result.
Try changing
set columnToTest to range "G1:G1000" of active sheet
to
set columnToTest to range "G:G" of active sheet
or
set columnToTest to range "G1:G1000" of active sheet
set columnToTest to entire column of columnToTest
or
leave that part alone and try
-- columnToTest = G1:G1000
-- other code
set formulaCells to get resize (cell 1 of column (lastCol + 2) of active sheet) row size 1000
-- set formulaCells to get resize (cell 1 of column (lastCol + 2) of active sheet) row size (count of rows of columnToTest
(By the way, instead of hardcoding all those replacemnts, a VLOOKUP table would be easier to impliment and maintain.
(pseudo script)-- Addhelper column
set formula r1c1 of helperColum to "=IF(ISNA(VLOOKUP(RC7,lookUpTable,2,False)), RC7, VLOOKUP(RC7,lookUpTable,2,False))"
set Value of "G1:G100" to Value of helperColumn
delete range entire column of helperColumn
Any changes to the replace what with what could be done at the lookUpTable rather than editing the applescript.)
Hi
I have tried all your versions, played with them some more. I got them all to work by running a sort order before them.
tell application "Microsoft Excel"
activate object workbook "x"
accept all changes active workbook
activate object worksheet 1
sort range "G1" of worksheet "Sheet1" key1 (range "G1" of worksheet "Sheet1")
end tell
tell application "Microsoft Excel"
activate object workbook "x"
accept all changes active workbook
activate object worksheet 1
set columnToTest to range "G:G" of active sheet -- adjust
set maxvalueCutOff to 4000 -- adjust
set cellCount to count of cells of columnToTest
set usedRanges to used range of worksheet object of columnToTest
set rowsCount to first row index of (get end cell cellCount of columnToTest direction toward the top)
set lastCol to (first column index of usedRanges) + (count of columns of usedRanges)
set formulaCells to get resize (cell 1 of column (lastCol + 2) of active sheet) row size rowsCount
set formula r1c1 of formulaCells to ¬
"=1/(rc" & (first column index of columnToTest) ¬
& "<" & maxvalueCutOff & ")"
try
delete range entire row of (special cells formulaCells type cell type formulas value errors)
end try
try
delete range entire column of formulaCells
end try
end tell
as to why sort first,I don’t know, but it works,maybe as I learn more I might figure it out.
as to
(pseudo script)-- Addhelper column
set formula r1c1 of helperColum to "=IF(ISNA(VLOOKUP(RC7,lookUpTable,2,False)), RC7, VLOOKUP(RC7,lookUpTable,2,False))"
set Value of "G1:G100" to Value of helperColumn
delete range entire column of helperColumn
it is beyond my understanding at the moment.
thanks for your help
bills
The last part is about those many replacements. I’ve found it easier to maintain lists like that if they are in Excel cells, rather than hard coded into a script.
The native Excel function VLOOKUP is designed to do what those many Replace commands do.
Hi I ran into a couple problems,sense I’m running many lists, I made them into subroutines
tell application "Finder"
activate
select window of desktop
open document file "X 1" of folder "TEST 2" of folder "Documents" of folder "bestbuy" of folder "Users" of startup disk
tell application "Microsoft Excel"
activate
accept all changes active workbook
activate object worksheet 1
my subTemp()
my sub1wk()
my subMax3()
my subMin1()
my subReal()
my sub13
my subMax1
my subReal
my subVwk()
my subMax5()
my subMin5()
my subReal()
end tell
end tell
tell application "Finder"
activate
select window of desktop
open document file "X 2" of folder "TEST 2" of folder "Documents" of folder "bestbuy" of folder "Users" of startup disk
tell application "Microsoft Excel"
activate
accept all changes active workbook
activate object worksheet 1
my subTemp()
my sub1wk()
my subMax3()
my subMin1()
my subReal()
my sub13
my subMax1
my subReal
my subVwk()
my subMax5()
my subMin5()
my subReal()
end tell
end tell
on sub13 everything in the workbook is deleated, the script gets hung up at the next sort.
I tried if statements to get me to next finder item, most of what I tried wouldn’t compile,
and what compiled didn’t produce any results.
The other problem I have is to replace pairs of numbers in a row, e.g. 1,2 or 3,4 both pair must be in same row, if only one, no replacement.
I appreciate any help, this is getting very frustrating
thanks bills
tell application "Microsoft Excel"
activate
accept all changes active workbook
activate object worksheet 1
activate object range "H1"
if get value of range "H1" is "" then
set value of active cell to 7000--ajustable
end if
(* put script here*)
end
I added it to the front of my subMax, it gave Excel something to sort,though the rest of the script is wasted time on the rest of this workbook. But it will get me to the next workbook(file)
You were right,many typos.
ended up with this, no more typos
.
on sub101()
tell application "Finder"
activate
open document file "REAL.xlsx" of folder "Documents" of folder "bestbuy" of folder "Users" of startup disk
end tell
tell application "Microsoft Excel"
activate
get value of range "A1"
if get value of range "A1" is "" then
set getValue1 to "2000"
else
set getValue1 to value of range "A1"
end if
get value of range "B1"
if get value of range "B1" is "" then
set getValue2 to "2000"
else
set getValue2 to value of range "B1"
end if
get value of range "C1"
if get value of range "C1" is "" then
set getValue3 to "2000"
else
set getValue3 to value of range "C1"
end if
get value of range "D1"
if get value of range "D1" is "" then
set getValue4 to "2000"
else
set getValue4 to value of range "D1"
end if
get value of range "E1"
if get value of range "E1" is "" then
set getValue5 to "2000"
else
set getValue5 to value of range "E1"
end if
get value of range "F1"
if get value of range "F1" is "" then
set getValue6 to "2000"
else
set getValue6 to value of range "F1"
end if
get value of range "G1"
if get value of range "G1" is "" then
set getValue7 to "2000"
else
set getValue7 to value of range "G1"
end if
end tell
tell application "Microsoft Excel"
save active workbook
close active workbook
end tell
tell application "Finder"
set getPath to "TEST"
set fileList to every file of folder getPath
set loopFinish to count fileList
repeat with i from 1 to number of items in the fileList
set thisFile to item i of the fileList
open thisFile
tell application "Microsoft Excel"
replace range "A:G" of worksheet "Sheet1" what "101" replacement getValue1 search order by rows look at whole
replace range "A:G" of worksheet "Sheet1" what "102" replacement getValue2 search order by rows look at whole
replace range "A:G" of worksheet "Sheet1" what "103" replacement getValue3 search order by rows look at whole
replace range "A:G" of worksheet "Sheet1" what "104" replacement getValue4 search order by rows look at whole
replace range "A:G" of worksheet "Sheet1" what "105" replacement getValue5 search order by rows look at whole
replace range "A:G" of worksheet "Sheet1" what "106" replacement getValue6 search order by rows look at whole
replace range "A:G" of worksheet "Sheet1" what "107" replacement getValue7 search order by rows look at whole
end tell
tell application "Microsoft Excel"
activate object range "A1"
if get value of range "A1" is not "" then
save active workbook
close active workbook
else
tell application "Finder"
delete thisFile
tell application "Microsoft Excel"
close active workbook saving no
set screen updating to true
end tell
end tell
end if
end tell
end repeat
end tell
end sub101
on sub201()
tell application "Finder"
activate
open document file "REAL.xlsx" of folder "Documents" of folder "bestbuy" of folder "Users" of startup disk
end tell
tell application "Microsoft Excel"
activate
get value of range "A2"
if get value of range "A2" is "" then
set getValue1 to "2000"
else
set getValue1 to value of range "A2"
end if
get value of range "B2"
if get value of range "B2" is "" then
set getValue2 to "2000"
else
set getValue2 to value of range "B2"
end if
get value of range "C2"
if get value of range "C2" is "" then
set getValue3 to "2000"
else
set getValue3 to value of range "C2"
end if
get value of range "D2"
if get value of range "D2" is "" then
set getValue4 to "2000"
else
set getValue4 to value of range "D2"
end if
get value of range "E2"
if get value of range "E2" is "" then
set getValue5 to "2000"
else
set getValue5 to value of range "E2"
end if
get value of range "F2"
if get value of range "F2" is "" then
set getValue6 to "2000"
else
set getValue6 to value of range "F2"
end if
get value of range "G2"
if get value of range "G2" is "" then
set getValue7 to "2000"
else
set getValue7 to value of range "G2"
end if
end tell
tell application "Microsoft Excel"
save active workbook
close active workbook
end tell
tell application "Finder"
set getPath to "TEST"
set fileList to every file of folder getPath
set loopFinish to count fileList
repeat with i from 1 to number of items in the fileList
set thisFile to item i of the fileList
open thisFile
tell application "Microsoft Excel"
replace range "A:G" of worksheet "Sheet1" what "201" replacement getValue1 search order by rows look at whole
replace range "A:G" of worksheet "Sheet1" what "202" replacement getValue2 search order by rows look at whole
replace range "A:G" of worksheet "Sheet1" what "203" replacement getValue3 search order by rows look at whole
replace range "A:G" of worksheet "Sheet1" what "204" replacement getValue4 search order by rows look at whole
replace range "A:G" of worksheet "Sheet1" what "205" replacement getValue5 search order by rows look at whole
replace range "A:G" of worksheet "Sheet1" what "206" replacement getValue6 search order by rows look at whole
replace range "A:G" of worksheet "Sheet1" what "207" replacement getValue7 search order by rows look at whole
end tell
tell application "Microsoft Excel"
activate object range "A1"
if get value of range "A1" is not "" then
save active workbook
close active workbook
else
tell application "Finder"
delete thisFile
tell application "Microsoft Excel"
close active workbook saving no
set screen updating to true
end tell
end tell
end if
end tell
end repeat
end tell
end sub201
on sub301()
tell application "Finder"
activate
open document file "REAL.xlsx" of folder "Documents" of folder "bestbuy" of folder "Users" of startup disk
end tell
tell application "Microsoft Excel"
activate
get value of range "A3"
if get value of range "A3" is "" then
set getValue1 to "2000"
else
set getValue1 to value of range "A3"
end if
get value of range "B3"
if get value of range "B3" is "" then
set getValue2 to "2000"
else
set getValue2 to value of range "B3"
end if
get value of range "C3"
if get value of range "C3" is "" then
set getValue3 to "2000"
else
set getValue3 to value of range "C3"
end if
get value of range "D3"
if get value of range "D3" is "" then
set getValue4 to "2000"
else
set getValue4 to value of range "D3"
end if
get value of range "E3"
if get value of range "E3" is "" then
set getValue5 to "2000"
else
set getValue5 to value of range "E3"
end if
get value of range "F3"
if get value of range "F3" is "" then
set getValue6 to "2000"
else
set getValue6 to value of range "F3"
end if
get value of range "G3"
if get value of range "G3" is "" then
set getValue7 to "2000"
else
set getValue7 to value of range "G3"
end if
end tell
tell application "Microsoft Excel"
save active workbook
close active workbook
end tell
tell application "Finder"
set getPath to "TEST" -- the "root" folder of the Finder IS the desktop
set fileList to every file of folder getPath
set loopFinish to count fileList
repeat with i from 1 to number of items in the fileList
set thisFile to item i of the fileList
open thisFile
tell application "Microsoft Excel"
replace range "A:G" of worksheet "Sheet1" what "301" replacement getValue1 search order by rows look at whole
replace range "A:G" of worksheet "Sheet1" what "302" replacement getValue2 search order by rows look at whole
replace range "A:G" of worksheet "Sheet1" what "303" replacement getValue3 search order by rows look at whole
replace range "A:G" of worksheet "Sheet1" what "304" replacement getValue4 search order by rows look at whole
replace range "A:G" of worksheet "Sheet1" what "305" replacement getValue5 search order by rows look at whole
replace range "A:G" of worksheet "Sheet1" what "306" replacement getValue6 search order by rows look at whole
replace range "A:G" of worksheet "Sheet1" what "307" replacement getValue7 search order by rows look at whole
end tell
tell application "Microsoft Excel"
activate object range "A1"
if get value of range "A1" is not "" then
save active workbook
close active workbook
else
tell application "Finder"
delete thisFile
tell application "Microsoft Excel"
close active workbook saving no
set screen updating to true
end tell
end tell
end if
end tell
end repeat
end tell
end sub301
on sub401()
tell application "Finder"
activate
open document file "REAL.xlsx" of folder "Documents" of folder "bestbuy" of folder "Users" of startup disk
end tell
tell application "Microsoft Excel"
activate
get value of range "A4"
if get value of range "A4" is "" then
set getValue1 to "2000"
else
set getValue1 to value of range "A4"
end if
get value of range "B4"
if get value of range "B4" is "" then
set getValue2 to "2000"
else
set getValue2 to value of range "B4"
end if
get value of range "C4"
if get value of range "C4" is "" then
set getValue3 to "2000"
else
set getValue3 to value of range "C4"
end if
get value of range "D4"
if get value of range "D4" is "" then
set getValue4 to "2000"
else
set getValue4 to value of range "D4"
end if
get value of range "E4"
if get value of range "E4" is "" then
set getValue5 to "2000"
else
set getValue5 to value of range "E4"
end if
get value of range "F4"
if get value of range "F4" is "" then
set getValue6 to "2000"
else
set getValue6 to value of range "F4"
end if
get value of range "G4"
if get value of range "G4" is "" then
set getValue7 to "2000"
else
set getValue7 to value of range "G4"
end if
end tell
tell application "Microsoft Excel"
save active workbook
close active workbook
end tell
tell application "Finder"
set getPath to "TEST" -- the "root" folder of the Finder IS the desktop
set fileList to every file of folder getPath
set loopFinish to count fileList
repeat with i from 1 to number of items in the fileList
set thisFile to item i of the fileList
open thisFile
tell application "Microsoft Excel"
replace range "A:G" of worksheet "Sheet1" what "401" replacement getValue1 search order by rows look at whole
replace range "A:G" of worksheet "Sheet1" what "402" replacement getValue2 search order by rows look at whole
replace range "A:G" of worksheet "Sheet1" what "403" replacement getValue3 search order by rows look at whole
replace range "A:G" of worksheet "Sheet1" what "404" replacement getValue4 search order by rows look at whole
replace range "A:G" of worksheet "Sheet1" what "405" replacement getValue5 search order by rows look at whole
replace range "A:G" of worksheet "Sheet1" what "406" replacement getValue6 search order by rows look at whole
replace range "A:G" of worksheet "Sheet1" what "407" replacement getValue7 search order by rows look at whole
end tell
tell application "Microsoft Excel"
activate object range "A1"
if get value of range "A1" is not "" then
save active workbook
close active workbook
else
tell application "Finder"
delete thisFile
tell application "Microsoft Excel"
close active workbook saving no
set screen updating to true
end tell
end tell
end if
end tell
end repeat
end tell
end sub401
on sub501()
tell application "Finder"
activate
open document file "REAL.xlsx" of folder "Documents" of folder "bestbuy" of folder "Users" of startup disk
end tell
tell application "Microsoft Excel"
activate
get value of range "A5"
if get value of range "A5" is "" then
set getValue1 to "2000"
else
set getValue1 to value of range "A5"
end if
get value of range "B5"
if get value of range "B5" is "" then
set getValue2 to "2000"
else
set getValue2 to value of range "B5"
end if
get value of range "C5"
if get value of range "C5" is "" then
set getValue3 to "2000"
else
set getValue3 to value of range "C5"
end if
get value of range "D5"
if get value of range "D5" is "" then
set getValue4 to "2000"
else
set getValue4 to value of range "D5"
end if
get value of range "E5"
if get value of range "E5" is "" then
set getValue5 to "2000"
else
set getValue5 to value of range "E5"
end if
get value of range "F5"
if get value of range "F5" is "" then
set getValue6 to "2000"
else
set getValue6 to value of range "F5"
end if
get value of range "G5"
if get value of range "G5" is "" then
set getValue7 to "2000"
else
set getValue7 to value of range "G5"
end if
end tell
tell application "Microsoft Excel"
save active workbook
close active workbook
end tell
tell application "Finder"
set getPath to "TEST" -- the "root" folder of the Finder IS the desktop
set fileList to every file of folder getPath
set loopFinish to count fileList
repeat with i from 1 to number of items in the fileList
set thisFile to item i of the fileList
open thisFile
tell application "Microsoft Excel"
replace range "A:G" of worksheet "Sheet1" what "501" replacement getValue1 search order by rows look at whole
replace range "A:G" of worksheet "Sheet1" what "502" replacement getValue2 search order by rows look at whole
replace range "A:G" of worksheet "Sheet1" what "503" replacement getValue3 search order by rows look at whole
replace range "A:G" of worksheet "Sheet1" what "504" replacement getValue4 search order by rows look at whole
replace range "A:G" of worksheet "Sheet1" what "505" replacement getValue5 search order by rows look at whole
replace range "A:G" of worksheet "Sheet1" what "506" replacement getValue6 search order by rows look at whole
replace range "A:G" of worksheet "Sheet1" what "507" replacement getValue7 search order by rows look at whole
end tell
tell application "Microsoft Excel"
activate object range "A1"
if get value of range "A1" is not "" then
save active workbook
close active workbook
else
tell application "Finder"
delete thisFile
tell application "Microsoft Excel"
close active workbook saving no
set screen updating to true
end tell
end tell
end if
end tell
end repeat
end tell
end sub501
on sub601()
tell application "Finder"
activate
open document file "REAL.xlsx" of folder "Documents" of folder "bestbuy" of folder "Users" of startup disk
end tell
tell application "Microsoft Excel"
activate
get value of range "A6"
if get value of range "A6" is "" then
set getValue1 to "2000"
else
set getValue1 to value of range "A6"
end if
get value of range "B6"
if get value of range "B6" is "" then
set getValue2 to "2000"
else
set getValue2 to value of range "B6"
end if
get value of range "C6"
if get value of range "C6" is "" then
set getValue3 to "2000"
else
set getValue3 to value of range "C6"
end if
get value of range "D6"
if get value of range "D6" is "" then
set getValue4 to "2000"
else
set getValue4 to value of range "D6"
end if
get value of range "E6"
if get value of range "E6" is "" then
set getValue5 to "2000"
else
set getValue5 to value of range "E6"
end if
get value of range "F6"
if get value of range "F6" is "" then
set getValue6 to "2000"
else
set getValue6 to value of range "F6"
end if
get value of range "G6"
if get value of range "G6" is "" then
set getValue7 to "2000"
else
set getValue7 to value of range "G6"
end if
get value of range "H6"
if get value of range "H6" is "" then
set getValue8 to "2000"
else
set getValue8 to value of range "H6"
end if
get value of range "I6"
if get value of range "I6" is "" then
set getValue9 to "2000"
else
set getValue9 to value of range "I6"
end if
get value of range "J6"
if get value of range "J6" is "" then
set getValue10 to "2000"
else
set getValue10 to value of range "J6"
end if
get value of range "K6"
if get value of range "K6" is "" then
set getValue11 to "2000"
else
set getValue11 to value of range "K6"
end if
get value of range "L6"
if get value of range "L6" is "" then
set getValue12 to "2000"
else
set getValue12 to value of range "L6"
end if
end tell
tell application "Microsoft Excel"
save active workbook
close active workbook
end tell
tell application "Finder"
set getPath to "TEST" -- the "root" folder of the Finder IS the desktop
set fileList to every file of folder getPath
set loopFinish to count fileList
repeat with i from 1 to number of items in the fileList
set thisFile to item i of the fileList
open thisFile
tell application "Microsoft Excel"
replace range "A:G" of worksheet "Sheet1" what "601" replacement getValue1 search order by rows look at whole
replace range "A:G" of worksheet "Sheet1" what "602" replacement getValue2 search order by rows look at whole
replace range "A:G" of worksheet "Sheet1" what "603" replacement getValue3 search order by rows look at whole
replace range "A:G" of worksheet "Sheet1" what "604" replacement getValue4 search order by rows look at whole
replace range "A:G" of worksheet "Sheet1" what "605" replacement getValue5 search order by rows look at whole
replace range "A:G" of worksheet "Sheet1" what "606" replacement getValue6 search order by rows look at whole
replace range "A:G" of worksheet "Sheet1" what "607" replacement getValue7 search order by rows look at whole
replace range "A:G" of worksheet "Sheet1" what "608" replacement getValue8 search order by rows look at whole
replace range "A:G" of worksheet "Sheet1" what "609" replacement getValue9 search order by rows look at whole
replace range "A:G" of worksheet "Sheet1" what "610" replacement getValue10 search order by rows look at whole
replace range "A:G" of worksheet "Sheet1" what "611" replacement getValue11 search order by rows look at whole
replace range "A:G" of worksheet "Sheet1" what "612" replacement getValue12 search order by rows look at whole
end tell
tell application "Microsoft Excel"
activate object range "A1"
if get value of range "A1" is not "" then
save active workbook
close active workbook
else
tell application "Finder"
delete thisFile
tell application "Microsoft Excel"
close active workbook saving no
set screen updating to true
end tell
end tell
end if
end tell
end repeat
end tell
end sub601
on deleteFiller()
tell application "Finder"
activate
set getPath to "TEST"
set fileList to every file of folder getPath
set loopFinish to count fileList
repeat with i from 1 to number of items in the fileList
set thisFile to item i of the fileList
open thisFile
tell application "Microsoft Excel"
activate
set screen updating to false
accept all changes active workbook
set columnToTest to range "G:G" of active sheet -- adjust
set cellCount to count of cells of columnToTest
set usedRanges to used range of worksheet object of columnToTest
set rowsCount to first row index of (get end cell cellCount of columnToTest direction toward the top)
set lastCol to (first column index of usedRanges) + (count of columns of usedRanges)
set formulaCells to get resize (cell 1 of column (lastCol) of active sheet) row size rowsCount
set formula r1c1 of formulaCells to ¬
"=SUM(RC[-7]:RC[-1])"
end tell
tell application "Microsoft Excel"
activate
accept all changes active workbook
activate object worksheet 1
sort range "H1" of worksheet "Sheet1" key1 (range "H1" of worksheet "Sheet1")
end tell
tell application "Microsoft Excel"
activate
accept all changes active workbook
activate object worksheet 1
set columnToTest to range "H:H" of active sheet -- adjust
set maxvalueCutOff to 1000 -- adjust
set cellCount to count of cells of columnToTest
set usedRanges to used range of worksheet object of columnToTest
set rowsCount to first row index of (get end cell cellCount of columnToTest direction toward the top)
set lastCol to (first column index of usedRanges) + (count of columns of usedRanges)
set formulaCells to get resize (cell 1 of column (lastCol + 2) of active sheet) row size rowsCount
set formula r1c1 of formulaCells to ¬
"=1/(rc" & (first column index of columnToTest) ¬
& "<" & maxvalueCutOff & ")"
try
delete range entire row of (special cells formulaCells type cell type formulas value errors)
end try
try
delete range entire column of formulaCells
end try
end tell
tell application "Microsoft Excel"
activate
set screen updating to true
accept all changes active workbook
activate object worksheet 1
activate object range "A1"
if get value of range "A1" is not "" then
save active workbook
close active workbook
else
delete thisFile
close active workbook saving no
end if
end tell
end repeat
end tell
end deleteFiller
tell application "Microsoft Excel"
activate
set screen updating to false
my sub101()
my sub201()
my sub301()
my sub401()
my sub501()
my sub601()
my deleteFiller()
set screen updating to true
end tell