I could really use some help with this!
I need to be able to format the same column in many opened Numbers files, and then export these files to Excel format and save them in Numbers format back into the same folder each one currently resides.
I have the following script that seems to do the job of formatting, exporting and saving, but I can only seem to export the Excel file onto the desktop and not the original folder.
I need to end up with the original folder containing both the Numbers and Excel files.
Any help with this would be greatly appreciated and please feel free to improve on the existing Applescript syntax as well to make it more efficient.
tell application "Numbers"
activate
set the defaultDestinationFolder to (path to desktop folder)
repeat with i from 1 to count of documents
tell table 1 of sheet 1 of document i
set alignment of column 10 to right
end tell
set documentName to the name of document i
tell application "Finder"
set newExportFileName to documentName & ".xlsx"
end tell
set the targetFileHFSPath to (defaultDestinationFolder as string) & newExportFileName
with timeout of 1200 seconds
export document i to file targetFileHFSPath as Microsoft Excel
end timeout
save document i
close document i
end repeat
end tell
Well, you are explicitly instructing it to save to the desktop.
Yes, because that’s all I know how to do! I need to delete that and have the script save everything to the original folder.
Are all documents from the same folder or could they be in different folders?
They could be from different folders, so as it cycles through each open document, it uses the parent folder of that one to export to.
I’m also not sure if the existing script can be tidied up a bit as well? I’m pretty new to this and am trying my best to figure it all out 
Okay. Then inside the repeat loop, you can get the path of each document:
set docPath to file of document i as text
Yeah, there are a couple of things you can do, e.g. there isn’t a need to put set newExportFileName inside a finder tell block. Give me a few minutes and I’ll try and post something.
And if I could also ask you for a second variant that does the same thing but instead of having all the numbers files opened, it would prompt you to select the folder and process all the numbers files inside without having to open them all in advance, that would be great.
So this should work with open files. I moved the close command outside the loop so it doesn’t complicate the loop. I changed a couple of numbers to make it easier to test (e.g. column to align, timeout).
tell application "Numbers"
-- activate
repeat with i from 1 to count of documents
set alignment of column 2 of table 1 of sheet 1 of document i to right
set docName to the name of document i
set docPath to file of document i as text
tell application "Finder" to set docDir to container of alias docPath as text
set newExportFileName to docName & ".xlsx"
set the targetFileHFSPath to docDir & newExportFileName
with timeout of 12 seconds
export document i to file targetFileHFSPath as Microsoft Excel with exclude summary worksheet
end timeout
save document i without asking
end repeat
close documents
end tell
And can this be modified so that each file opens from a selected folder, then processes and closes, one at a time, so that they don’t all need to be first opened at the same time in Numbers?
Two questions: what is the purpose of setting the delimiters, and why is there a timeout setting? And should we reset the timeout back to something much higher, like 1200?
Thanks again!
Delimiters are a tool to allow you to separate and join texts. You can read up on them here.
Basically, you use text items of to split a text around the delimiters. I set them to a colon (because that’s what separates directories and files in a path) and it returned the individual elements of the path to the file.
I then removed the last item from the list (i.e. the file) and then rejoined the items into a path that no longer included the file (i.e. the file’s containing folder) using as text. At the end, I set the delimiters back to their default but this is optional — of course if you do any other such processing within the script, they will affect them as set.
set AppleScript's text item delimiters to ":"
set xy to "MacHD:Users:home:Desktop:numb.numbers"
set yz to text items of xy
--> {"MacHD", "Users", "home", "Desktop", "numb.numbers"}
set ab to items 1 thru -2 of yz
--> {"MacHD", "Users", "home", "Desktop"}
set cd to ab as text
--> "MacHD:Users:home:Desktop"
You can set the timeout to any number you like (or remove it) but It does perplex me as to why you would want to potentially wait 20 minutes for each file to export. For myself, if something is wrong and the script won’t complete, I would rather be notified sooner rather than later. A valid reason to increase it would be that your spreadsheet tables are massive and the script won’t finish exporting each one before the default timeout (two minutes).
I use timeouts with excel because a default spreadsheet has a million rows and hundreds of columns and it is relatively easy to have a script try to process every row or cell even when empty. That doesn’t really apply to Numbers because you only have as many rows and columns as your table shows but YMMV.
This should cycle through each Numbers spreadsheet file and open, edit, export, save, and close them.
set sDir to choose folder
--> alias "MacHD:Users:username:Documents:numero:"
tell application "Finder" to set sList to (files of sDir whose kind is "Numbers Spreadsheet") as alias list
tell application "Numbers"
-- open sList -- if you want to open all spreadsheets at once
repeat with ff in sList
open ff
tell document 1
set alignment of column 2 of table 1 of sheet 1 to right
set docName to its name
set newExportFileName to docName & ".xlsx"
set the targetFileHFSPath to (sDir as text) & newExportFileName
with timeout of 12 seconds
export to file targetFileHFSPath as Microsoft Excel with exclude summary worksheet
end timeout
save without asking
close
end tell
end repeat
end tell
It is possible that opening each file will take a bit of time and thus the script may get confused. If this occurs, try adding delay 1 after the open ff line and a one second delay will be added.
But I didn’t see where you actually parsed the path in the script, unless I missed it?
Sorry, as I went through the script, I ended up using a different method — having the Finder get the container. I’ll delete the delimiter lines.
The first script works great, This one produces an error: “Numbers got an error: Invalid key form.” on this line: " export to file targetFileHFSPath as Microsoft Excel with exclude summary worksheet"
Put this line immediately above the export line in each script. You only need to have one spreadsheet open (or in the folder).
display dialog targetFileHFSPath
The resulting dialogue (in each script) should have the full path to the spreadsheet, e.g.
MacHD:user:Documents:numero:ran.numbers.xlsx
This is what is being fed to the export command.
Yes, it does have the full path.
But it then says : Numbers got an error: Invalid key form.