Can automator gather files listed in an excel spreadsheet?

Question

Level 1

4 points

Can automator gather files listed in an excel spreadsheet?

I customized the AppleScript from (https://discussions.apple.com/thread/5528059) to search for files containing an ISBN13 string (no dashes) in column B of an Excel worksheet with a wildcard search at the beginning and end of the file name. I'm basically searching for a filename that has the ISBN13 in it in part of the filename.

Search Directory is usually on a server mounted to the Mac. This has not changed.

Destination is usually the user’s desktop. When testing, it doesn’t matter I use the desktop or a folder not he root level of my hard drive.

The script used to run just great in the MacOS in various versions all the way up to and including 10.13 and in Excel versions up to Office 2016.

Now in MacOS 10.14 and Microsoft 16.25 it no longer works. It just runs without any errors, but it does not copy the file.

I can run the commands in terminal (pasting int he search and destination path), and everything works just great (it finds the file and copies the file), so something tells me it’s something with the OS or Excel.

I have tried running it both as a Workflow and as a standalone app. When I run it as an app, I have granted it permission to control Microsoft Excel in Security and Privacy / Automation and allowed the saved AppleScript App to control Excel. But, it still does not work.

Thoughts? What am I missing?

on run {input, parameters}

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 ("B1:B" & rc) as list

repeat with theItem in theList

if contents of theItem is not {""} then

do shell script "find " & theDirectory & " -iname '*" & theItem & "*' -exec cp {} " & theDestination & " \\;"

end if

end repeat

end tell

return input

end run

Posted on Jun 4, 2019 1:29 PM

Reply

Answer 1

Jun 5, 2019 11:19 AM in response to TS-WS

See if this works and is faster than find:

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")

try
	-- because copy will not copy an identical file over itself if it is already in the destination folder
	-- case independency using 'c'
	(do shell script "mdfind -0 -onlyin " & theDirectory & " \"kMDItemFSName == '*" & theItem & "*'c\" | xargs -0 -n 1 -P 2 -I{} cp {} " & theDestination)
end try

I would also use a guard statement to verify that theList has content:

if theList is {} then return

Reply

Can automator gather files listed in an excel spreadsheet?

Similar questions