OK, been on this great forum quite a few times and I know theres plenty of Master Scripters here, so its my turn to ask for help. I˜m confortable with scripting but I˜m facing the challenge to try to build a script that will gather entries from a multisheet Excel document (never scripted Excel before), sort them and save them into a Textedit file. Here˜s what I want to do: my document has 12 sheets (named sheet1, sheet2 and so on), each of them is built the same way so from one sheet to another, each column represents the same kind of data. The first column as publication names (such as “Acme Magazine”), column 2 has a numeric width value (such as 8.5) and column 3 has a height value (such as 11). What I want to do is have the script ask me for a width value (let˜s pretend I input 8.5), then a height value (let˜s pretend I input 11) then have it to go through each sheet, get every line where it finds 8.5 in column 2 AND 11 in column 3 and write something like “sheet1_Acme Magazine” in a text file named “width_8.5_height_11.txt”. So I would end-up with a text file with all names of publications with these corresponding value. I˜m sure I would find a way (exploring Excel dictionnary with trial and error) being given a bit of time but that˜s exactly what I˜m missing. So any help is welcome guys! Thanks in advance.
Hi stefcyr,
I wrote a sample script for you, which might get you started.
Please note that it was written for Microsoft Excel 2004. Moreover the script will create a folder on your desktop named “Search results” and save the corresponding text files therein. The contents in the text files is always appended, so if you need a fresh run, then you need to delete the files in the folder.
Search values for width and height are hard coded (as you can see below) into the script, as I had no time to write an additional function asking you for these values. So this is left as an excercise. Last but not least the script expects the workbook to be already opened in Excel.
property searchwidth : "8.5"
property searchheight : "11.0"
property worksheetnames : {"Sheet1", "Sheet2", "Sheet3"}
on run
tell application "Microsoft Excel"
tell workbook 1
repeat with worksheetname in worksheetnames
tell worksheet worksheetname
set counter to 0
repeat
set counter to counter + 1
set foundpubname to string value of cell ("A" & counter)
if foundpubname is "" then
exit repeat
end if
set foundwidth to string value of cell ("B" & counter)
set foundheight to string value of cell ("C" & counter)
if foundwidth = searchwidth and foundheight = searchheight then
my writetofile(worksheetname, foundpubname, foundwidth, foundheight)
end if
end repeat
end tell
end repeat
end tell
end tell
end run
on writetofile(worksheetname, foundpubname, foundwidth, foundheight)
set folderpath to ((path to desktop) as Unicode text) & "Search results:"
try
set folderalias to folderpath as alias
on error
set command to "mkdir -p " & quoted form of POSIX path of folderpath
do shell script command
end try
set logfilename to "width_" & foundwidth & "_height_" & foundheight & ".txt"
set logfilepath to folderpath & logfilename
set entry to worksheetname & "_" & foundpubname & return
try
set openfile to open for access logfilepath with write permission
write entry to openfile starting at eof
close access openfile
on error
try
close access openfile
end try
end try
end writetofile
Of course there might be even more sophisticated solutions to your problem 
Thanks I’ll give it a shot and let you know about it. I really appreciate.
Just tried it, does exactly what it should. You rule!
Thanks a million.