Is there a way to speed this script up?
100 files takes 35 min., I got 14000 files and 20 similar scripts.
on sub_total()
tell application "Microsoft Excel"
delete range range "H:H" shift shift up
set columnToTest to last column of used range of active sheet
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 bottom)
set lastCol to (first column index of usedRanges) + (count of columns of usedRanges)
set totalCells to get resize (cell 1 of column (lastCol) of active sheet) row size rowsCount
set formula r1c1 of totalCells to ¬
"=SUM(RC[-7]:RC[-1])"
end tell
end sub_total
set CR to ASCII character 13
set NL to ASCII character 10
set getPath to "28#remove duplicates"
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 "Microsoft Excel"
launch
set screen updating to false
open thisFile
if get value of range "A1" is "" then
delete thisFile
close active workbook saving no
else
my sub_total()
replace range "A:G" of worksheet "Sheet1" what "1" replacement "1001" search order by rows look at whole
replace range "A:G" of worksheet "Sheet1" what "2" replacement "1002" search order by rows look at whole
replace range "A:G" of worksheet "Sheet1" what "3" replacement "1003" search order by rows look at whole
replace range "A:G" of worksheet "Sheet1" what "4" replacement "1004" search order by rows look at whole
replace range "A:G" of worksheet "Sheet1" what "5" replacement "1005" search order by rows look at whole
replace range "A:G" of worksheet "Sheet1" what "6" replacement "1006" search order by rows look at whole
replace range "A:G" of worksheet "Sheet1" what "7" replacement "1007" search order by rows look at whole
if get value of range "H1" is "" then
set value of active cell to 7000
end if
sort range "H1" of worksheet "Sheet1" key1 (range "H1" of worksheet "Sheet1")
set columnToTest to range "H:H" 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
replace range "A:G" of worksheet "Sheet1" what "1001" replacement "1" search order by rows look at whole
replace range "A:G" of worksheet "Sheet1" what "1002" replacement "2" search order by rows look at whole
replace range "A:G" of worksheet "Sheet1" what "1003" replacement "3" search order by rows look at whole
replace range "A:G" of worksheet "Sheet1" what "1004" replacement "4" search order by rows look at whole
replace range "A:G" of worksheet "Sheet1" what "1005" replacement "5" search order by rows look at whole
replace range "A:G" of worksheet "Sheet1" what "1006" replacement "6" search order by rows look at whole
replace range "A:G" of worksheet "Sheet1" what "1007" replacement "7" search order by rows look at whole
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
set screen updating to true
end if
end tell
end repeat
beep 3
Hm… I assume you edit .xls files and so you have to use excel in first place.
A workaround to speed everything up a bit could be:
copy the contents of the sheet to the clipboard pipe it through some perl/ruby script and paste it back into excel…
if you do not mind the style (font etc.) of the sheet you are editing.
In my case:
I needed to format a large excel list for printing and this took up to 5 minutes with applescript within excel.
Now all I do is copy the list to the clipboard, paste it into an html-site (with javascript) and press 1 button.
The layout is build within a second =)
Hi
ran into some problem with former script, This fixes it
set CR to ASCII character 13
set NL to ASCII character 10
set getPath to "test remove"
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 "Microsoft Excel"
launch
open thisFile
if get value of range "A1" is not "" then
delete range range "H:H" shift shift up
set columnToTest to last column of used range of active sheet
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 bottom)
set lastCol to (first column index of usedRanges) + (count of columns of usedRanges)
set totalCells to get resize (cell 1 of column (lastCol) of active sheet) row size rowsCount
set formula r1c1 of totalCells to ¬
"=SUM(RC[-7]:RC[-1])"
replace range "A:G" of worksheet "Sheet1" what "1" replacement "1001" search order by rows look at whole
replace range "A:G" of worksheet "Sheet1" what "2" replacement "1002" search order by rows look at whole
replace range "A:G" of worksheet "Sheet1" what "3" replacement "1003" search order by rows look at whole
replace range "A:G" of worksheet "Sheet1" what "4" replacement "1004" search order by rows look at whole
replace range "A:G" of worksheet "Sheet1" what "5" replacement "1005" search order by rows look at whole
replace range "A:G" of worksheet "Sheet1" what "6" replacement "1006" search order by rows look at whole
replace range "A:G" of worksheet "Sheet1" what "7" replacement "1007" search order by rows look at whole
if get value of range "H1" is "" then
set value of active cell to 7000
end if
sort range "H1" of worksheet "Sheet1" key1 (range "H1" of worksheet "Sheet1")
set columnToTest to range "H:H" 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
replace range "A:G" of worksheet "Sheet1" what "1001" replacement "1" search order by rows look at whole
replace range "A:G" of worksheet "Sheet1" what "1002" replacement "2" search order by rows look at whole
replace range "A:G" of worksheet "Sheet1" what "1003" replacement "3" search order by rows look at whole
replace range "A:G" of worksheet "Sheet1" what "1004" replacement "4" search order by rows look at whole
replace range "A:G" of worksheet "Sheet1" what "1005" replacement "5" search order by rows look at whole
replace range "A:G" of worksheet "Sheet1" what "1006" replacement "6" search order by rows look at whole
replace range "A:G" of worksheet "Sheet1" what "1007" replacement "7" search order by rows look at whole
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
end tell
end tell
end if
set screen updating to true
else
tell application "Finder"
delete thisFile
tell application "Microsoft Excel"
close active workbook saving no
end tell
end tell
end if
end tell
end repeat
hubionmac wrote perhaps you could offer some example data…
each file is 1.2 mb.
basic 7 columns & 30000 lines, any random numbers 1-28 will do, files I botched will have 0 lines, hence the fix