MoneyWorks Express is able to output a tab-delimited report; if no output medium is defined, the report is stored in AppleScript’s “result” variable.
I would like to read the contents of “result” into Numbers, using the delimiters to put the data into columns.
If I copy the result and paste it into Numbers it does this. I’d unable, though, to code the necessary to do the same in a script and would appreciate any feedback on the best way to achieve this.
Thanks,
Des Dougan
Hi,
save the result to disk and just open the file with Numbers
set theData to result -- assuming the text is the result of the preceding line
set tmpFile to (path to desktop as text) & (do shell script "date +%Y%m%d%H%M%S.txt")
try
set ff to open for access file tmpFile with write permission
write theData to ff
close access ff
tell application "Numbers"
open alias tmpFile
end tell
on error
try
close access file theFile
end try
end try
Stefan,
That works perfectly! Many thanks.
Des
I just discover this thread.
Here is a soluce with no temporary document.
--=====
on run
(*
Replace this read instruction by the code extracting your datas from MoneyWorks Express
*)
read file ((path to desktop as text) & "_20100729-152547.txt")
(*
Store the datas in the clipboard
*)
set the clipboard to result
(*
Activate GUI scripting to be able to paste
*)
my activateGUIscripting()
(*
Create a Numbers document from the Blank template
*)
set myNewDoc to my makeAnIworkDoc("Numbers")
tell application "Numbers" to tell document myNewDoc to tell sheet 1
delete table 1 (* It may be too large *)
make new table with properties {column count:2, row count:2}
tell table 1
remove row 1 (* remove the default header row *)
remove column 1 (* remove the default header column *)
end tell
end tell
my raccourci("Numbers", "v", "cas")
end run
--=====
on parleAnglais()
local z
try
tell application "Numbers" to set z to localized string "Cancel"
on error
set z to "Cancel"
end try
return (z is not "Annuler")
end parleAnglais
--=====
(*
Creates a new iWork document from the Blank template and returns its name.
example:
set myNewDoc to my makeAnIworkDoc(theApp)
*)
on makeAnIworkDoc(the_app)
local maybe, path_to_the_App, nb_doc, doc_name
if the_app is "Pages" then
tell application "Pages"
set nb_doc to count of documents
make new document with properties {template name:item 1 of templates}
end tell -- Pages
else if the_app is "Numbers" then
tell application "System Events" to set maybe to the_app is in title of every application process
if not maybe then tell application theApp to activate
(*
I use this scheme because on my machine, iWork '09 isn't on the startup volume.
I have three volumes : one for 10.4.11, one for 10.5.8, one for shared items.
This way I'm OK with iWork's License which claims : one copy for one machine
*)
tell application "System Events"
set path_to_the_App to get application file of application process the_app
end tell
tell application "Numbers"
set nb_doc to count of documents
open ((path_to_the_App as text) & "Contents:Resources:Templates:Blank.nmbtemplate:")
end tell
else
if my parleAnglais(theApp) then
error "The application "" & the_app & "" is not accepted !"
else
error "l'application « " & the_app & " » n'est pas gérée !"
end if
end if
tell application the_app
repeat until (count of documents) > nb_doc
delay 0.1
end repeat
set doc_name to name of document 1
end tell -- the_App
return doc_name
end makeAnIworkDoc
--=====
on activateGUIscripting()
(* to be sure that GUI scripting will be active *)
tell application "System Events"
if not (UI elements enabled) then set (UI elements enabled) to true
end tell
end activateGUIscripting
--=====
(*
==== Uses GUIscripting ====
*)
(*
This handler may be used to 'type' text, invisible characters if the third parameter is an empty string.
It may be used to 'type' keyboard raccourcis if the third parameter describe the required modifier keys.
I changed its name « shortcut » to « raccourci » to get rid of a name conflict in Smile.
*)
on raccourci(a, t, d)
local k
tell application a to activate
tell application "System Events" to tell application process a
set frontmost to true
try
t * 1
if d is "" then
key code t
else if d is "c" then
key code t using {command down}
else if d is "a" then
key code t using {option down}
else if d is "k" then
key code t using {control down}
else if d is "s" then
key code t using {shift down}
else if d is in {"ac", "ca"} then
key code t using {command down, option down}
else if d is in {"as", "sa"} then
key code t using {shift down, option down}
else if d is in {"sc", "cs"} then
key code t using {command down, shift down}
else if d is in {"kc", "ck"} then
key code t using {command down, control down}
else if d is in {"ks", "sk"} then
key code t using {shift down, control down}
else if (d contains "c") and (d contains "s") and d contains "k" then
key code t using {command down, shift down, control down}
else if (d contains "c") and (d contains "s") and d contains "a" then
key code t using {command down, shift down, option down}
end if
on error
repeat with k in t
if d is "" then
keystroke (k as text)
else if d is "c" then
keystroke (k as text) using {command down}
else if d is "a" then
keystroke k using {option down}
else if d is "k" then
keystroke (k as text) using {control down}
else if d is "s" then
keystroke k using {shift down}
else if d is in {"ac", "ca"} then
keystroke (k as text) using {command down, option down}
else if d is in {"as", "sa"} then
keystroke (k as text) using {shift down, option down}
else if d is in {"sc", "cs"} then
keystroke (k as text) using {command down, shift down}
else if d is in {"kc", "ck"} then
keystroke (k as text) using {command down, control down}
else if d is in {"ks", "sk"} then
keystroke (k as text) using {shift down, control down}
else if (d contains "c") and (d contains "s") and d contains "k" then
keystroke (k as text) using {command down, shift down, control down}
else if (d contains "c") and (d contains "s") and d contains "a" then
keystroke (k as text) using {command down, shift down, option down}
end if
end repeat
end try
end tell
end raccourci
--=====
Yvan KOENIG (VALLAURIS, France) jeudi 29 juillet 2010 15:53:31
20 temporary files are even better than one line of GUI scripting 
Yvan,
Many thanks for the script - it works very well. I did find one issue - if there is already a Numbers document open, the script seems to blank it so that it ends up with just a single cell in it. Otherwise, it does exactly what I was trying to get to.
Des
2000 temporary files are better than one line of GUI scripting…
I never got this behaviour.
I tested again for see under 10.4.11, 10.5.8 and 10.6.4
I don’t guess how you may get this behaviour.
For those which dislike GUIscripting, may you explain a way to select a sheet without GUI scripting.
I know that it’s not a perfect tool but when the AppleScript support is sparse, it’s useful and works very well.
If someone is able to trigger the “Categorize a Column” contextual menu item of Numbers, with or without GUIscripting, I opened a thread about that and he would be welcomed.
When I decide to use temporary files, I store them in the Temporary Items folder because I don’t like to put such files on my desktop.
I wish to add that stephan’s script gave me an useful info.
I didn’t know that we may define a file name extension in the sell script building a date_stamp file name.
I was accustomed to concatenate de result of the shell script with a string like “.txt”.
Yvan KOENIG (VALLAURIS, France) mardi 3 août 2010 18:43:38
Yvan,
Do you use Spaces? I’m also on 10.6.4 and it would appear our systems are pretty similar. I use Spaces and am wondering if that might be the cause.
Regards,
Des
I never used Spaces.
Yvan KOENIG (VALLAURIS, France) mercredi 4 août 2010 07:40:37
Yvan,
It seems to occur if the script is run for a second or subsequent time. The first window is the one which gets overwritten.
Do you see this?
Des
Never got that.
Will make new tests.
Yvan KOENIG (VALLAURIS, France) jeudi 12 août 2010 12:41:34