Opening XLSX file in Numbers

Hi

The following code:

tell application “Finder”
set TodayTable to the last file in folder “Enecsys” in desktop as alias
end tell

tell application “Numbers”
set TT to open TodayTable
– rest of code to manipulate the data in the file
end tell

gives: remove id “413E07E6-C63A-482C-B6A0-3C510A4C85BD” of application “Numbers”
and TT is undefined.

The folder exists and is populated with XLSX files that are updated daily.

This was working at first but then decided to stop.

Please Help

Thank you

(OSX 10.11.5 Numbers 3.6.2)

I’m unable to reproduce the described behavior.

Running :

tell application "Finder"
	set TodayTable to the last file in folder "Enecsys" in desktop as alias
end tell

tell application "Numbers"
	set TT to open TodayTable
	-- rest of code to manipulate the data in the file
end tell

I got the events log :
[format]tell application “Finder”
get last file of folder “Enecsys” of desktop
→ alias “SSD 500:Users:user account:Desktop:Enecsys:noms des polices.xlsx”
end tell
tell application “Numbers”
open alias “SSD 500:Users:user account:Desktop:Enecsys:noms des polices.xlsx”
→ document id “F1D22C7A-EF73-4C55-92AA-CA7107D452C9”
end tell
Résultat :
document id “F1D22C7A-EF73-4C55-92AA-CA7107D452C9” of application “Numbers”[/format]

Yvan KOENIG running El Capitan 10.11.5 in French (VALLAURIS, France) mardi 21 juin 2016 12:23:52

Hi Pierre.

To the Finder, the “last” file in a folder is usually the one whose name has the highest lexical value, not necessarily the last file to be added. I don’t know if this is relevant to your problem.

By the way, could you please enclose AppleScript code in our [applescript] and [/applescript] tags when posting it here? There’s a button for them just above the text window on the posting page. Cheers.

It seems that I understand it.

I ran the script while Numbers wasn’t open.
Nothing appeared but in the Dock the dot flagging the fact that Numbers was now running was displayed.
I clicked it.
The document’s window appeared but the app was in an odd state.
Most of its menu items where grayed and clicking in the doc’s window issued a bip.
I decided to force quit then re-ran the script after opening the app by hand.
This time I got the document,the app in a correct state but the events log was always issuing :
[format]remove id “89D8574D-1A0F-41F2-BCCE-30EDB65F1F35” of application “Numbers” [/format]

My understanding was that something was wrong in some Script Editor’s datas.
I forced quitted it.
Then re-ran it and ran a slightly modified script :

tell application "Finder"
	set TodayTable to the last file in folder "Enecsys" in desktop as alias
end tell

tell application "Numbers"
	activate # ADDED
	set TT to open TodayTable
	-- rest of code to manipulate the data in the file
end tell

After that everything behave right.

Yvan KOENIG running El Capitan 10.11.5 in French (VALLAURIS, France) mardi 21 juin 2016 15:46:38

Hi

Thanks for responding so promptly.

To Yvan:

The ‘activate’ command solved the problem. This was actually there earlier on but somehow got deleted. That explains why it was working before. Thanks.

To Nigel:

The folder contains files that are named “YYYYMMDD.xlsx”: one for each day. So I guess that ‘last’ works well in this case.

Is there a better way to find the last added file?

Thanks

PS Sorry for not posting applescript appropriately.

Here is a way to fit your needs. It required the free BridgePlus.scptd delivered by Shane STANLEY.
https://www.macosxautomation.com/applescript/apps/BridgePlus.html

use scripting additions
use framework "Foundation"
use script "BridgePlus"
load framework

set urlFileOrPath to (path to desktop as text) & "Enecsys"
set resourceKeys to {current application's NSURLAddedToDirectoryDateKey}
set theResult to current application's SMSForder's resourceValuesForKeys:resourceKeys forItemsIn:urlFileOrPath recursive:false

set sortDesc to current application's NSSortDescriptor's sortDescriptorWithKey:(current application's NSURLAddedToDirectoryDateKey) ascending:true
set sortedValues to theResult's sortedArrayUsingDescriptors:{sortDesc}
set thePaths to sortedValues's valueForKey:(current application's NSURLPathKey)

set TodayTable to POSIX file (last item of (thePaths as list))
tell application "Numbers"
	activate # ADDED
	set TT to open TodayTable
	-- rest of code to manipulate the data in the file
end tell

Yvan KOENIG running El Capitan 10.11.5 in French (VALLAURIS, France) mardi 21 juin 2016 16:47:20

You can get the newest file by sorting the files by modification date

tell application "Finder"
	set TodayTable to last item of (sort files of folder "Enecsys" by modification date) as alias
end tell

in desktop is not needed since the desktop of the current user is the “root” folder of the Finder.

Thank you guys for all your valid inputs and ideas.

Thanks Stefan

As I hate the Finder I never think to its sort by. function :rolleyes:

Alas here the script fails because, as hidden files are always visible, the sort instruction returns the .DS_Store file.

So I wrote an alternate code using only old fashioned AppleScript :

set theFolder to (path to desktop as text) & "Enecsys"
set greaterDate to "1/1/1"
set greaterDate to date greaterDate
set lastFile to ""
tell application "System Events"
	set theFiles to files of folder theFolder whose visible is true
	repeat with aFile in theFiles
		set maybe to modification date of aFile
		if maybe > greaterDate then
			set geaterdate to maybe
			set lastFile to aFile
		end if
	end repeat
	
	set todayTable to path of lastFile 
end tell

tell application "Numbers"
	activate # ADDED
	set TT to open file todayTable
	-- rest of code to manipulate the data in the file
end tell

Just for the fun, I wrote an alternate one using ASObjC which gives the ability to really use the added date time.
The one using BridgePlus does that too.

use AppleScript version "2.4" -- Yosemite (10.10) or later
use framework "Foundation"
use scripting additions

set thePath to POSIX path of (((path to desktop as text) & "Enecsys") as alias)

set laterPath to my GetLastPath:thePath

tell application "Numbers"
	activate # ADDED
	set TT to open file laterPath
	-- rest of code to manipulate the data in the file
end tell


on GetLastPath:folderPosixPath
	set greaterDate to "1/1/1"
	set greaterDate to date greaterDate
	set laterURL to ""
	set theURLs to {}
	set theNSURL to current application's class "NSURL"'s fileURLWithPath:folderPosixPath
	set theNSFileManager to current application's NSFileManager's defaultManager()
	set addedKey to current application's NSURLAddedToDirectoryDateKey
	set theURLs to theNSFileManager's contentsOfDirectoryAtURL:theNSURL includingPropertiesForKeys:{current application's NSURLIsDirectoryKey, current application's NSURLIsPackageKey, addedKey} options:(current application's NSDirectoryEnumerationSkipsHiddenFiles) |error|:(missing value) -- skip hidden 
	repeat with anNSURL in theURLs
		set {theResult, isDirectory} to (anNSURL's getResourceValue:(reference) forKey:(current application's NSURLIsDirectoryKey) |error|:(missing value))
		if isDirectory as boolean then
			set {theResult, isPackage} to (anNSURL's getResourceValue:(reference) forKey:(current application's NSURLIsPackageKey) |error|:(missing value))
			if isPackage as boolean then
				-- it's a package
				set {theResult, theDate} to (anNSURL's getResourceValue:(reference) forKey:addedKey |error|:(missing value))
			else
				-- It's a folder; ignore
			end if
		else
			-- it's a file
			set {theResult, theDate} to (anNSURL's getResourceValue:(reference) forKey:addedKey |error|:(missing value))
		end if
		if theDate is not missing value then
			set theDate to (theDate as date)
			if theDate > greaterDate then
				set greaterDate to theDate
				set laterURL to anNSURL
			end if
		end if
	end repeat
	return (laterURL as text)
end GetLastPath:

Yvan KOENIG running El Capitan 10.11.5 in French (VALLAURIS, France) mardi 21 juin 2016 17:45:51

Hi again


tell application "Finder"
	set TodayTable to last item of (sort files of folder "Enecsys" by modification date) as alias
	set Aloaha to ":volumes:macintosh hd:users:pierrescerri:Library:Mobile Documents:com~apple~Numbers:Documents:Aloaha Tables.numbers" as alias
end tell

tell application "Numbers"
	activate
	
	-- Get file references
	set TT to open TodayTable
	set AO to open Aloaha
	
	-- Get Production values from Source (TT)
	tell table 1 of sheet 1 of document (name of TT) to set TodayProd to value of every cell of column 6
	
	
	tell table "Production" of sheet 1 of document (name of AO)
		-- Set Production values in Destination (AO)
		repeat with p from 2 to 7
			set value of last cell of column p to item p of TodayProd
		end repeat
	end tell
end tell


The list ‘TodayProd’ returns number values as strings.

is there another (simpler) way to transfer the values from one spreadsheet (TT) to the other (AO) i.e. without using a repeat loop.

This is more for fun & learning. Loops are an easy way out of most repetitive tasks. Can I use lists for example:

set value of last cell of (columns 2 thru 7) to every item of TodayProd (I know this doesn’t work - it’s just the idea)

Thanks.

Hi

I thought that the ‘Activate’ command had solved the problem:

I am randomly (it seems) getting this result in the event log:

tell application “Numbers”
activate
open alias “Macintosh HD:users:pierrescerri:Desktop:Enecsys:20160626.xlsx”
→ ldap server id “3A13B1D4-6056-485E-A51B-2DDDDE7BB6A8”
open alias “Macintosh HD:users:pierrescerri:Documents:iWork:Aloaha Tables.numbers”
→ ldap server id “FB8C4798-ED43-42A7-ABB5-09C5AE77C6B8”
get name of remove id “3A13B1D4-6056-485E-A51B-2DDDDE7BB6A8”
→ error number -1700 from remove id “3A13B1D4-6056-485E-A51B-2DDDDE7BB6A8” to specifier
Result:
error “Numbers got an error: Can’t make remove id "3A13B1D4-6056-485E-A51B-2DDDDE7BB6A8" into type specifier.” number -1700 from remove id “3A13B1D4-6056-485E-A51B-2DDDDE7BB6A8” to specifier

If I run the script a couple of times it will eventually work.

Thank you for any suggestions.