Home

Automator/AppleScript and Excel 16.25 Script issue in Mac OS

%3CLINGO-SUB%20id%3D%22lingo-sub-670353%22%20slang%3D%22en-US%22%3EAutomator%2FAppleScript%20and%20Excel%2016.25%20Script%20issue%20in%20Mac%20OS%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-670353%22%20slang%3D%22en-US%22%3E%3CP%3EI'm%20having%20issues%20getting%20Automator%2FApplescript%20to%20work%20with%20excel%2016.25.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20customized%20the%20AppleScript%20from%20(%3CA%20href%3D%22https%3A%2F%2Fdiscussions.apple.com%2Fthread%2F5528059%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdiscussions.apple.com%2Fthread%2F5528059%3C%2FA%3E)%20to%20search%20for%20files%20containing%20an%20ISBN13%20string%20(no%20dashes)%20in%20column%20B%20of%20an%20Excel%20worksheet%20with%20a%20wildcard%20search%20at%20the%20beginning%20and%20end%20of%20the%20file%20name.%20I'm%20basically%20searching%20for%20a%20filename%20that%20has%20the%20ISBN13%20in%20it%20in%20part%20of%20the%20filename.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESearch%20Directory%20is%20usually%20on%20a%20server%20mounted%20to%20the%20Mac.%20This%20has%20not%20changed.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDestination%20is%20usually%20the%20user%E2%80%99s%20desktop.%20When%20testing%2C%20it%20doesn%E2%80%99t%20matter%20I%20use%20the%20desktop%20or%20a%20folder%20not%20he%20root%20level%20of%20my%20hard%20drive.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20script%20used%20to%20run%20just%20great%20in%20the%20MacOS%20in%20various%20versions%20all%20the%20way%20up%20to%20and%20including%20MacOS%2010.13%20and%20in%20Excel%20versions%20up%20to%20Office%202016.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENow%20in%20MacOS%2010.14%20and%20Microsoft%2016.25%20it%20no%20longer%20works%20(I%20think%20it%20is%20an%20Excel%20issue%20as%20it%20does%20not%20work%20inExcel%2016.25%20in%20earlier%20versions%20of%20the%20MacOS).%20It%20just%20runs%20without%20any%20errors%2C%20but%20it%20does%20not%20copy%20the%20file.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20can%20run%20the%20commands%20in%20terminal%20(pasting%20int%20he%20search%20and%20destination%20path)%2C%20and%20everything%20works%20just%20great%20(it%20finds%20the%20file%20and%20copies%20the%20file)%2C%20so%20something%20tells%20me%20it%E2%80%99s%20something%20with%20the%20OS%20or%20Excel.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20tried%20running%20it%20both%20as%20a%20Workflow%20and%20as%20a%20standalone%20app.%20When%20I%20run%20it%20as%20an%20app%2C%20I%20have%20granted%20it%20permission%20to%20control%20Microsoft%20Excel%20in%20Security%20and%20Privacy%20%2F%20Automation%20and%20allowed%20the%20saved%20AppleScript%20App%20to%20control%20Excel.%20But%2C%20it%20still%20does%20not%20work.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThoughts%3F%20What%20am%20I%20missing%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3Eon%3C%2FSTRONG%3E%20%3CSTRONG%3Erun%3C%2FSTRONG%3E%20%7Binput%2C%20parameters%7D%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3Eset%3C%2FSTRONG%3E%20theDirectory%20%3CSTRONG%3Eto%3C%2FSTRONG%3E%20quoted%20form%20%3CSTRONG%3Eof%3C%2FSTRONG%3E%20POSIX%20path%20%3CSTRONG%3Eof%3C%2FSTRONG%3E%20(%3CSTRONG%3Echoose%20folder%3C%2FSTRONG%3E%20with%20prompt%20%22Select%20Search%20Directory%22)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3Eset%3C%2FSTRONG%3E%20theDestination%20%3CSTRONG%3Eto%3C%2FSTRONG%3E%20quoted%20form%20%3CSTRONG%3Eof%3C%2FSTRONG%3E%20POSIX%20path%20%3CSTRONG%3Eof%3C%2FSTRONG%3E%20(%3CSTRONG%3Echoose%20folder%3C%2FSTRONG%3E%20with%20prompt%20%22Select%20Directory%20to%20Copy%20Files%22)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3Etell%3C%2FSTRONG%3E%20%3CEM%3Eapplication%3C%2FEM%3E%20%22Microsoft%20Excel%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3Etell%3C%2FSTRONG%3E%20active%20sheet%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3Etell%3C%2FSTRONG%3E%20used%20range%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3Eset%3C%2FSTRONG%3E%20rc%20%3CSTRONG%3Eto%3C%2FSTRONG%3E%20%3CSTRONG%3Ecount%3C%2FSTRONG%3E%20%3CSTRONG%3Eof%3C%2FSTRONG%3E%20%3CEM%3Erows%3C%2FEM%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3Eend%3C%2FSTRONG%3E%20%3CSTRONG%3Etell%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3Eset%3C%2FSTRONG%3E%20theList%20%3CSTRONG%3Eto%3C%2FSTRONG%3E%20%3CSTRONG%3Eget%3C%2FSTRONG%3E%20value%20%3CSTRONG%3Eof%3C%2FSTRONG%3E%20%3CEM%3Erange%3C%2FEM%3E%20(%22B1%3AB%22%20%26amp%3B%20rc)%20%3CSTRONG%3Eas%3C%2FSTRONG%3E%20%3CEM%3Elist%3C%2FEM%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3Erepeat%3C%2FSTRONG%3E%20%3CSTRONG%3Ewith%3C%2FSTRONG%3E%20theItem%20%3CSTRONG%3Ein%3C%2FSTRONG%3E%20theList%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3Eif%3C%2FSTRONG%3E%20contents%20%3CSTRONG%3Eof%3C%2FSTRONG%3E%20theItem%20%3CSTRONG%3Eis%3C%2FSTRONG%3E%20%3CSTRONG%3Enot%3C%2FSTRONG%3E%20%7B%22%22%7D%20%3CSTRONG%3Ethen%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3Edo%20shell%20script%3C%2FSTRONG%3E%20%22find%20%22%20%26amp%3B%20theDirectory%20%26amp%3B%20%22%20-iname%20'*%22%20%26amp%3B%20theItem%20%26amp%3B%20%22*'%20-exec%20cp%20%7B%7D%20%22%20%26amp%3B%20theDestination%20%26amp%3B%20%22%20%5C%5C%3B%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3Eend%3C%2FSTRONG%3E%20%3CSTRONG%3Eif%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3Eend%3C%2FSTRONG%3E%20%3CSTRONG%3Erepeat%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3Eend%3C%2FSTRONG%3E%20%3CSTRONG%3Etell%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3Eend%3C%2FSTRONG%3E%20%3CSTRONG%3Etell%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3Ereturn%3C%2FSTRONG%3E%20input%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3Eend%3C%2FSTRONG%3E%20%3CSTRONG%3Erun%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-670353%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EDeveloper%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20on%20Mac%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
TS-WS
Occasional Visitor

I'm having issues getting Automator/Applescript to work with excel 16.25.

 

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 MacOS 10.13 and in Excel versions up to Office 2016.

 

Now in MacOS 10.14 and Microsoft 16.25 it no longer works (I think it is an Excel issue as it does not work inExcel 16.25 in earlier versions of the MacOS). 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

 

end tell

 

 

 

return input

 

end run

Related Conversations
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
30 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies