Finding in Excel

Scripting Forums AppleScript | Mac OS X
,
#1

I’ve developed a folder action script that takes the picture file names from a folder and searches for it in an Excel sheet.
The purpose of the script is to find whether or not the file exists on the Excel file. I found that if the file name is found then the script works fine. However if the name is not found then the script quits. Any suggestions for how to make a response for the “Not Found” file? I want to sort the files based on if they are on the Excel sheet. Help? Suggestions?

set Portal to (Find Selection What (trimmed_name as string) after ActiveCell LookIn xlFormulas LookAt xlPart SearchBy xlByColumns direction xlNext)
Goto Reference Portal with Scroll

		display dialog "Found on Portal at" & " " & (get Address Portal) & return & return & "Item name is: " & return & return & this_item
#2

You might enclose the search routine in a try block with an on error portion that continued with a dialog “Not found”


try
	-- do your search
	-- return results
on error
	display dialog "Not Found"
end try
#3

Thanks. I tried it with no luck. The script quits and does not even find files that are there. here’s the code I have:

tell application "Microsoft Excel"
			Activate
			set CutCopyMode to false
			Activate Window "Marketing Store Metadata.xls"
			Select Range "C2"
			Activate Range "R1C2"
			
			try
				set MarkStore to (Find Selection What (trimmed_name as string) after ActiveCell LookIn xlFormulas LookAt xlPart SearchBy xlByColumns direction xlNext)
				Goto Reference MarkStore with Scroll
				
				display dialog "Found in Marketing Store at" & " " & (get Address MarkStore) & return & return & "Item name is: " & return & return & this_item
			on error
				display dialog "Not Found"
			end try
		end tell

Any other ides? I am definately not a pro at this so I could have gotten the syntax wrong.

#4

What version of Excel? Excel X is very difficult to script, 2004 is much improved.

#5

I am running Excel X. Do you think that could be it? I could probably get a hold of 2004 if I need it.

#6

Jacques;

His problem is that his script crashes if the data is not in the second column.

#7

I was not clear. He is checking to see if the data is present in that column. If it is not, that is OK, and his script should continue. Instead, the script crashes. He tried a try block, and it still crashes. Does it crash for you if you search a column not containing the search item?

[Je n’étais pas certain. Il vérifie pour voir si les données sont présentes dans cette colonne. Si elle n’est pas, c’est CORRECTE, et son manuscrit devrait continuer. Au lieu de cela, les accidents de manuscrit. Il a essayé un bloc d’essai, et il se brise toujours. Se brise-t-il pour vous si vous recherchez une colonne ne contenant pas l’article de recherche ? Pardonnez-moi si j’ai maltraité votre langue]

#8

Yes, I want the script to do different things based on whether the file name is present in the Excel file or not-- ie. if it is there then alert me. If it is not, then move the file to anther folder.

I am sorting documents based on whether the name is in the spreadsheet.

#9

Thank you, thank you, THANK YOU!

with a little modification, it is now flagging the files correctly. Here is the final code I’m working with:

on adding folder items to this_folder after receiving these_items
	set the item_count to the number of items in the these_items
	--display dialog "Pos. 1 Item Count is: " & item_count
	
	repeat with i from 1 to number of items in these_items
		set this_item to item i of these_items
		set the item_info to info for this_item
		
		--display dialog "Pos. 1.1 Item name is: " & this_item
		--display dialog the name extension of the item_info
		
		set file_extension to the name extension of item_info
		--display dialog "Pos. 2 Current item file extension is: " & file_extension
		
		tell application "Finder"
			set the file_name to the name of this_item
			set file_extension to the name extension of this_item
			if the file_extension is "" then
				set the trimmed_name to the file_name
			else
				set the trimmed_name to text 1 thru -((length of file_extension) + 2) of the file_name
			end if
		end tell
		
		--display dialog "Pos. 4 Trimmed Name is: " & trimmed_name
		
		tell application "Microsoft Excel" -- Office X
			Activate Window "_Marketing Store Metadata.xls"
			set theCount to count Worksheets
			set MarkStore to "Not Found"
			repeat with i from 1 to 1 --theCount -- find in each Sheet
				Activate Sheet i
				--set tRange to UsedRange of ActiveSheet
				Select Range "C2"
				Activate Range "R1C2"
				
				try
					set MarkStore to (Find Selection What (trimmed_name as string) LookIn xlFormulas LookAt xlPart SearchBy xlByColumns direction xlNext)
					Goto Reference MarkStore with Scroll
					exit repeat
				end try
			end repeat
			
			if MarkStore is "Not Found" then
				display dialog "Not found"
				
			else
				display dialog "Found on Portal at" & " " & (get Address MarkStore) & return & return & "Item name is: " & return & return & trimmed_name
			end if
			
		end tell

	end repeat
end adding folder items to

Now to figure out what to do with it. Thanks for the help!