Valider et éditer la facture PDF en VBA Excel

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


Sujets que vous pourriez aussi aimer :


Valider et éditer la facture du client

Dans cette formation VBA Excel, nous souhaitons aboutir le processus consistant à archiver et valider les commandes des clients. Précédemment, nous avons bâti le code permettant d'alimenter les commandes mais aussi de retirer des articles à la volée. Désormais, les éléments de la commande doivent être enregistrés pour des raisons de traçabilité notamment. Mais ne l'oublions pas, à chaque achat, les stocks doivent être actualisés en base de données. Enfin, la facture doit être livrée au format PDF et archivée elle aussi.

Source et problématique
Nous devons poursuivre les travaux aboutis jusqu'à ce stade.

• Télécharger le fichier valider-editer-facture.rar en cliquant sur ce lien,
• Décompresser le fichier et double cliquer sur le classeur pour l'ouvrir dans Excel,
• Puis, cliquer sur le bouton Activer la modification du bandeau de sécurité,

Nous l'évoquions, cette sécurité neutralise le code VBA destiné à s'enclencher à chaque ouverture.

• Fermer le classeur en l'enregistrant puis le rouvrir,
• En bas de la fenêtre Excel, cliquer sur l'onglet Facture pour activer sa feuille,

Désormais, la source est considérée comme fiable et nous pouvons débuter.



Comme vous le savez, la première liste déroulante permet de désigner un client par son identifiant. Dès lors, la seconde liste déroulante propose de spécifier tour à tour, les articles qu'il a achetés. L'opérateur doit renseigner la quantité achetée en cellule I7 et cliquer sur le bouton +Com. Et c'est ainsi que la commande se construit à partir de la ligne 11 de la feuille Facture.

En ligne 10, un bouton intitulé Valider, a été ajouté. Son code associé doit premièrement mettre à jour les stocks. Il doit ensuite archiver la commande et produire la facture au format PDF.

Appel des procédures de traitement
Comme plusieurs traitements doivent s'enchaîner, nous proposons de les dissocier dans des procédures indépendantes. La clarté et la structure du code s'en trouveront bonifiées. Ces appels doivent intervenir dans un ordre précis, au clic sur le bouton Valider.

• En haut de la fenêtre Excel, cliquer sur l'onglet Développeur pour activer son ruban,
• Dans la section Contrôles du ruban, cliquer sur le bouton Mode création,
• Dès lors, sur la feuille, double cliquer sur le bouton Valider,

Nous basculons ainsi dans l'éditeur de code VBA Excel, entre les bornes de la procédure événementielle Valider_Click. Son code doit se déclencher au clic sur le bouton Valider.

• Dans les bornes de la procédure événementielle, ajouter le code VBA suivant :

...
If (Range("B11").Value <> "") Then

maj_stocks
archiver
ligne = 11
couleur = False
nettoyer
ID.Value = ""
Ref.Value = ""
Range("I7").Value = ""
Sheets("Facture").Select

End If
...

Une vérification est tout d'abord établie sur la première cellule de la commande. Si cette dernière n'est pas renseignée, nous savons que la commande est vide. En conséquence, aucun traitement ne doit être enclenché. Dans le cas où elle est effectivement renseignée, nous appelons les procédures de traitement et réinitialisons les variables publiques.

Les procédures maj_stocks et archiver sont à construire. La procédure nettoyer existe déjà. Après validation de la commande, elle est destinée à purger la feuille Facture.

• Sous la procédure évènementielle, créer les procédures maj_stocks et archiver :

Private Sub maj_stocks()

End Sub

Private Sub archiver()

End Sub

Actualiser les quantités en stock
Lorsqu'une commande est validée, toutes les quantités achetées doivent être soustraites aux quantités en stock, pour les articles respectifs. Nous devons donc engager une double boucle destinée à parcourir en même temps les lignes de la commande et les lignes de la base de données. Lorsqu'une référence de la commande est trouvée dans la base de données, son stock doit aussitôt être mis à jour.

• Dans les bornes de la procédure maj_stocks, ajouter le code VBA suivant :

...
Dim continuer As Boolean: Dim la_qte As Integer
Dim la_ligne As Integer: Dim la_ref As String
Dim com_ligne As Byte

For com_ligne = 11 To ligne
continuer = True: la_ligne = 3
la_ref = Range("B" & com_ligne).Value
la_qte = Range("I" & com_ligne).Value

While continuer = True

Wend

Next com_ligne
...

Nous débutons bien sûr par la déclaration des variables nécessaires. La variable continuer, typée comme un booléen, doit servir de test pour la boucle destinée à parcourir les articles de la base de données. Les variables la_qte et la_ref doivent prélever les informations de l'article, fournies sur chaque ligne de la commande. Elles permettront de connaître la référence à trouver et la quantité à soustraire au stock. La variable com_ligne doit être utilisée pour passer en revue toutes les lignes de la commande. Et la variable la_ligne doit être utilisée pour passer en revue toutes les lignes de la base de données, à la recherche d'une correspondance.

C'est pourquoi, nous engageons la première boucle sur les articles de la commande. Elle initie sa trajectoire à partir de la première ligne, définie à l'indice 11. Elle poursuit son traitement récursif jusqu'à atteindre la valeur, mémorisée dans la variable publique ligne. Souvenez-vous, c'est elle qui détient l'indice de ligne du dernier article acheté.

Pour chaque article ainsi passé en revue dans la commande, nous commençons par réinitialiser la valeur des variables continuer et la_ligne. En effet, pour chaque article de la commande, la comparaison doit être réalisée sur toute la base de données, en repartant de la première ligne (3). Et tant que la référence n'est pas trouvée, la variable booléenne doit indiquer de poursuivre la recherche.

Pour l'article en cours, nous prélevons donc la référence et la quantité dans les variables déclarées à cet effet. Puis, nous engageons une nouvelle boucle (While) destinée à parcourir la base de données de la feuille Catalogue, à la recherche de cette référence.

Dans cette boucle imbriquée, dès que la référence est trouvée, nous devons actualiser le stock.

• Dans les bornes de la boucle While, ajouter les instructions suivantes :

...
If (Sheets("Catalogue").Cells(la_ligne, 2).Value = la_ref) Then
Sheets("Catalogue").Cells(la_ligne, 7).Value = Sheets("Catalogue").Cells(la_ligne, 7).Value - la_qte
continuer = False
End If

la_ligne = la_ligne + 1
If (la_ligne > 1000) Then continuer = False
...

Pour chaque article de la commande, nous vérifions que la référence en cours d'analyse, correspond dans la base de données. Si l'égalité est avérée, nous mettons à jour son stock en colonne 7. Puis, nous n'oublions pas de basculer la variable continuer à False. Ainsi, l'analyse de la base de données s'interrompt. De fait, la main est repassée à la première boucle pour prélever les informations de l'article suivant sur la commande. Et donc, une nouvelle analyse de la base de données débute à la recherche de la nouvelle correspondance. A chaque passage dans la boucle While, nous n'oublions pas d'incrémenter l'indice de ligne. Puis, nous ajoutons une sécurité pour mettre fin au traitement récursif lorsque le nombre de passages est jugé trop important.

• Basculer sur la feuille Facture et enregistrer le classeur,
• Le fermer puis le rouvrir,

Nous souhaitons en effet réaliser une simulation en conditions réelles. Souvenez-vous, les variables publiques sont toutes initialisées à l'ouverture du classeur.

• Choisir un identifiant client avec la liste déroulante du premier cadre,
• Choisir un article avec la liste déroulante du second cadre,
• Taper une quantité achetée en I7 et cliquer sur le bouton +Com,
• De la même façon, ajouter un ou deux articles supplémentaires,
• Puis, cliquer sur le bouton Valider,

Tout d'abord et comme vous pouvez le voir, la commande est totalement purgée. Et bien entendu, tel que nous les avons programmées, les variables publiques sont réinitialisées. De fait, l'interface est prête à accueillir la construction d'une nouvelle facture. De plus, si vous choisissez l'une des références précédemment validées dans le second cadre, vous notez que l'information rapatriée sur le stock a effectivement été décrémentée. Cela va de soi, le constat est le même en affichant la base de données de la feuille Catalogue.

La première étape est donc accomplie avec succès. Le code VBA complet de la procédure maj_stocks est le suivant :

Private Sub maj_stocks()
Dim continuer As Boolean: Dim la_qte As Integer
Dim la_ligne As Integer: Dim la_ref As String
Dim com_ligne As Byte

For com_ligne = 11 To ligne
continuer = True: la_ligne = 3
la_ref = Range("B" & com_ligne).Value
la_qte = Range("I" & com_ligne).Value

While continuer = True
If (Sheets("Catalogue").Cells(la_ligne, 2).Value = la_ref) Then
Sheets("Catalogue").Cells(la_ligne, 7).Value = Sheets("Catalogue").Cells(la_ligne, 7).Value - la_qte
continuer = False
End If

la_ligne = la_ligne + 1
If (la_ligne > 1000) Then continuer = False
Wend

Next com_ligne
End Sub

Archiver la commande du client
Nous proposons de stocker toutes les commandes dans une autre feuille du même classeur.

• En bas de la fenêtre Excel, cliquer sur l'onglet Archives pour afficher sa feuille,

Nous y trouvons les en-têtes d'un tableau vide pour l'instant. C'est ici que nous devons archiver les commandes des clients, ligne à ligne. Sont attendues les informations sur le numéro de commande, l'identifiant client, le nom du client, le montant de la commande, la date et le nom du fichier archivant la facture PDF. A l'instar d'une clé primaire de base de données, nous souhaitons que le numéro de commande soit auto-incrémenté, en partant arbitrairement de la valeur 1000.

• Revenir dans l'éditeur de code VBA Excel,
• Entre les bornes de la procédure archiver, ajouter les déclarations et initialisations suivantes :

...
Dim la_ligne As Long: Dim nom_fichier As String
Dim lid As Integer: Dim num_com As Long

la_ligne = 2: num_com = 1000: lid = ID.Value

Do While Sheets("Archives").Cells(la_ligne, 2).Value <> ""

Loop
...

C'est habituel désormais, nous déclarons la variable permettant de parcourir toutes les lignes (la_ligne) de la feuille Archives. Son objectif est de trouver la première rangée vide pour procéder à la nouvelle inscription, à la suite des autres. Elle est fort logiquement initialisée à l'indice 2, soit la ligne de départ du tableau. La variable nom_fichier devra stocker le nom de la facture PDF à enregistrer sur le disque dur. C'est pourquoi nous la déclarons comme un String. La variable lid est prévue pour stocker l'identifiant client qu'il s'agit d'inscrire dans le tableau des archives. Nous l'affectons d'ailleurs au contenu de la liste déroulante (ID.Value). La variable num_com doit créer le nouveau numéro de commande. Il s'agit d'incrémenter le dernier numéro trouvé. Nous l'initialisons à 1000 pour créer le premier numéro en cas d'archives vides.

Ensuite, nous enclenchons une boucle sur la feuille Archives, plus précisément sur la colonne 2, celle du numéro de commande.

Dans cette boucle, nous devons poursuivre l'analyse du tableau, ligne à ligne, tant qu'un numéro de commande est trouvé. Il convient d'incrémenter le dernier trouvé, soit le plus grand.

• Entre les bornes de la boucle, ajouter le code VBA suivant :

...
If (IsNumeric(Sheets("Archives").Cells(la_ligne, 2).Value)) Then num_com = Sheets("Archives").Cells(la_ligne, 2).Value + 1
la_ligne = la_ligne + 1

If (la_ligne > 10000) Then Exit Do
...

A chaque passage, grâce à la fonction VBA IsNumeric, nous testons que la valeur en cours d'analyse est bien un nombre. Ainsi, dès que la première cellule vide sera détectée, nous saurons que le bout du tableau d'archives est atteint. Lorsque le test est concluant, nous incrémentons ce numéro que nous stockons dans la variable num_com. Ainsi, à l'issue du traitement, c'est bien le dernier numéro qui aura été incrémenté. Bien sûr, nous n'oublions pas d'incrémenter la variable de boucle pour poursuivre l'analyse sur les lignes suivantes. Et comme souvent, nous ajoutons un garde-fou destiné à interrompre le code VBA si le nombre de passages est jugé trop important.

Une fois le traitement de la boucle terminé, nous connaissons donc le nouveau numéro de commande à écrire. Comme nous connaissons toutes les autres informations, nous devons procéder à leurs inscriptions.

• Sous la boucle, donc après le Loop, ajouter le code VBA suivant :

...
nom_fichier = lid & "-" & num_com & ".pdf"

Sheets("Archives").Cells(la_ligne, 2).Value = num_com
Sheets("Archives").Cells(la_ligne, 3).Value = lid
Sheets("Archives").Cells(la_ligne, 4).Value = Range("B7").Value
Sheets("Archives").Cells(la_ligne, 5).Value = Range("J" & ligne + 1).Value
Sheets("Archives").Cells(la_ligne, 6).Value = Now
Sheets("Archives").Cells(la_ligne, 7).Value = nom_fichier

faire_facture num_com, nom_fichier
...

Tout d'abord, pour le nom du fichier, nous concaténons l'identifiant client avec le numéro de commande. Le tout est logiquement suffixé de l'extension Pdf. Avec cette construction, nous nous assurons de créer des noms de fichiers uniques. Dans le même temps, nous nous assurons de pouvoir récolter toutes les commandes attachées à un client. A l'avenir, nous bâtirons en effet cette console de gestion. Ensuite, nous inscrivons toutes les données dans les colonnes respectives pour la ligne détectée (la_ligne). Comme vous le savez, le montant total de la commande est récupéré à l'indice publique incrémenté d'une unité (Range("J"& ligne + 1)).

Enfin, nous passons la main à une nouvelle procédure qui n'existe pas encore. Elle se nomme faire_facture. Comme son nom l'indique, elle doit se charger de faire la mise en page de la facture, de l'éditer au format Pdf et de l'archiver sur le disque dur, dans un sous dossier local de l'application. Elle requière deux paramètres : Le numéro de commande et le nom du fichier.

Pour réaliser une nouvelle simulation, nous devons premièrement construire cette procédure.

• Sous la procédure archiver, créer la procédure faire_facture, comme suit :

Private Sub faire_facture(num_com As Long, nom_fichier As String)

End Sub

Nous la déclarons donc avec les deux paramètres attendus.

• Basculer sur la feuille Facture et enregistrer les modifications,
• Choisir un identifiant client,
• Puis, ajouter des articles à la commande avec des quantités associées,
• Cliquer alors sur le bouton Valider pour archiver la commande,

Comme précédemment, nous constatons que la commande est complètement réinitialisée. De même, une simple vérification sur les références, confirme que les stocks ont parfaitement été actualisés.

• En bas de la fenêtre Excel, cliquer sur l'onglet Archives pour activer sa feuille,

Comme vous pouvez le voir, les informations de la commande ont parfaitement été archivées. En l'absence de commandes précédentes, le numéro a été initialisé à 1000, comme nous l'avons codé. Le montant total a été importé. La date du jour a été greffée et le nom du fichier a été construit. C'est ce dernier point que nous devons désormais aboutir, soit la construction et la mise en page de la facture.

Construire et éditer la facture en PDF
Pour bâtir cette facture, sous le nom de fichier défini, nous proposons tout d'abord de la reconstruire sur une feuille servant d'étalon.

• En bas de la fenêtre Excel, cliquer sur l'onglet Reflet pour activer sa feuille,

Les informations du client doivent être intégrées dans le cadre en haut à gauche de la commande. Il est placé entre les colonnes H et J. Le numéro et la date de la commande doivent être inscrits en cellules respectives D7 et J7. Le détail de la commande doit être énuméré à partir de la cellule B11.

Avant de nous atteler au code VBA, quelques réglages de mise en page sont nécessaires. Ils seront ainsi définis pour toutes les factures à éditer. Il s'agit de modifier l'orientation du papier mais aussi de centrer le rendu sur la largeur de la page. Nous définirons la zone d'impression par le code VBA.

• En haut de la fenêtre Excel, cliquer sur l'onglet Mise en page pour activer son ruban,
• Dans la section Mise en page du ruban, cliquer sur le bouton Orientation,
• Puis, choisir l'option Paysage dans la liste qui se propose,
• Cliquer ensuite sur le petit bouton d'options en bas à droite de la section Mise en page,
• Dans la boîte de dialogue qui apparaît, activer l'onglet Marges,
• Puis, dans la section Centrer sur la page, cocher la case Horizontalement,

• Cliquer sur le bouton Ok de la boîte de dialogue pour valider ces réglages de mise en page,
• Basculer dans l'éditeur de code Visual Basic Excel,
• Entre les bornes de la procédure faire_facture, ajouter les lignes VBA suivantes :

...
Sheets("Reflet").Range("H2:H4").Value = ""
Sheets("Reflet").Range("B11:T1000").Clear

Range("B11:J" & ligne + 2).Copy
Sheets("Reflet").Activate
Sheets("Reflet").Range("B11").Activate
ActiveSheet.Paste
...

Nous purgeons tout d'abord le contenu du bloc d'adresse du client (H2:H4), sans altérer la mise en forme. Puis, en prévoyant large (B11:T1000), nous supprimons la mise en forme et le contenu des lignes de la commande répliquée.

Ensuite, nous copions l'intégralité de la commande sur la feuille Facture (Range("B11:J" & ligne + 2).Copy). Puis, nous la collons sur la feuille Reflet à partir de la cellule B11.

Il s'agit maintenant de répliquer les attributs de format pour une mise en forme cohérente. Une macro automatique Excel fournit ce code. Et nous proposons de le prélever pour le répliquer.

• A la racine du dossier de décompression, double cliquer sur le fichier code-source.txt,
• Copier la première portion de code VBA intitulée Collage spécial,
• Puis, la coller à la suite du code de la procédure faire_facture,

...
'Collage spécial
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Sheets("Reflet").Range("A1").Select
Application.CutCopyMode = False
...

La commande a donc été répliquée avec sa mise en forme. Nous devons désormais inscrire les informations du client et de la commande, dans les cellules prévues à cet effet.

• A la suite du code de la procédure, ajouter les instructions VBA suivantes :

...
Sheets("Reflet").Range("H2").Value = Range("B7").Value & " " & Range("D7").Value
Sheets("Reflet").Range("H3").Value = Range("D5").Value & " " & Range("E5").Value
Sheets("Reflet").Range("D7").Value = num_com
Sheets("Reflet").Range("J7").Value = Now
...

Nous prélevons les informations du client, issues de la feuille Facture. Certaines peuvent apparaître tronquées du fait des réglages que nous avions entrepris. Il peut donc sembler préférable de réaliser une nouvelle recherche des informations selon l'identifiant, dans la feuille Clients. Mais ici, nous concentrons nos travaux sur la construction de la facture. Grâce à la variable num_com transmise en paramètre, nous inscrivons le numéro de commande dans la cellule prévue à cet effet (D7). Et puis, nous exploitons la fonction VBA Now pour inscrire la date de la commande.

Pour parachever la conception, il convient de définir une zone d'impression bornée sur les limites de la commande. Le rendu sera ainsi parfaitement calibré pour la sortie PDF. Et cette exportation doit être conclue dans l'enchaînement. Dans les deux cas, c'est encore une macro VBA qui livre le code spécifique.

• Dans le fichier code-source.txt, copier le deuxième bloc de code (Editer en PDF),
• Coller ces instructions à la suite dans la procédure faire_facture,

...
'Editer en PDF
ActiveSheet.PageSetup.PrintArea = "B2:J" & ligne + 2
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
ThisWorkbook.Path & "\archives\" & nom_fichier _
, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
:=False, OpenAfterPublish:=True
...

Certes, nous avons adapté quelques arguments. Nous calons tout d'abord la zone d'impression précisément sur les bornes de la facture ("B2:J" & ligne + 2). Nous exportons le rendu ainsi délimité au format PDF, dans le sous dossier archives du dossier local (ThisWorkbook.Path). Cet enregistrement est réalisé sous le nom défini par la procédure archiver et passé en paramètre (nom_fichier).

• Enregistrer les modifications et créer le sous dossier archives dans le dossier du classeur,
• Revenir ensuite sur la feuille Facture,
• Désigner un client et construire une commande,
• Enfin, cliquer sur le bouton Valider,

Le rendu est aussitôt livré au format PDF, parfaitement calibré et mis en page. Si vous ouvrez le sous dossier archives, vous constatez que la facture est bien enregistrée. Si vous affichez la feuille Archives, vous notez la présence de la nouvelle commande avec son numéro auto-incrémenté. Si vous consultez les stocks, vous confirmez leur actualisation. Bref, nous avons abouti une application de gestion et de facturation des clients . Néanmoins, dans une dernière étape, nous bâtirons une console permettant de manipuler ces factures archivées.

Le code VBA complet de la procédure faire_facture, est le suivant :

Private Sub faire_facture(num_com As Long, nom_fichier As String)

Sheets("Reflet").Range("H2:H4").Value = ""
Sheets("Reflet").Range("B11:T1000").Clear

Range("B11:J" & ligne + 2).Copy
Sheets("Reflet").Activate
Sheets("Reflet").Range("B11").Activate
ActiveSheet.Paste
'Collage spécial
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Sheets("Reflet").Range("A1").Select
Application.CutCopyMode = False

Sheets("Reflet").Range("H2").Value = Range("B7").Value & " " & Range("D7").Value
Sheets("Reflet").Range("H3").Value = Range("D5").Value & " " & Range("E5").Value
Sheets("Reflet").Range("D7").Value = num_com
Sheets("Reflet").Range("J7").Value = Now

'Editer en PDF
ActiveSheet.PageSetup.PrintArea = "B2:J" & ligne + 2
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
ThisWorkbook.Path & "\archives\" & nom_fichier _
, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
:=False, OpenAfterPublish:=True

End Sub