Scripting Excel advice needed

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
						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:"
		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
		set openfile to open for access logfilepath with write permission
		write entry to openfile starting at eof
		close access openfile
	on error
			close access openfile
		end try
	end try
end writetofile

Of course there might be even more sophisticated solutions to your problem :wink:

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.