Numérotation automatisée de factures Excel en VBA

Pour partager cette vidéo sur les réseaux sociaux ou sur un site, voici son url :


Sujets que vous pourriez aussi aimer :


Numérotation automatique de Factures

Dans de précédentes formations, nous avons appris à bâtir une facturation automatisée avec Excel. La fonction RechercheV nous a permis d'extraire dynamiquement les informations du catalogue, en fonction des références articles. Puis avec un code Visual Basic, nous avons intégré la gestion de stocks et l'automatisation par interface graphique.



Pour peaufiner l'application professionnelle, nous souhaitons ajouter un système de numérotation automatique. Chaque nouvelle facture sera un incrément de la précédente facturation validée.

Facturation Excel
Comme toujours, pour concentrer nos travaux sur l'essentiel, nous récupérons le modèle complet de facturation Excel.

• Télécharger le classeur numerotation-automatique.xlsm en cliquant sur son lien,
• L'ouvrir dans Excel et cliquer sur le bouton Activer le contenu si nécessaire,

Grâce à une ligne de code VBA Excel dans la procédure événementielle Workbook_Open, l'interface graphique s'affiche automatiquement à l'ouverture du classeur. Notez à ce titre, l'extension xlsm du classeur pour la gestion du code Visual Basic.

Si vous choisissez une référence à l'aide de la liste déroulante en indiquant une quantité et que vous cliquez sur le bouton Ajouter, l'article est rapatrié à la suite sur la facturation. Des formules se chargent de calculer les montants hors taxes. Les informations des produits sont extraites de la feuille articles, par une fonction RechercheV qui récupère la référence à rechercher, transmise par le code VBA Excel. Si vous cliquez sur le bouton Valider la facture, les stocks du catalogue sont mis à jour en fonction des quantités achetées.

Et comme la facture a été validée, la numérotation de la prochaine devra être incrémentée. Dans notre exemple, la référence srt-106 devra remplacer la référence srt-105.

Accès aux données externes en VBA Excel
Pour des raisons de sécurité, nous proposons d'externaliser l'archivage de la numérotation. Un fichier texte fera l'affaire. A chaque ouverture de la facturation, un code VBA doit accéder à ce fichier pour récupérer son contenu et l'inscrire dans la cellule de numérotation. A chaque fois qu'une facture est validée, par clic sur le bouton de l'interface, le numéro en cours incrémenté doit être inscrit dans ce fichier texte, à la place de l'ancien.

• Créer le fichier numerotation.txt dans le dossier où a été téléchargé le classeur Excel,
• L'ouvrir, y écrire le numéro 106 puis le fermer en l'enregistrant,
• Sur la facturation Excel, cliquer sur la croix du UserForm pour fermer l'interface,
• Réaliser le raccourci clavier ALT + F11 pour basculer dans l'éditeur de code VBA Excel,
• Dans l'explorateur de projets sur la gauche, double cliquer sur l'élément facture,

Nous affichons ainsi l'interface graphique(Userform) de la facturation en mode conception. Si l'explorateur de projets n'est pas visible dans votre environnement, vous devez cliquer sur le menu Affichage en haut de l'éditeur. Vous choisissez alors Explorateur de projets dans la liste.

• Double cliquer sur le bouton Valider la facture depuis le UserForm,

Nous basculons ainsi dans la feuille de code VBA et plus précisément entre les bornes de la procédure valider_Click. Le code présent entre ces bornes se déclenche au clic sur le bouton. L'incrémentation du numéro de facture ne doit intervenir que si cette dernière est validée par l'utilisateur. Et cet événement intervient au clic sur le bouton Oui à la demande de confirmation par MsgBox. La partie du code concerné se situe donc entre les bornes de l'instruction conditionnelle If.

If (reponse = 6) Then
...
End If

C'est dans cette branche que nous devons ajouter les instructions permettant la numérotation automatique. Mais pour améliorer la structure, nous choisissons de créer une procédure indépendante que nous nommerons numerotation. Si la facture est validée par le vendeur, le code de cette procédure doit donc être appelé pour être exécuté.

• Dans l'instruction If, juste avant le End If, ajouter l'appel suivant :

numerotation

Certes nous avons anticipé puisque cette procédure n'existe pas encore. Si bien que si nous exécutions le code à ce stade, une erreur serait retournée. Nous devons créer cette procédure indépendante. Et pour ce faire, les modules sont particulièrement adaptés.

• Enregistrer les modifications (CTRL + S),
• Cliquer sur le menu Insertion en haut de l'éditeur de code,
• Dans la liste choisir Module,

Le Module1 apparait dans l'arborescence de l'explorateur de projets sur la gauche.

• Double cliquer sur le Module1 pour afficher sa feuille de code au centre,
• Dans sa feuille de code, créer la procédure numerotation, comme suit :

Sub numerotation()

End Sub

Le code que nous saisirons entre ces bornes s'exécutera donc, au clic sur le bouton Valider la facture du UserForm, si l'utilisateur confirme le message de validation. L'objectif est d'inscrire le numéro incrémenté dans le fichier numerotation.txt. Ce numéro est inscrit en cellule G2 : srt-105. Le préfixe est toujours le même : srt-. Le numéro à extraire est donc 105. Il doit être incrémenté (106) avant d'être exporté. Nous avons donc besoin d'une variable pour stocker ce numéro. Une variable est de même nécessaire afin de mémoriser le chemin d'accès au fichier texte.

• Entre les bornes de la procédure, ajouter les déclarations de variables suivantes :

Dim chemin_fichier As String: Dim numero As Integer

Nous déclarons fort logiquement la variable chemin_fichier comme un String, afin de pouvoir stocker une chaîne de caractères. La variable numero quant à elle est déclarée comme un entier (Integer) afin de pouvoir être incrémentée.

Après la déclaration des variables vient l'affectation. La propriété Path de l'objet ThisWorkbook renvoie le chemin d'accès complet au classeur actif (ThisWorkbook). Moyennant un antislash, il faut ajouter à ce chemin par concaténation, le nom du fichier texte pour qu'il soit complet : ThisWorkbook.Path & "\numerotation.txt". Pour stocker le numéro à incrémenter dans la variable numero, il faut d'abord supprimer le préfixe. La fonction VBA Replace permet de remplacer une occurrence dans une chaîne de caractères, par une autre. Sa syntaxe est la suivante :

Replace(chaine, terme_a_remplacer, terme_de_remplacement)

La chaîne correspond au contenu de la cellule G2 que l'on peut désigner avec l'objet Range (Range("G2")). La propriété Value d'un objet Range permet d'accéder au contenu de la cellule. Le terme à remplacer est srt- . Le terme de remplacement est un chaîne vide (""), pour supprimer ce préfixe.

• En conséquence de ces remarques, ajouter les affectations suivantes :

numero = Int(Replace(Range("G2").Value, "srt-", "")) + 1
chemin_fichier = ThisWorkbook.Path & "\numerotation.txt"

MsgBox numero & "-" & chemin_fichier

La fonction Int permet de convertir explicitement en entier, la chaîne récupérée et retravaillée grâce à la fonction Replace. A cet entier, nous ajoutons une unité (+ 1) pour incrémenter la numérotation. Nous ajoutons à ce code un test temporaire avec la fonction MsgBox pour nous assurer que nous avons bien stocké les bonnes valeurs.

• Enregistrer les modifications et exécuter le code (F5),

Comme l'illustre la capture ci-dessus, le numéro est extrait et incrémenté. De plus le chemin d'accès complet au fichier texte est parfaitement recomposé. Il s'agit donc désormais d'accéder à ce fichier en écriture pour y inscrire ce numéro. La formation VBA Excel pour importer et exporter des données, nous avait appris à coder l'accès séquentiel aux fichiers. Nous allons nous inspirer de ces enseignements.

• Ajouter une apostrophe (') devant la ligne du MsgBox pour la passer en commentaire,
• Puis, ajouter les instructions suivantes à la suite :

Open chemin_fichier For Output As #1
Print #1, Replace(numero, " ", "")
Close #1

L'instruction Open suivi du chemin du fichier permet d'accéder à ce dernier en mémoire. For Output permet d'indiquer que nous y accédons en écriture et non en lecture. Si un contenu existe, il est écrasé. Enfin,As #1, permet d'allouer une adresse mémoire sous forme de numéro. Ce numéro doit être libre. Pas de souci dans notre cas puisqu'il s'agit du seul accès mémoire réalisé. C'est par ce numéro désormais que nous désignons le fichier pour y écrire. Ainsi, l'instruction Print permet d'écrire dans le fichier référencé par son adresse (#1), l'information qui lui est passée en paramètre. Cette information n'est autre que le numéro incrémenté. Notez que nous le retravaillons à la volée afin de purger les espaces résiduels potentiels (Replace(numero," ", "")). Une fois l'écriture terminée, le fichier doit être déchargé de la mémoire grâce à l'instruction Close suivi de l'adresse #1 qui le désigne. C'est en libérant les ressources que l'écriture se fait de la mémoire vers le disque dur, dans le fichier désigné. Il s'agit maintenant de vérifier si cette inscription en dur s'effectue bien.

• Enregistrer les modifications et exécuter le code (F5),
• Ouvrir le fichier numerotation.txt pour consulter son contenu,

Comme l'illustre la capture ci-dessus, le numéro incrémenté est parfaitement inscrit.

Modifier les attributs de fichiers externes
Nous souhaitons ajouter une sécurité sur le fichier de la numérotation automatique, afin qu'il ne risque pas d'être modifié ou supprimé par mégarde. Nous proposons d'accéder à ses propriétés pour modifier certains attributs. Nous souhaitons que le fichier soit caché et en lecture seule. La formation VBA Excel pour accéder aux propriétés des fichiers, nous a appris les instructions permettant de modifier ces attributs.

• Dans la partie déclarative de la procédure, ajouter les deux déclarations suivantes :

Dim objet_fichier: Dim le_fichier

Il s'agit de deux variables non typées. En effet elles vont servir à instancier une classe ActiveX pour hériter des propriétés et méthodes permettant de manipuler les fichiers du système. Et c'est la fonction VBA CreateObject qui rend possible cette instanciation.

• Après l'affectation de la variable chemin_fichier, ajouter les deux affectations suivantes :

Set objet_fichier = CreateObject("scripting.filesystemobject")
Set le_fichier = objet_fichier.getfile(chemin_fichier)

Le mot clé Set est obligatoire pour instancier une classe. C'est le paramètre scripting.filesystemobject, passé à la fonction CreateObject qui permet d'instancier la classe pour manipuler les fichiers et les dossiers. Cet objet propose alors une méthode getfile qui permet de pointer sur le fichier qui lui est passé en paramètre (chemin_fichier). Nous affectons la variable le_fichier de cet objet retourné désignant le fichier précisément. Dès lors l'objet le_fichier propose la méthode Attributes qui peut être réglée sur différentes valeurs numériques afin d'affecter les propriétés du fichier en question.

Comme nous l'enseigne l'aide en ligne de Microsoft, la valeur 1 place le fichier en lecture seule tandis que la valeur 2 le transforme en fichier caché. Et ces constantes peuvent se combiner. Ainsi la valeur 3 (2 + 1) affectée à la propriété Attributes, transforme le fichier en fichier caché et en lecture seule.

• A la fin de la procédure, après le Close #1 et avant le End Sub, ajouter l'affectation suivante :

le_fichier.Attributes = 3

• Enregistrer les modifications et tester le code (F5),

Si nous accédons au dossier du fichier de numérotation et que nous affichons la colonne Attributs, nous remarquons la présence des indications RH. R est la première lettre de ReadOnly pour lecture seule tandis que H est la première lettre de Hidden pour caché. Bien sûr, vous devez indiquer à l'explorateur Windows d'afficher les fichiers masqués si vous souhaitez le visualiser.



Si nous tentons de modifier les informations du fichier, Windows ne nous y autorise pas. C'est parfait, le fichier est correctement protégé grâce à la modification de ses attributs par le code VBA. Mais désormais un problème se pose. Comme le fichier est protégé contre l'écriture, le code VBA lui-même n'est plus autorisé à y écrire. C'est ce qu'illustre la capture ci-dessous.



Cela signifie que le code Visual Basic Excel doit d'abord ôter la protection du fichier pour pouvoir y écrire. Une fois l'écriture terminée, il doit réactiver cette protection. Il suffit de régler la propriété Attributes à 0 pour éliminer toutes ces options.

• Avant l'instruction Open de l'accès en écriture, ajouter l'affectation suivante :

le_fichier.Attributes = 0

Désormais si nous exécutons le code, non seulement VBA peut modifier les informations du fichier, mais l'utilisateur lui, n'a pas le droit d'intervenir. Le code VBA complet de la procédure numerotation est la suivant :

Sub numerotation()
Dim chemin_fichier As String: Dim numero As Integer
Dim objet_fichier: Dim le_fichier

numero = Int(Replace(Range("G2").Value, "srt-","")) + 1
chemin_fichier = ThisWorkbook.Path & "\numerotation.txt"
Set objet_fichier = CreateObject("scripting.filesystemobject")
Set le_fichier = objet_fichier.getfile(chemin_fichier)
le_fichier.Attributes = 0

Open chemin_fichier For Output As #1
Print #1, Replace(numero, " ", "")
Close #1

le_fichier.Attributes = 3
End Sub

Accès en lecture aux fichiers externes
L'application n'est pas totalement terminée. A l'ouverture de la facture, un code VBA doit récupérer le dernier numéro incrémenté pour l'inscrire en G2. Sans cette étape, la numérotation restera toujours figée sur le même nombre. L'accès en lecture se fait toujours avec l'instruction Open mais accompagnée de la méthode For Input. Ce code doit se déclencher au moment de l'ouverture du classeur.

• Dans l'explorateur de projets, double cliquer sur l'objet ThisWorkbook,

Dans les bornes de la procédure événementielle Workbook_Open, nous trouvons fort logiquement l'instruction : facture.Show. La méthode Show de l'objet UserForm nommé facture, commande son affichage. Et comme cette ligne se déclenche sur l'événement de l'ouverture du classeur, l'interface s'affiche en même temps. Nous devons ajouter les lignes de code VBA permettant d'accéder au contenu du fichier numerotation.txt en lecture.

• Au début de la procédure, avant facture.Show, ajouter les lignes suivantes :

Dim chemin_fichier As String: Dim numero As String
chemin_fichier = ThisWorkbook.Path & "\numerotation.txt"

Il s'agit d'une réplication du code précédent. Nous stockons le chemin complet d'accès au fichier, dans la variable chemin_fichier, déclarée comme un String à cet effet. Bien sûr la variable numero doit permettre de stocker l'information récupérée du fichier.

• Toujours avant l'instruction facture.Show, ajouter les instructions suivantes :

Open chemin_fichier For Input As #1
Line Input #1, numero
Close #1

Range("G2").Value = "srt-" & numero

Nous accédons donc au fichier texte en lecture (For Input). Comme précédemment, cette lecture s'effectue en mémoire, raison pour laquelle nous définissons un numéro d'allocation (#1). L'instruction Line Input permet de récupérer les informations ligne à ligne, soit la première ligne ici. Elle est accompagnée de l'adresse mémoire pour désigner le fichier à lire. L'information récupérée (La numérotation) est stockée dans la variable passée en paramètre (numero). Nous libérons ensuite les ressources en déchargeant le fichier de la mémoire (Close #1). Et nous reconstruisons le code de la facturation incrémenté par concaténation en cellule G2 (Range("G2").Value= "srt-" & numero).

• Enregistrer les modifications et tester le code (F5),

Comme l'illustre la capture ci-dessus, l'ancienne numérotation a bien été remplacée par la nouvelle, issue du fichier texte qui avait mémorisé le nombre incrémenté, suite à la validation de la facturation. Si nous validons une nouvelle facture, nous constatons qu'à l'ouverture suivante, le numéro de facturation a bien été incrémenté.

Le processus est parfaitement fonctionnel. Pour parfaire l'application, il s'agirait de nettoyer la facture après validation pour la réinitialiser et de récupérer dans la foulée le nouveau numéro. Ainsi, plusieurs factures pourraient être émises dans successivement.

Le code VBA complet à l'ouverture du classeur est le suivant :

Private Sub Workbook_Open()
Dim chemin_fichier As String: Dim numero As String

chemin_fichier = ThisWorkbook.Path & "\numerotation.txt"

Open chemin_fichier For Input As #1
Line Input #1, numero
Close #1

Range("G2").Value = "srt-" & numero
facture.Show
End Sub