Find a row in Excel based on one cell value

Scripting Forums AppleScript | Mac OS X
,
#1

Hi, hopefully this is an easy question. I have an Excel file with, among other data, a list of filenames in Column D (4th column). These are the files I will be dropping on my applet. For every file I drop, I need to get the data associated with it: each row in the Excel worksheet corresponds to one file, so I’m looking for a list of the values from the row where the filename appears in Column D.

Here’s what I want to do:
-Drop a file on the Script
-Script finds the row in the Excel worksheet where the filename of the dropped file is the value in column D
-Script sets the found row as a variable
-Script sets variables to the value of each cell in the found row (9 cells in each row, so 9 variables)
Then I can go on to do what I need to do with those variables.

Any comments appreciated!
Thanks, Alison

#2

Hi Alison,

Unfortunately I have no time to provide you with a ready-to-use solution, but I guess the following code will get you started. Please note, that this code was written for Microsoft Excel X for Mac, as I don’t have the latest version installed on my private PowerBook.


set filenames to {"myspouse.jpg", "happymarriage.pdf", "dirtydivorces.pdf", "howtobrewbeer.txt"}

repeat with filename in filenames
	set excelvalues to my getexcelvalues(filename)
	if excelvalues is not missing value then
		-- do something crazy with the data!
	end if
end repeat

on getexcelvalues(filename)
	-- minimum and maximum cell range to be searched
	set begincell to 1
	set endcell to 100
	set foundfilename to false
	set matchrow to missing value
	
	tell application "Microsoft Excel"
		-- we are talking to the frontmost workbook and worksheet!
		tell Workbook 1
			tell Worksheet 1
				repeat with i from begincell to endcell
					-- climbing down the column D and getting the value of each cell
					set cellval to (Value of Cell i of Column 4)
					-- do we have a match?
					if cellval is equal to (filename as Unicode text) then
						set foundfilename to true
						set matchrow to i
						exit repeat
					end if
				end repeat
				if foundfilename is true then
					-- now that we found the file name, we need to get the corresponding data from the 9 cells
					set excelvalues to {}
					repeat with i from 1 to 9
						set excelvalues to excelvalues & (Value of Cell i of Row matchrow)
					end repeat
					return excelvalues
				else
					-- returning missing value in case we did not locate the file name
					return missing value
				end if
			end tell
		end tell
	end tell
end getexcelvalues
#3

Thank you, that works perfectly! I have one question though. Is there a way to get the value of the cell as it is displayed, rather than (what might be) the actual value of the cell? That is, I have a zip code [21219] in one cell that the script returns as [“2.219E+4”] and a date [06/09/08] that the script returns as [date “Monday, June 9, 2008 12:00:00 AM”]. If there’s not an easier way, I guess I’ll write subroutines in to convert these to the format I want.

Thanks again for the script you provided!
Alison

#4

I do not have so much experience with Microsoft Office scripting (I am a RagTime nut), but maybe coercing the values into Unicode text might already be sufficient:


repeat with i from 1 to 9
    set excelvalues to excelvalues & ((Value of Cell i of Row matchrow) as Unicode text)
end repeat
#5

Thanks, but it says it can’t make the values into Unicode text. I think I can come up with something to reformat them. Thanks for your help!

#6

Hi,

a different approach (tested with Excel 2004)


property searchFileName : "myFileName.ext"

tell application "Microsoft Excel" to tell active sheet to set checkValues to value of column 4 of used range

set flag to false
repeat with oneValue from 1 to count checkValues
	if item 1 of item oneValue of checkValues is searchFileName then
		set flag to true
		exit repeat
	end if
end repeat
if flag then
	tell application "Microsoft Excel" to set theVariables to item 1 of (get value of range ("A" & oneValue & ":" & "I" & oneValue))
	repeat with i in theVariables
		set cl to class of i
		if cl is real then
			set contents of i to (get i as integer as text)
		else if cl is date then
			set contents of i to short date string of (get i)
		end if
	end repeat
else
	display dialog "no match found"
end if

set {v1, v2, v3, v4, v5, v6, v7, v8, v9} to theVariables
#7

Thanks Stefan, I always learn something from you! Martin’s solution is working but I’ll hold onto this.

#8

That’s a very polite description :wink: I’d rather suggest to call it a tremendously better solution :smiley:

Best regards to lovely Switzerland!

#9

Stefan, how would you alter your solution for the Office X version of Excel? I got hung up on the references to “active sheet” and “user range” when I tried to compile in Script Editor.
Thanks,
Alison

#10

“used range” not “user range” :wink:

AFAIR in Office v.X activesheet and usedrange are a single word.
Take a look in the dictionary :slight_smile: