Hi,
I have written a script but I am having trouble with the do shell script part.
set theDirectory to quoted form of POSIX path of (choose folder with prompt "Select Search Directory")
set theDestination to quoted form of POSIX path of (choose folder with prompt "Select Directory to Copy Files")
tell application "Microsoft Excel"
tell active sheet
tell used range
set rc to count of rows
end tell
set theList to get value of range ("A1:A" & rc) as list
repeat with theItem in theList
if contents of theItem is not {""} then
do shell script "find " & theDirectory & " -iname " & theItem & " -type d -ipath '*Archive' -prune -o -ipath '*WIP' -prune -o -ipath '*APPROVED/*/*OLD' -prune -o -ipath '*APPROVED/*/*supplied' -prune -o -ipath '*APPROVED/*/*Amends' -prune -o -ipath '*APPROVED/*/*Brief' -prune -o -ipath '*Approved/*' -print -exec cp {} " & theDestination & " \\;"
end if
end repeat
end tell
end tell
Our folder structure is “Level 1” — “Level 2”---- “Level 3 Jobcode”
Inside “Level 3 jobcode” are three directories at “Level 4”(“APPROVED” “TO BE ARCHIVED” and “WIP”)
We are trying to target all files inside the “Level 4”(“APPROVED”) folder only.
Inside the “Level 4”(“APPROVED”) folder is a folder “Level 5”(“Datexxx”) which the naming can change and inside that are a further five folders called “Level 6”(“AMENDS” “BRIEF” “LINKS” “OLD” and “SUPPLIED”)
The code is actually just copying the files in the folder “Level 5”(“Datexxx”) excluding all files in “Level 6”(“AMENDS” “BRIEF” “LINKS” “OLD” and “SUPPLIED”).
This script seems to be ignoring the Excel workbook.
Any help would be much appreciated
Welcome to Macscripter.
I’m fixing the shell script by mostly just avoiding using it. Shell commands are more concise, but I generally find the syntax is a lot less confusing to just use Applescript for this kind of file manipulation.
First off, this is a confusing folder structure, so to be sure we’re on the same page here, here’s an image of what I created to test the script:
https://www.dropbox.com/s/sj63cfdtgwqv49j/file%20heirarchy.png?dl=0
This is working for me:
--get directories
set theDirectory to (choose folder with prompt "Select Search Directory")
set theDestination to (choose folder with prompt "Select Directory to Copy Files")
--get jobcodes from Excel
tell application "Microsoft Excel"
tell active sheet
tell used range
set rc to count of rows
end tell
set theList to the value of range ("A1:A" & rc)
end tell
end tell
--find job folders
set foundFoldersList to {}
repeat with aJobcode in theList
if aJobcode is not "" then
set foundFoldersList to foundFoldersList & (do shell script "find " & quoted form of POSIX path of theDirectory & " -iname \"*" & aJobcode & "\"")
end if
end repeat
-- find files to move
set filesToMove to {}
repeat with posixJobFolder in foundFoldersList
set aliasJobFolder to (POSIX file posixJobFolder as alias)
tell application "System Events"
set filesToMove to filesToMove & (every file of the first folder of folder "APPROVED" of aliasJobFolder whose visible is true)
end tell
end repeat
-- move them
tell application "System Events"
move filesToMove to theDestination
end tell
While it’s working, I worry it may contain assumptions that might not work in your environment, such as:
- The “Level 5” folder is always the sole subfolder of the “Approved” folder.
- You want to move every non-invisible file (as opposed to folder) inside the Level 5 Folder.
If these aren’t correct, this is easy to adjust. I’m just not 100% clear on the allowed logic.
I also suspect it could use a lot more error handling, but I wasn’t sure what that would be. For example, is it always supposed to find exactly one folder per jobcode? If so, I’d error if it finds more or less. If the assumption that:
“The “Level 5” folder is always the sole subfolder of the “Approved” folder.”
was correct, then probably best to check that the count of folders in “Approved” = 1.
That kind of thing.
Wow this is awesome…
many thanks.
If nothing is in Approved it comes up with an error. Is there anyway to log the error and move onto the next code in the list.
--get directories
set theDirectory to (choose folder with prompt "Select Search Directory")
set theDestination to (choose folder with prompt "Select Directory to Copy Files")
--get jobcodes from Excel
tell application "Microsoft Excel"
tell active sheet
tell used range
set rc to count of rows
end tell
set theList to the value of range ("A1:A" & rc)
end tell
end tell
--find job folders
set foundFoldersList to {}
repeat with aJobcode in theList
if aJobcode is not "" then
set foundFoldersList to foundFoldersList & (do shell script "find " & quoted form of POSIX path of theDirectory & " -iname \"*" & aJobcode & "\"")
end if
end repeat
-- find files to move
set filesToMove to {}
set emptyApprovedFolders to {}
repeat with posixJobFolder in foundFoldersList
set aliasJobFolder to (POSIX file posixJobFolder as alias)
tell application "System Events"
try
set levelFiveFolder to the first folder of folder "APPROVED" of aliasJobFolder
set filesToMove to filesToMove & (every file of levelFiveFolder whose visible is true)
on error
set emptyApprovedFolders to emptyApprovedFolders & posixJobFolder
end try
end tell
end repeat
-- move them
if filesToMove ≠ {} then
tell application "System Events"
move filesToMove to theDestination
end tell
end if
if emptyApprovedFolders ≠ {} then display dialog "The following Jobcode folders did not contain any subfolders in \"APPROVED\":" & return & return & list_to_text(emptyApprovedFolders)
on list_to_text(aList)
set textOutput to ""
set itemCount to count of aList
repeat with i from 1 to itemCount
set currentItem to item i of aList
if i ≠ itemCount then
set textOutput to textOutput & currentItem & return
else
set textOutput to textOutput & currentItem
end if
end repeat
end list_to_text