Hi
I was wondering if it is possible to run a script simultaneously on every file of a folder?
The script I am using takes less than a minute on one file, but over two hours on all files.
on subT()
tell application "Microsoft Excel"
launch
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[-6]:RC[-1])"
end tell
end subT
set getPath to "A3_BC49Workbook5"
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
my subT()
get value of range "f1" of worksheet "Sheet1"
set getValue1 to value of range "f1" of worksheet "Sheet1"
replace range "A:F" of worksheet "Sheet1" what getValue1 replacement "1000" search order by rows look at whole
sort range "g1" of worksheet "Sheet1" key1 (range "g1" of worksheet "Sheet1")
set columnToTest to range "g:g" of active sheet -- adjust
set maxvalueCutOff to 1999 -- 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:F" of worksheet "Sheet1" what "1000" replacement getValue1 search order by rows look at whole
delete range range "g:g" shift shift up
if get value of range "A1" is not "" then
save active workbook
close active workbook
else
tell application "Finder"
delete thisFile
end tell
close active workbook saving no
end if
set screen updating to true
end tell
end repeat
beep 2
Any comments would be appreciated
thanks
bills