help in renaming an excel worksheet via applescript.

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

Hi,

of course it’s 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

try to insert this instruction:

 set destinationExcelFile to (newJobFolder as text) & jobNum & " Documents:" & jobNum & " E.xlsx"
   close access (open for access file destinationExcelFile) # ADDED
   tell application "Microsoft Excel"

Yvan KOENIG running Sierra 10.12.3 in French (VALLAURIS, France) dimanche 26 février 2017 19:56:49

i added that in and still says the same error

It looks like a sandbox issue where path to home folder points into the container

Try this workaround:

replace

set excelTemplate to (path to home folder as text) & "Dropbox:Company:0000-1_E.xlsx"

with

tell application "Finder"
	set excelTemplate to (file "Users:ace:Dropbox:Company:0000-1_E.xlsx" of startup disk) as text
end tell

thanks. i tried and then same error comes up in the applescript the line

	set name of worksheet 1 to jobNum & " E1"

is highlighted and the error below

error “Microsoft Excel got an error: The object you are trying to access does not exist” number -10006 from name of worksheet 1

fyi the first sheet i have in my template excel file is called “Sheet 1”

I tested the code successfully with Excel 2011. Maybe the dictionary has changed.

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

Put this somewhere early on:

set sd to path to startup disk
tell application id "com.microsoft.Excel" -- Microsoft Excel
	try
		close sd -- will error
	end try
end tell

Haha, your trick worked, Shane. Thanks.


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