Automatically create numbers documents from one worksheet with a table

e,

My choice would be to do all calculations in Numbers and then to do a Mail Merge to a Pages document. Whether this would work for you depends on the complexity of your invoices.

Jerry

I can think of a way it can be "done" but it's not exactly intuitive -- more of a workaround. Perhaps somebody else can come up with something a little more eloquent.

If I'm understanding you correctly, you want your Invoices as separate tables or sheets that will automatically pop up, 1 for each line of your main table. I'm not aware of any way to accomplish that.

What I can think of, however, is to create a new table where each N-lines (for one of my documents, it's 68), the number of lines that fill up a standard 8.5 x 11 sheet of printed paper, propogate for each line of your main table. So, given that 68-lines-per-page number works for you, if your main table has 5 lines, this table has 340 visible lines; if your main table has 10 lines, this table has 680.

The secret is to have a maximum size for your input table and create your Invoices table based on that size. Add what will be a hidden column, a checkmark column, where for each set of 68 lines, the column is true if a given cell on the page has a value (given from the main table). Click on the header letter for this column and before you hide it, click "Show More Options" and select "Show Rows That Match The Following" to only show rows where this column is TRUE. Then Hide the column.

On the plus side, a single PRINT command will now print -all- your invoices.

Vince

To the best of my knowledge, the only way to exchange data between two different Numbers documents is to use Applescript.

Vince

It does require knowledge of a different means of interaction with data, true, but Applescript is one of the joys of working in the Mac world. Applescript allows the means for different programs to interact with each other automatically.

Example: a friend of mine has a private practice. I wrote a script that goes into his calendar each night, looks for clients over the next 72 hours with whom he has appointments and who have not been sent a confirmation email. The script then goes into his Address Book to find email addresses for those clients. It then uses his email program to send a generated email to those clients, and finally goes back to his calendar to mark those appointments as having been sent the confirmation email.

How much effort would it take to do that in Excel?

My point is that there are times when Excel excells over Numbers, but there are times when Numbers exceeds Excel. Use the right tool for the right job.

Vince

Send a sample csv file to my mailbox. (click my blue name to get my address)

I will try to build a script doing the trick.

I already downloaded thr template.

Yvan KOENIG (VALLAURIS, France) lundi 5 septembre 2011 09:57:11

iMac 21”5, i7, 2.8 GHz, 4 Gbytes, 1 Tbytes, mac OS X 10.6.8 and 10.7.0

My iDisk is : <http://public.me.com/koenigyvan>

Please : Search for questions similar to your own before submitting them to the community

The script is done but I wish to make some additional tests before sending it.

I will do that tomorrow.

AppleScript was required with Excel too with Office 2008 which dropped the VBA support.

Yvan KOENIG (VALLAURIS, France) lundi 5 septembre 2011 23:48:59

iMac 21”5, i7, 2.8 GHz, 4 Gbytes, 1 Tbytes, mac OS X 10.6.8 and 10.7.0

My iDisk is : <http://public.me.com/koenigyvan>

Please : Search for questions similar to your own before submitting them to the community

Here is the script :

--{code}

--[SCRIPT invoiceWithDateAndnumber_fromCSV]

(*

Enregistrer ce script en tant que Script : invoiceWithDateAndnumber_fromCSV.scpt

déplacer le fichier créé dans le dossier

<VolumeDeDémarrage>:Users:<votreCompte>:Library:Scripts:Applications:Numbers:

Il vous faudra peut-être créer le dossier Numbers et peut-être même le dossier Applications.

Le fichier modèle "facture.nmbtemplate" doit être stocké dans le dossier :

<disqueDeDémarrage>:Utilisateurs:<compteUtilisateur>:Bibliothèque:Application Support:iWork:Numbers:Modèles:Mes modèles:

menu Scripts > Numbers > invoiceWithDateAndnumber_fromCSV

crée de nouveaux documents à partir du modèle personnel prédéfini

et insère des valeurs extraites d'un fichier csv.

Les fichiers sont stockés dans le dossier :

<disqueDeDémarrage>:Utilisateurs:<compteUtilisateur>:Documents:mes_factures:

--=====

L'aide du Finder explique:

L'Utilitaire AppleScript permet d'activer le Menu des scripts :

Ouvrez l'Utilitaire AppleScript situé dans le dossier Applications/AppleScript.

Cochez la case "Afficher le menu des scripts dans la barre de menus".

+++++++++

Save this script as Script : invoiceWithDateAndnumber_fromCSV.scpt

Move the newly created file into the folder:

<startup Volume>:Users:<yourAccount>:Library:Scripts:Applications:Numbers:

Maybe you would have to create the folder Numbers and even the folder Applications by yourself.

The template file must be stored in the folder :

<startupVolume>:Users:<userAccount>:Library:Application Support:iWork:Numbers:Templates:My Templates:

menu Scripts > Numbers > invoiceWithDateAndnumber_fromCSV

will create new documents from the defined user template

and insert values grabbed from a csv file.

The files ares stored in the folder:

<startupVolume>:Users:<userAccount>:Documents:my_invoices:

--=====

The Finder's Help explains:

To make the Script menu appear:

Open the AppleScript utility located in Applications/AppleScript.

Select the "Show Script Menu in menu bar" checkbox.

--=====

Yvan KOENIG (VALLAURIS, France)

2011/09/05

*)

--=====

property theApp : "Numbers"

property |alias_du_modèle| : ""

property dossier_de_stockage : ""

property fichierNum : ""

--=====

(*

Entry point used if you double clic the script’s icon

or if you trigger it from the Scripts menu.

*)

on run

if my parleAnglais() then

set un_csv to choose file with prompt "Choose the csv file storing the datas" of type {"public.comma-separated-values-text"}

else

set un_csv to choose file with prompt "Sélectionner le fichier CSV contenant les données" of type {"public.comma-separated-values-text"}

end if

my main(un_csv)

end run

--=====

(*

Entry point used if you drag an drop the CSV’s icon on the script’s one.

*)

on open sel

set un_csv to item 1 of sel

tell application "System Events"

set maybe to type identifier of disk item (un_csv as text) is "public.comma-separated-values-text"

end tell

if maybe then

my main(un_csv)

else

if my parleAnglais() then

error "" & un_csv & return & "isn’t a CSV file !"

else

error "" & un_csv & return & "n’est pas un fichier CSV !"

end if

end if

end open

--=====

on main(le_csv)

(*

Use the properties :

fichierNum, dossier_de_stockage , |alias_du_modèle|

*)

local |mon_modèle|, dossier_factures, p2documents, d1, d2, p2ApplicationsSupport, en_liste, i, une_ligne

my nettoie()

if my parleAnglais() then

set |mon_modèle| to "invoice.nmbtemplate"

set dossier_factures to "my_invoices"

else

set |mon_modèle| to "facture.nmbtemplate"

set dossier_factures to "mes_factures"

end if

set fichierNum to |mon_modèle| & "_" & reverse of (characters of |mon_modèle|) & ".txt"

(*

Create the storage folder if it doesn’t exist. *)

set p2documents to (path to documents folder) as text

tell application "System Events"

if not (existsfolderdossier_factures of folderp2documents) then makenewfolderat end of folderp2documentswith properties {name:dossier_factures}

end tell

set dossier_de_stockage to p2documents & dossier_factures & ":"

tell applicationtheApp

set d1 to localized string "Templates" (* nom local du dossier "Modèles" *)

set d2 to localized string "My Templates" (* nom local du dossier "Mes Modèles" *)

end tell -- theApp

set p2ApplicationsSupport to (path toapplication supportfromuser domain) as Unicode text

set |alias_du_modèle| to p2ApplicationsSupport & "iWork:" & theApp & ":" & d1 & ":" & d2 & ":" & |mon_modèle| & ":"

try

set |alias_du_modèle| to |alias_du_modèle| as alias

on error

if my parleAnglais() then

error "The template “" & |alias_du_modèle| & "” is unavailable! Please make sure that the template file “" & |mon_modèle| & "” is installed in the folder “" & d1 & ":" & d2 & "” of Numbers, before running this script."

else

error "Le modèle « " & |alias_du_modèle| & " » est introuvable! Veuillez installer le fichier modèle « " & |mon_modèle| & " » dans le dossier « " & d1 & ":" & d2 & " » de Numbers avant de relancer ce script."

end if

end try

set en_liste to paragraphs of (read le_csv)

if item 1 of en_liste contains "," then

set delim to ","

else

set delim to ";"

end if

repeat with i from 2 to count of en_liste

set une_ligne to itemi of en_liste

(*

Split a line of datas into values.

Assume that there is no character 'delim' in the passed values.

*)

if une_ligne > "" then my cree_une_facture(my decoupe(une_ligne, delim))

end repeat

my nettoie()

end main

--=====

on nettoie()

set |alias_du_modèle| to ""

set dossier_de_stockage to ""

set fichierNum to ""

end nettoie

--=====

on cree_une_facture(fiche_en_liste)

local cellules_cibles, une_fiche, |numéro|, nouveau_nom

local le_chemin, documents_ouverts, nom_du_document, i

(*

List of target cells *)

set cellules_cibles to {"D4", "D9", "D23", "D10", "D11", "D12", "D13", "A17", "A26"}

(*

Build the invoice num *)

set |numéro| to text -5 thru -1 of ("0000" & my get_a_num()) (* pour numéro de 5 chiffres *)

if my parleAnglais() then

set nouveau_nom to "my_invoice_" & |numéro| & ".numbers"

else

set nouveau_nom to "ma_facture_" & |numéro| & ".numbers"

end if

(*

Create an invoice file to save in soon *)

tell application "System Events" to makenewfileat end of folderdossier_de_stockagewith properties {name:nouveau_nom}

set le_chemin to (dossier_de_stockage & nouveau_nom) as alias

tell application "Numbers"

(*

Create a new document from the template *)

set documents_ouverts to name of documents

activate

open|alias_du_modèle|

repeat while (count of documents) is (count of documents_ouverts)

delay 0.1

end repeat

set nom_du_document to name of document 1

(*

Fill the document with invoice # and passed values *)

tell document nom_du_document to tell sheet 1 to tell table 1

tell cell "D5"

set format to text

set value to |numéro|

end tell

repeat with i from 1 to count of cellules_cibles

try

set value of cell (item i of cellules_cibles) to (item i of fiche_en_liste) as text

end try

end repeat

end tell -- document nom_du_document

savedocumentnom_du_documentinle_chemin

(*

Enable this instruction if you want to close the doc automatically *)

--close document nom_du_document

end tell -- Numbers

end cree_une_facture

--=====

on get_a_num()

local le_dossier, p2n, nn

(*

Use the properties : fichierNum,|alias_du_modèle|

*)

tell application "System Events"

set le_dossier to path of container of |alias_du_modèle|

set p2n to le_dossier & fichierNum

if not (exists file p2n) then

makenewfileat end of folderle_dossierwith properties {name:fichierNum}

write "100" tofilep2n(* mettez le numéro de départ de votre choix *)

end if -- not…

end tell -- System Events

set nn to read file p2n

set nn to ((nn as integer) + 1) as text

writenntofilep2nstarting at 1

return nn

end get_a_num

--=====

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

--=====

on decoupe(t, d)

local oTIDs, l

set oTIDs to AppleScript's text item delimiters

set AppleScript's text item delimiters to d

set l to text items of t

set AppleScript's text item delimiters to oTIDs

return l

end decoupe

--=====

--[/SCRIPT]

--{code}

CAUTION :

what you sent is not a template but a standard document.

A Numbers template must be named xxxxx.nmbtemplate.

The script assume that the template is named invoice.nmbtemplate (or facture.nmbtempate for French users.

The template is supposed to be stored in the standard templates folder:

<startupVolume>Users:<userAccount>:Library:Application Support:iWork:Numbers:Templates:My Templates:

or

<disqueDeDémarrage:Utilisateurs:<compteUtilisateur>:Library:Application Support:iWork:Numbers:Modèles:Mes modèles:

for French users.

The embedded instructions explain where you must store the script if you want to use it from the Scripts menu.

You may also save it as an Application.

If yo do that, you may run it

(a) with a double click on its icon

(b) by drag & drop of the CSV’s icon on the script’s one.

Let me know if changes are needed.

Yvan KOENIG (VALLAURIS, France) mardi 6 septembre 2011 12:34:39

iMac 21”5, i7, 2.8 GHz, 4 Gbytes, 1 Tbytes, mac OS X 10.6.8 and 10.7.0

My iDisk is : <http://public.me.com/koenigyvan>

Please : Search for questions similar to your own before submitting them to the community

I was not satisfied of the way the 1st version treated the invoice # so I revised it.

I think that this new version is better.

--{code}

--[SCRIPT invoiceWithDateAndnumber_fromCSV]

(*

Enregistrer ce script en tant que Script : invoiceWithDateAndnumber_fromCSV.scpt

déplacer le fichier créé dans le dossier

<VolumeDeDémarrage>:Users:<votreCompte>:Library:Scripts:Applications:Numbers:

Il vous faudra peut-être créer le dossier Numbers et peut-être même le dossier Applications.

Le fichier modèle "facture.nmbtemplate" doit être stocké dans le dossier :

<disqueDeDémarrage>:Utilisateurs:<compteUtilisateur>:Bibliothèque:Application Support:iWork:Numbers:Modèles:Mes modèles:

menu Scripts > Numbers > invoiceWithDateAndnumber_fromCSV

crée de nouveaux documents à partir du modèle personnel prédéfini

et insère des valeurs extraites d'un fichier csv.

Les fichiers sont stockés dans le dossier :

<disqueDeDémarrage>:Utilisateurs:<compteUtilisateur>:Documents:mes_factures:

--=====

L'aide du Finder explique:

L'Utilitaire AppleScript permet d'activer le Menu des scripts :

Ouvrez l'Utilitaire AppleScript situé dans le dossier Applications/AppleScript.

Cochez la case "Afficher le menu des scripts dans la barre de menus".

+++++++++

Save this script as Script : invoiceWithDateAndnumber_fromCSV.scpt

Move the newly created file into the folder:

<startup Volume>:Users:<yourAccount>:Library:Scripts:Applications:Numbers:

Maybe you would have to create the folder Numbers and even the folder Applications by yourself.

The template file must be stored in the folder :

<startupVolume>:Users:<userAccount>:Library:Application Support:iWork:Numbers:Templates:My Templates:

menu Scripts > Numbers > invoiceWithDateAndnumber_fromCSV

will create new documents from the defined user template

and insert values grabbed from a csv file.

The files ares stored in the folder:

<startupVolume>:Users:<userAccount>:Documents:my_invoices:

--=====

The Finder's Help explains:

To make the Script menu appear:

Open the AppleScript utility located in Applications/AppleScript.

Select the "Show Script Menu in menu bar" checkbox.

--=====

Yvan KOENIG (VALLAURIS, France)

2011/09/05

2011/09/06 changed the treatment of the invoice #. Now the script read and write it only once when applied to a CSV file

*)

--=====

property |alias_du_modèle| : ""

property dossier_de_stockage : ""

property fichierNum : ""

--=====

(*

Entry point used if you double clic the script’s icon

or if you trigger it from the Scripts menu.

*)

on run

if my parleAnglais() then

set un_csv to choose file with prompt "Choose the csv file storing the datas" of type {"public.comma-separated-values-text"}

else

set un_csv to choose file with prompt "Sélectionner le fichier CSV contenant les données" of type {"public.comma-separated-values-text"}

end if

my main(un_csv)

end run

--=====

(*

Entry point used if you drag an drop the CSV’s icon on the script’s one.

*)

on open sel

set un_csv to item 1 of sel

tell application "System Events"

set maybe to type identifier of disk item (un_csv as text) is "public.comma-separated-values-text"

end tell

if maybe then

my main(un_csv)

else

if my parleAnglais() then

error "" & un_csv & return & "isn’t a CSV file !"

else

error "" & un_csv & return & "n’est pas un fichier CSV !"

end if

end if

end open

--=====

on main(le_csv)

(*

Define the properties : fichierNum, dossier_de_stockage , |alias_du_modèle|

*)

local |mon_modèle|, dossier_factures, p2documents, d1, d2, p2ApplicationsSupport, en_liste, i, une_ligne

(*

Define two parameters *)

if my parleAnglais() then

set |mon_modèle| to "invoice.nmbtemplate"

set dossier_factures to "my_invoices"

else

set |mon_modèle| to "facture.nmbtemplate"

set dossier_factures to "mes_factures"

end if

(*

Create the storage folder if it doesn’t exist. *)

set p2documents to (path to documents folder) as text

set dossier_de_stockage to p2documents & dossier_factures & ":"

tell application "System Events"

if not (existsfolderdossier_de_stockage) then makenewfolderat end of folderp2documentswith properties {name:dossier_factures}

end tell

(*

Grab the localized names of the two default subfolders storing the templates *)

tell application "Numbers"

set d1 to localized string "Templates" (* nom local du dossier "Modèles" *)

set d2 to localized string "My Templates" (* nom local du dossier "Mes Modèles" *)

end tell -- Numbers

set p2ApplicationsSupport to (path toapplication supportfromuser domain) as Unicode text

set |alias_du_modèle| to p2ApplicationsSupport & "iWork:Numbers:" & d1 & ":" & d2 & ":" & |mon_modèle| & ":"

try

set |alias_du_modèle| to |alias_du_modèle| as alias

on error

if my parleAnglais() then

error "The template “" & |alias_du_modèle| & "” is unavailable! Please make sure that the template file “" & |mon_modèle| & "” is installed in the folder “" & d1 & ":" & d2 & "” of Numbers, before running this script."

else

error "Le modèle « " & |alias_du_modèle| & " » est introuvable! Veuillez installer le fichier modèle « " & |mon_modèle| & " » dans le dossier « " & d1 & ":" & d2 & " » de Numbers avant de relancer ce script."

end if

end try

(*

According to the template’s name, define the name of the text file storing the last used invoice # *)

set fichierNum to |mon_modèle| & "_" & reverse of (characters of |mon_modèle|) & ".txt"

tell application "System Events"

set le_dossier to path of container of |alias_du_modèle|

set p2n to le_dossier & fichierNum

if not (exists file p2n) then

makenewfileat end of folderle_dossierwith properties {name:fichierNum}

write "0" tofilep2n(* mettez le numéro de départ de votre choix *)

end if -- not…

end tell -- System Events

(*

Read the last used invoice # *)

set nn to (read file p2n) as integer

(*

Read the source CSV file *)

set en_liste to paragraphs of (read le_csv)

if item 1 of en_liste contains "," then

set delim to ","

else

set delim to ";"

end if

repeat with i from 2 to count of en_liste

set une_ligne to itemi of en_liste

(*

Assuming that there is no character 'delim' in the passed values, split a line of datas into values to insert in invoices.

*)

if une_ligne > "" then

set nn to nn + 1 (* increment the invoice # and pass it in the format 00001 *)

my cree_une_facture(my decoupe(une_ligne, delim), text -5 thru -1 of ("0000" & nn))

end if

end repeat

(*

Write the last used invoice # in the dedicated text file *)

write (nn as text) tofilep2nstarting at 1

(*

Clean the three properties used so that their contents will not be saved in the script *)

my nettoie()

end main

--=====

on nettoie()

set |alias_du_modèle| to ""

set dossier_de_stockage to ""

set fichierNum to ""

end nettoie

--=====

on cree_une_facture(fiche_en_liste, |numéro|)

local cellules_cibles, une_fiche, nouveau_nom, le_chemin, documents_ouverts, nom_du_document, i

(*

List of target cells *)

set cellules_cibles to {"D4", "D9", "D23", "D10", "D11", "D12", "D13", "A17", "A26"}

(*

Build the invoice name *)

if my parleAnglais() then

set nouveau_nom to "my_invoice_" & |numéro| & ".numbers"

else

set nouveau_nom to "ma_facture_" & |numéro| & ".numbers"

end if

(*

Create an invoice file to save in soon *)

tell application "System Events" to makenewfileat end of folderdossier_de_stockagewith properties {name:nouveau_nom}

set le_chemin to (dossier_de_stockage & nouveau_nom) as alias

tell application "Numbers"

(*

Create a new document from the template *)

set documents_ouverts to name of documents

activate

open|alias_du_modèle|

repeat while (count of documents) is (count of documents_ouverts)

delay 0.1

end repeat

set nom_du_document to name of document 1

(*

Fill the document with invoice # and passed values *)

tell document nom_du_document to tell sheet 1 to tell table 1

tell cell "D5"

set format to text

set value to |numéro|

end tell

repeat with i from 1 to count of cellules_cibles

try

set value of cell (item i of cellules_cibles) to (item i of fiche_en_liste) as text

end try

end repeat

end tell -- document nom_du_document

savedocumentnom_du_documentinle_chemin

(*

Enable this instruction if you want to close the doc automatically *)

--close document nom_du_document

end tell -- Numbers

end cree_une_facture

--=====

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

--=====

on decoupe(t, d)

local oTIDs, l

set oTIDs to AppleScript's text item delimiters

set AppleScript's text item delimiters to d

set l to text items of t

set AppleScript's text item delimiters to oTIDs

return l

end decoupe

--=====

--[/SCRIPT]

--{code}

Yvan KOENIG (VALLAURIS, France) mardi 6 septembre 2011 14:41:30

iMac 21”5, i7, 2.8 GHz, 4 Gbytes, 1 Tbytes, mac OS X 10.6.8 and 10.7.0

My iDisk is : <http://public.me.com/koenigyvan>

Please : Search for questions similar to your own before submitting them to the community

(a) No reward required.

I must add that here, it must be spelled "no reward allowed".

(1) For a French user, the text file storing the laste invoice # used is :

<disqueDeDémarrage>:Utilisateurs:<compteUtilisateur>:Bibliothèque:Application Support:iWork:Numbers:Modèles:Mes modèles:facture.nmbtemplate_etalpmetbmn.erutcaf.txt

For an English one it is :

<startupVolume>:Users:<userAccount>:Library:Application Support:iWork:Numbers:Templates:My Templates:invoice.nmbtemplate_etalpmetbmn.eciovni.txt

The file name is build by the concatenation of the template name and its reversed version.

This way I guess that it will not conflict with an othe tool.

In the old original script creating a single invoice, the file was embedded in the package.

To accomodate the introduction of flatfiles, I started with a code using two locations :

-- in the package for … packages 😉

-- in the "My Templates" folder for flatfiles

Yesterdays I decided to keep a single location which require less job to maintain the script (don‘t forget that I am lazy) and which offer easiest acces if some change must be done.

The first version posted here started with the index 101 but the 2nd one starts with the index 1.

At this time it way work from 1 thru 99999.

(2) If you look carefully, you will see :

--{code}

savedocumentnom_du_documentinle_chemin

(*

Enable this instruction if you want to close the doc automatically *)

--close document nom_du_document

end tell -- Numbers

end cree_une_facture

--{code}

edit it this way :

--{code}

savedocumentnom_du_documentinle_chemin

(*

Enable this instruction if you want to close the doc automatically *)

closedocument1

end tell -- Numbers

end cree_une_facture

--{code}

Yes, enable the instruction but edit it because when it's called, the name of the doc is no longer nom_du_document 😟

This way, the invoices will be closed automatically.

An instruction like :

would make sense only if the variables numbersStarted and numbersRunning were defined before.

There is no provision in the standard AppleScript support of Numbers to do that.

This instruction :

tell application "System Events" to set numbersLoaded to (name of application processes) contains "Numbers"

at the beginnining of the main handler would return true if Numbers was loaded but I don't know how to know if it is running

I may get some infos about a process ::

process n, pl processes [inh. UI element > item] : A process running on this computer

elements

contained by application.

properties

accepts high level events (boolean, r/o) : Is the process high-level event aware (accepts open application, open document, print document, and quit)?

accepts remote events (boolean, r/o) : Does the process accept remote events?

architecture (text, r/o) : the architecture in which the process is running

background only (boolean, r/o) : Does the process run exclusively in the background?

bundle identifier (text, r/o) : the bundle identifier of the process' application file

Classic (boolean, r/o) : Is the process running in the Classic environment?

creator type (text, r/o) : the OSType of the creator of the process (the signature)

displayed name (text, r/o) : the name of the file from which the process was launched, as displayed in the User Interface

file (alias, r/o) : the file from which the process was launched

file type (text, r/o) : the OSType of the file type of the process

frontmost (boolean) : Is the process the frontmost process

has scripting terminology (boolean, r/o) : Does the process have a scripting terminology, i.e., can it be scripted?

id (unsigned integer, r/o) : The unique identifier of the process

name (text, r/o) : the name of the process

partition space used (unsigned integer, r/o) : the number of bytes currently used in the process' partition

short name (text, r/o) : the short name of the file from which the process was launched

total partition size (unsigned integer, r/o) : the size of the partition with which the process was launched

unix id (integer, r/o) : The Unix process identifier of a process running in the native environment, or -1 for a process running in the Classic environment

visible (boolean) : Is the process' layer visible?

but none may give a valuable info.

Numbers may be loaded without 'running' which means without triggering the micro-processors

It may be working without being the frontmost one.

So at this timewhat I may offer for a serious task is :

Insert

tell application "System Events" to set numbersLoaded to (name of application processes) contains "Numbers"

at the beginning of the main() handler

and insert :

if numbersLoaded then tell application "Numbers" to quit

just before the instruction end main.

(3) In standard AppleScript we can't define popup menus but the feature

display dialog give the ability to define a value on the fly.

I did that using one of my ready_to_use handlers.

Here is the 3rd version :

--{code}

--[SCRIPT invoiceWithDateAndnumber_fromCSV]

(*

Enregistrer ce script en tant que Script : invoiceWithDateAndnumber_fromCSV.scpt

déplacer le fichier créé dans le dossier

<VolumeDeDémarrage>:Users:<votreCompte>:Library:Scripts:Applications:Numbers:

Il vous faudra peut-être créer le dossier Numbers et peut-être même le dossier Applications.

Le fichier modèle "facture.nmbtemplate" doit être stocké dans le dossier :

<disqueDeDémarrage>:Utilisateurs:<compteUtilisateur>:Bibliothèque:Application Support:iWork:Numbers:Modèles:Mes modèles:

menu Scripts > Numbers > invoiceWithDateAndnumber_fromCSV

crée de nouveaux documents à partir du modèle personnel prédéfini

et insère des valeurs extraites d'un fichier csv.

Les fichiers sont stockés dans le dossier :

<disqueDeDémarrage>:Utilisateurs:<compteUtilisateur>:Documents:mes_factures:

--=====

L'aide du Finder explique:

L'Utilitaire AppleScript permet d'activer le Menu des scripts :

Ouvrez l'Utilitaire AppleScript situé dans le dossier Applications/AppleScript.

Cochez la case "Afficher le menu des scripts dans la barre de menus".

+++++++++

Save this script as Script : invoiceWithDateAndnumber_fromCSV.scpt

Move the newly created file into the folder:

<startup Volume>:Users:<yourAccount>:Library:Scripts:Applications:Numbers:

Maybe you would have to create the folder Numbers and even the folder Applications by yourself.

The template file must be stored in the folder :

<startupVolume>:Users:<userAccount>:Library:Application Support:iWork:Numbers:Templates:My Templates:

menu Scripts > Numbers > invoiceWithDateAndnumber_fromCSV

will create new documents from the defined user template

and insert values grabbed from a csv file.

The files ares stored in the folder:

<startupVolume>:Users:<userAccount>:Documents:my_invoices:

--=====

The Finder's Help explains:

To make the Script menu appear:

Open the AppleScript utility located in Applications/AppleScript.

Select the "Show Script Menu in menu bar" checkbox.

--=====

Yvan KOENIG (VALLAURIS, France)

2011/09/05

2011/09/06 changed the treatment of the invoice #. Now the script read and write it only once when applied to a CSV file

2011/09/06 corrected a wrong instruction, added code to quit on exit, code to insert the conversion rate ($ to €),

*)

--=====

property |alias_du_modèle| : ""

property dossier_de_stockage : ""

property fichierNum : ""

property default_rate : 0.70602

--=====

(*

Entry point used if you double clic the script’s icon

or if you trigger it from the Scripts menu.

*)

on run

if my parleAnglais() then

set un_csv to choose file with prompt "Choose the csv file storing the datas" of type {"public.comma-separated-values-text"}

else

set un_csv to choose file with prompt "Sélectionner le fichier CSV contenant les données" of type {"public.comma-separated-values-text"}

end if

my main(un_csv)

end run

--=====

(*

Entry point used if you drag an drop the CSV’s icon on the script’s one.

*)

on open sel

set un_csv to item 1 of sel

tell application "System Events"

set maybe to type identifier of disk item (un_csv as text) is "public.comma-separated-values-text"

end tell

if maybe then

my main(un_csv)

else

if my parleAnglais() then

error "" & un_csv & return & "isn’t a CSV file !"

else

error "" & un_csv & return & "n’est pas un fichier CSV !"

end if

end if

end open

--=====

on main(le_csv)

(*

Define the properties : fichierNum, dossier_de_stockage , |alias_du_modèle|

*)

local |mon_modèle|, dossier_factures, p2documents, d1, d2, p2ApplicationsSupport, en_liste, i, une_ligne

tell application "System Events" to set numbersLoaded to (name of application processes) contains "Numbers"

if my parleAnglais() then

set conversionRate to my ask_a_number("Enter the current conversion rate ($ to €)", default_rate, "f")

else

set conversionRate to my ask_a_number("Donner le taux de conversion ($ en €)", default_rate, "f")

end if

(*

Define two parameters *)

if my parleAnglais() then

set |mon_modèle| to "invoice.nmbtemplate"

set dossier_factures to "my_invoices"

else

set |mon_modèle| to "facture.nmbtemplate"

set dossier_factures to "mes_factures"

end if

(*

Create the storage folder if it doesn’t exist. *)

set p2documents to (path to documents folder) as text

set dossier_de_stockage to p2documents & dossier_factures & ":"

tell application "System Events"

if not (existsfolderdossier_de_stockage) then makenewfolderat end of folderp2documentswith properties {name:dossier_factures}

end tell

(*

Grab the localized names of the two default subfolders storing the templates *)

tell application "Numbers"

set d1 to localized string "Templates" (* nom local du dossier "Modèles" *)

set d2 to localized string "My Templates" (* nom local du dossier "Mes Modèles" *)

end tell -- Numbers

set p2ApplicationsSupport to (path toapplication supportfromuser domain) as Unicode text

set |alias_du_modèle| to p2ApplicationsSupport & "iWork:Numbers:" & d1 & ":" & d2 & ":" & |mon_modèle| & ":"

try

set |alias_du_modèle| to |alias_du_modèle| as alias

on error

if my parleAnglais() then

error "The template “" & |alias_du_modèle| & "” is unavailable! Please make sure that the template file “" & |mon_modèle| & "” is installed in the folder “" & d1 & ":" & d2 & "” of Numbers, before running this script."

else

error "Le modèle « " & |alias_du_modèle| & " » est introuvable! Veuillez installer le fichier modèle « " & |mon_modèle| & " » dans le dossier « " & d1 & ":" & d2 & " » de Numbers avant de relancer ce script."

end if

end try

(*

According to the template’s name, define the name of the text file storing the last used invoice # *)

set fichierNum to |mon_modèle| & "_" & reverse of (characters of |mon_modèle|) & ".txt"

tell application "System Events"

set le_dossier to path of container of |alias_du_modèle|

set p2n to le_dossier & fichierNum

if not (exists file p2n) then

makenewfileat end of folderle_dossierwith properties {name:fichierNum}

write "0" tofilep2n(* mettez le numéro de départ de votre choix *)

end if -- not…

end tell -- System Events

(*

Read the last used invoice # *)

set nn to (read file p2n) as integer

(*

Read the source CSV file *)

set en_liste to paragraphs of (read le_csv)

if item 1 of en_liste contains "," then

set delim to ","

else

set delim to ";"

end if

repeat with i from 2 to count of en_liste

set une_ligne to itemi of en_liste

(*

Assuming that there is no character 'delim' in the passed values, split a line of datas into values to insert in invoices.

*)

if une_ligne > "" then

set nn to nn + 1 (* increment the invoice # and pass it in the format 00001 *)

my cree_une_facture(my decoupe(une_ligne, delim), text -5 thru -1 of ("0000" & nn), conversionRate)

end if

end repeat

(*

Write the last used invoice # in the dedicated text file *)

write (nn as text) tofilep2nstarting at 1

(*

Clean the three properties used so that their contents will not be saved in the script *)

my nettoie()

if numbersLoaded then tell application "Numbers" to quit

end main

--=====

on nettoie()

set |alias_du_modèle| to ""

set dossier_de_stockage to ""

set fichierNum to ""

end nettoie

--=====

on cree_une_facture(fiche_en_liste, |numéro|, conversion_rate)

local cellules_cibles, une_fiche, nouveau_nom, le_chemin, documents_ouverts, nom_du_document, i

(*

List of target cells *)

set cellules_cibles to {"D4", "D9", "D23", "D10", "D11", "D12", "D13", "A17", "A26"}

(*

Build the invoice name *)

if my parleAnglais() then

set nouveau_nom to "my_invoice_" & |numéro| & ".numbers"

else

set nouveau_nom to "ma_facture_" & |numéro| & ".numbers"

end if

(*

Create an invoice file to save in soon *)

tell application "System Events" to makenewfileat end of folderdossier_de_stockagewith properties {name:nouveau_nom}

set le_chemin to (dossier_de_stockage & nouveau_nom) as alias

tell application "Numbers"

(*

Create a new document from the template *)

set documents_ouverts to name of documents

activate

open|alias_du_modèle|

repeat while (count of documents) is (count of documents_ouverts)

delay 0.1

end repeat

set nom_du_document to name of document 1

(*

Fill the document with invoice # and passed values *)

tell document nom_du_document to tell sheet 1 to tell table 1

tell cell "D5"

set format to text

set value to |numéro|(* which is built as a string *)

end tell

(*

Coercion to text is required to pass the correct decimal separator *)

set value of cell "E26" to (conversion_rate as text)

repeat with i from 1 to count of cellules_cibles

try

set value of cell (item i of cellules_cibles) to (item i of fiche_en_liste) as text

end try

end repeat

end tell -- document nom_du_document

savedocumentnom_du_documentinle_chemin

(*

Enabled to close the doc automatically *)

closedocument 1

end tell -- Numbers

end cree_une_facture

--=====

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

--=====

on decoupe(t, d)

local oTIDs, l

set oTIDs to AppleScript's text item delimiters

set AppleScript's text item delimiters to d

set l to text items of t

set AppleScript's text item delimiters to oTIDs

return l

end decoupe

--=====

(*

Asks for an entry and checks that it is an floating number

set myInteger to my ask_a_number(Prompt, DefaultValue, "i")

set myFloating to my ask_a_number(Prompt, DefaultValue, "f")

*)

on ask_a_number(lPrompt, lDefault, IorF)

local lPrompt, lDefault, n

tell application (path tofrontmost applicationasstring)

if IorF is in {"F", "f"} then

set n to text returned of (display dialog (lPrompt & " (" & default_rate as text) & ")" default answer lDefault as text)

try

set n to n as number(* try to convert the value as an number *)

return n

on error

if my parleAnglais() then

display alert "The value needs to be a floating number." & return & "Please try again."

else

display alert "La valeur saisie doit être un nombre décimal." & return & "Veuillez recommencer."

end if

end try

else

set n to text returned of (display dialoglPromptdefault answerlDefault as text)

try

set n to n as integer(* try to convert the value as an integer *)

return n

on error

if my parleAnglais() then

display alert "The value needs to be an integer." & return & "Please try again."

else

display alert "La valeur saisie doit être un nombre entier." & return & "Veuillez recommencer."

end if

end try -- 1st attempt

end if -- IorF…

end tell -- application

(*

Here if the first entry was not of the wanted class

second attempt *)

tell application (path tofrontmost applicationasstring)

if IorF is in {"F", "f"} then

set n to text returned of (display dialog lPrompt & " (" & (1.2 as text) & ")" default answer lDefault as text)

try

set n to n as number(* try to convert the value as an number *)

return n

on error

--

end try

else

set n to text returned of (display dialoglPromptdefault answerlDefault as text)

try

set n to n as integer(* try to convert the value as an integer *)

return n

on error

--

end try -- 1st attempt

end if -- IorF…

end tell -- application

if my parleAnglais() then

error "The value you entered was not numerical !" & return & "Goodbye !"

else

error "La valeur saisie n’est pas numérique !" & return & "Au revoir !"

end if

end ask_a_number

--=====

--[/SCRIPT]

--{code}

Yvan KOENIG (VALLAURIS, France) mardi 6 septembre 2011 19:50:55

iMac 21”5, i7, 2.8 GHz, 4 Gbytes, 1 Tbytes, mac OS X 10.6.8 and 10.7.0

My iDisk is : <http://public.me.com/koenigyvan>

Please : Search for questions similar to your own before submitting them to the community