Hey, all. I have been charged by the powers that be to create a script that is giving me a hard time.
First the prerequisite background. We have a Filemaker server and a fileserver. On the fileserver live many, many Excel worksheets that contain all kinds of important data, sorted into different folders. Now for the script.
We’d like a script that can read a certain field in a FM database, then determine of the corresponding Excel worksheet (the name of the worksheet will be the same as the text in the Filemaker field first examined) lives in Folder A, Folder B or Folder C on the fileserver, announce its findings and then open the worksheet in excel.
I can display dialog, open the selected file, I just don’t know how to get AS to perform a query. Any ideas? I’m scouring O’Rielly’s Applescript in a Nutshell for clues. Thanks in advance.
It sounds like you just need something that can get the entire contents of your top-level folders, and pick out the right name from that. Perhaps a do shell script “find” put through a grep for the name you’re searching for?
repeat with aFolder in subfolderList
copy basepath & afolder & “:” & theFile to fullpath
tell app “Finder”
if exists file fullpath then
open theFile
return
end if
end repeat
– if we got here
display dialog “The file " & theFile & " doesn’t exist”
Sounds good, but it’s automatically jumping to the error dialog, even if the file does exist. Perhaps a little more background.
The problem is a variable that I haven’t yet defined. The worksheet that the scirpt is looking for exists in either a folder named “OPEN” “CLOSED” or “NOT INTRODUCED.” The path to each of these is, starting with the name of the disk first: "Cape Cod:Midas:Children:CODE and then within is “OPEN” “CLOSED” and “NOT INTRODUCED.”
“CODE” mentioned above is not a folder, but the varialbe that’s tricking me. Within the Children folder defined above are fifty some odd subfolders each named for each student in our school. They names they bear are the first three letters of that student’s last name, what we call the “Code.” So, for example, I may want to search for my excel worksheet in:
“Cape Cod:Midas:Children:smi: OPEN (0r) CLOSED (or) NOT INTRODUCED” where “smi” stands for “smith.” Like I said, there are over 50 students.
Sorry, I should have included this bit of info. I promise I’m reading, reasearching and trying all this time. This is both hard work and educational for me. Thaks for all your support/tips/advice.
Here’s how I’ve edited your code:
property basePath: "Cape Cod:Midas:Children"
property subfolderList: {"OPEN","NOT INTRODUCED","CLOSED"}
copy cell "midas worksheet" of current record to theFile
repeat with aFolder in subfolderList
copy basepath & afolder & ":" & theFile to fullpath
tell app "Finder"
if exists file fullpath then
open theFile
return
end if
end tell
end repeat
-- if the identified file does not exist
display dialog "The worksheet " & theFile & " doesn't exist"
property basePath: "Cape Cod:Midas:Children"
property subfolderList: {"OPEN","NOT INTRODUCED","CLOSED"}
copy cell "midas worksheet" of current record to theFile
repeat with aFolder in subfolderList
copy basepath & afolder & ":" & theFile to fullpath
tell app "Finder"
if exists file fullpath then
open theFile
return
end if
end tell
end repeat
-- if the identified file does not exist
display dialog "The worksheet " & theFile & " doesn't exist"
[/quote]
This may or may not be the problem but I notice that your “open” command does not include an object reference or path, it only refers to a name.
it should read:
tell app "Finder"
if exists file fullpath then
open file fullpath --theFile is only the name of the file with no path or object reference
return
end if
end tell