Hello, I have an applescript to setup, rename and copy template files to a new job folder as per below:
global jobNum
global newJobFolder
set jobNum to text returned of (display dialog “Enter a job number:” default answer “”)
set jobName to text returned of (display dialog “Enter a job name:” default answer “”)
set folderpath to (choose folder with prompt “Select client folder”)
set newJobFolder to my newFold(jobNum, jobName, folderpath)
on newFold(theNumber, theName, thefolder)
set subNameList to {“Designs”, “Documents”, “Received”}
set itemCount to count of subNameList
tell application “Finder”
set newJobFolder to (make new folder at thefolder with properties ¬
{name:theNumber & " " & theName})
repeat with i from 1 to itemCount
make new folder at newJobFolder with properties ¬
{name:jobNum & " " & item i of subNameList}
end repeat
duplicate file “Users:ace:Dropbox:Company:0000-1_E.xlsx” of startup disk to folder (jobNum & " Documents") of newJobFolder
set name of the result to jobNum & " E.xlsx"
end tell
end newFold
What I would like to do is to rename a tab/worksheet within the excel file that I copy to this location so the first tab is named as “jobNum E1” and also a certain cell within that tab, sa A3 has the value “jobNum E1” I am not sure if this is possible
Instead of duplicating the Excel template file open it in Excel change the contents and save it in the new created folder
global jobNum
global newJobFolder
set jobNum to text returned of (display dialog "Enter a job number:" default answer "")
set jobName to text returned of (display dialog "Enter a job name:" default answer "")
set folderpath to (choose folder with prompt "Select client folder")
set newJobFolder to my newFold(jobNum, jobName, folderpath)
on newFold(theNumber, theName, thefolder)
set subNameList to {"Designs", "Documents", "Received"}
set itemCount to count of subNameList
tell application "Finder"
set newJobFolder to (make new folder at thefolder with properties ¬
{name:theNumber & " " & theName})
repeat with i from 1 to itemCount
make new folder at newJobFolder with properties ¬
{name:jobNum & " " & item i of subNameList}
end repeat
end tell
set excelTemplate to (path to home folder as text) & "Dropbox:Company:0000-1_E.xlsx"
set destinationExcelFile to (newJobFolder as text) & jobNum & " Documents:" & jobNum & " E.xlsx"
tell application "Microsoft Excel"
open excelTemplate
set name of worksheet 1 to jobNum & " E1"
set value of cell 1 of row 3 to jobNum & " E1" -- cell A3
save active workbook in destinationExcelFile
close active workbook
end tell
end newFold
thank you. I just tried it but receive this error:
Microsoft Excel got an error: The object you are trying to access does not exist
and an excel Box popping up with:
“/users/ace/library/containers/com.microsoft.excel/data/macintosh HD:USERS:ACE:DROPBOX:COMPANY:000-1_E.XLSX COULD NOT BE FOUND” CHECK SPELLING OF FILE NAME
I am on Excel 15.21.1 ((160411) build. I am not familiar with the dictionaries etc but is there a way in excel to confirm they are loaded etc and which it is using?
I was interested in this topic and 2 errors indicated by the OP above. I tried to improve the stability of the code and I got it.
I was surprised by something else: when writing a file to a new subfolder, the system asks to grant for Microsoft Excel access to subfolder, although I have been granted full disk access for Microsoft Excel. I understand that this is not my fault, but Catalina’s tricks, however still I would like to solve this riddle.
The stable script is here:
property subNameList : {"Designs", "Documents", "Received"}
property desktopPath : (path to desktop folder) as text
property excelTemplate : ((path to home folder) as text) & "Dropbox:Company:0000-1_E.xlsx"
set jobNum to text returned of (display dialog "Enter a job number:" default answer "")
set jobName to text returned of (display dialog "Enter a job name:" default answer "")
set newJobFolder to my newFolders(jobNum, jobName)
set destinationExcelFile to (newJobFolder as text) & jobNum & " Documents:" & jobNum & " E.xlsx"
-- improves stability of code when opening Microsoft Excel
tell application "Finder" to open excelTemplate using (path to application "Microsoft Excel")
tell application "Microsoft Excel"
activate
-- improves stability of code when opening Microsoft Excel
repeat until document 1 exists
delay 0.02
end repeat
tell sheet 1 to set name to jobNum & " E1"
set value of cell 1 of row 3 to jobNum & " E1" -- cell A3
save active workbook in destinationExcelFile
close active workbook saving no
quit
end tell
on newFolders(jobNum, jobName)
tell application "Finder"
if not (folder (desktopPath & jobNum & " " & jobName) exists) then
set newJobFolder to (make new folder at desktopPath with properties ¬
{name:jobNum & " " & jobName}) as text
else
set newJobFolder to (desktopPath & jobNum & " " & jobName & ":")
end if
repeat with aName in subNameList
if not (folder (newJobFolder & jobNum & " " & aName) exists) then
make new folder at newJobFolder with properties ¬
{name:jobNum & " " & aName}
end if
end repeat
return newJobFolder
end tell
end newFolders
property subNameList : {"Designs", "Documents", "Received"}
property desktopPath : (path to desktop folder) as text
property excelTemplate : ((path to home folder) as text) & "Dropbox:Company:0000-1_E.xlsx"
set jobNum to text returned of (display dialog "Enter a job number:" default answer "")
set jobName to text returned of (display dialog "Enter a job name:" default answer "")
set newJobFolder to my newFolders(jobNum, jobName)
set destinationExcelFile to (newJobFolder as text) & jobNum & " Documents:" & jobNum & " E.xlsx"
-- force full disk access...
set sd to path to startup disk
tell application id "com.microsoft.Excel" to try
close sd -- will error
end try
-- improves stability of code when opening Microsoft Excel
tell application "Finder" to open excelTemplate using (path to application id "com.microsoft.Excel")
tell application id "com.microsoft.Excel"
activate
-- improves stability of code when opening Microsoft Excel
repeat until document 1 exists
delay 0.02
end repeat
tell sheet 1 to set name to jobNum & " E1"
set value of cell 1 of row 3 to jobNum & " E1" -- cell A3
save active workbook in destinationExcelFile
close active workbook saving no
quit
end tell
on newFolders(jobNum, jobName)
tell application "Finder"
if not (folder (desktopPath & jobNum & " " & jobName) exists) then
set newJobFolder to (make new folder at desktopPath with properties ¬
{name:jobNum & " " & jobName}) as text
else
set newJobFolder to (desktopPath & jobNum & " " & jobName & ":")
end if
repeat with aName in subNameList
if not (folder (newJobFolder & jobNum & " " & aName) exists) then
make new folder at newJobFolder with properties ¬
{name:jobNum & " " & aName}
end if
end repeat
return newJobFolder
end tell
end newFolders