So I’m trying to end a script where an excel workbook is saved in a user-selected folder, and the filename of the workbook borrows, in part, the title of that folder. As an example, if a user selects “Documents” as the destination folder, the saved workbook will have the filename:
“Documents Policy Draft”
Or if they selected a folder called “Fantastic Dan” the file name would read “Fantastic Dan Policy Quiz.” Or if they simply selected their Desktop that the file name would read “Desktop Policy Draft” you get the idea.
I have no problem getting a dialog box up, and the workbook saves in the selected folder, but what I can’t figure out is how to get Excel to save the workbook with name of the folder-destination. I’ve tried a few methods, each with no result (that is, an error that stops the script dead). The only thing that takes is the hard-coded second half of the name: “Policy Draft”
Here’s what I’m working with so far:
set thedestination to (choose folder)
tell application "Microsoft Excel"
set workbookName to "Policy Draft"
set thepath to (thedestination & workbookName) as string
save as sheet 1 filename thepath
end tell
There are a few possibilities to get the folder name. One is to use an ObjC function
On getFolderName_(path)
-- given: path as POSIX string "path/to/myfolder"
return (current application's NSURL's fileURLwithPath:(current application's NSString's stringWithString:path))'s lastPathComponent() as text
End
Two required instructions are missing and a typo prevent your proposal to work.
use framework "Foundation" # REQUIRED
use scripting additions # REQUIRED
on getFolderName:aPath
-- given: path as POSIX string "path/to/myfolder"
# was spelled fileURLwithPath, MUST be fileURLWithPath
return (current application's |NSURL|'s fileURLWithPath:(current application's NSString's stringWithString:aPath))'s lastPathComponent() as text
end getFolderName:
set thePath to POSIX path of (choose folder)
set folderName to my getFolderName:thePath
I also replaced the name of the variable passing the pathName from path to thePath or aPath which aren’t reserved words.
You will see the difference when you will compile.
path compile as path in purple like POSIX path
thePath and aPath compile in green like folderName.
EDIT: the spelling getFolderName_(aPath) was AUTOMATICALLY replaced by getFolderName:aPath by the compile process.
I had to enclose NSURL between pipes but it’s due to the fact that an installed OSAX conflicts with your original syntax which is theoretically correct.
Yvan KOENIG running Sierra 10.12.1 in French (VALLAURIS, France) lundi 21 novembre 2016 09:44:17
tell application "Microsoft Excel"
tell active workbook
-- returns the folder where the file is stored, not the path to the workbook
set thePath to path of it
-- if the file is located at the root folder of the FS we cannot continue
if thePath as string is equal to POSIX file "/" as string then
display alert "WARNING:" message "Script cannot continue because file is stored in root folder of file system. "
return
end if
-- the path returned from Excel doesn't contain trailing ":"
tell AppleScript
set oldTIDs to text item delimiters
set text item delimiters to ":"
set parentFolder to text items 1 thru -2 of thePath as string
set theFolder to text item -1 of thePath
set text item delimiters to oldTIDs
end tell
set newPath to parentFolder & ":" & theFolder & ":" & theFolder & space & name of it
save it in newPath
end tell
end tell
Thank you all for your help! Both of these come close, but don’t quite make it, actually.
I’m not familiar with objective-C so I don’t quite know where this goes in my code, nevermind trying to figure out how it works. While it does work, it saves the file as a new folder with the whole pathname as the file name. I was just looking for the name of the final destination folder (i.e.–“Desktop” instead of “Users/np/Desktop”).
This one comes really really close! But it renames workbooks based on where they’re currently saved, and I’m looking to save a new workbook based on a folder the user selects. So if they use my script on a workbook on their desktop and save it to a folder called “Company” the filename won’t be “Desktop Wokbook” it’ll be called “Company Workbook”
Any tips on how I can get either of these to work that way? Or is it a pipe dream?
use framework "Foundation" # REQUIRED
use scripting additions # REQUIRED
set theDestination to POSIX path of (choose folder)
set theDestination to my getFolderName:theDestination
tell application "Microsoft Excel"
set workbookName to "Policy Draft"
set thePath to (theDestination & workbookName) as string
save as sheet 1 filename thePath
end tell
on getFolderName:aPath
-- given: path as POSIX string "path/to/myfolder"
# was spelled fileURLwithPath, MUST be fileURLWithPath
return (current application's |NSURL|'s fileURLWithPath:(current application's NSString's stringWithString:aPath))'s lastPathComponent() as text
end getFolderName:
one based upon DJ Bazzie Wazzie one
set theDestination to (choose folder) as text
tell AppleScript
set oldTIDs to text item delimiters
set text item delimiters to ":"
set theDestination to text item -2 of theDestination
set text item delimiters to oldTIDs
end tell
tell application "Microsoft Excel"
set workbookName to "Policy Draft"
set thePath to (theDestination & workbookName) as string
save as sheet 1 filename thePath
end tell
I tested both until the entry in Excel block because Merdosoft products aren’t allowed on my machines.
Yvan KOENIG running Sierra 10.12.1 in French (VALLAURIS, France) lundi 21 novembre 2016 15:27:14