Gérer les factures archivées 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 :


Gestionnaire de factures clients

Dans cette formation VBA Excel, nous aboutissons l'application de gestion et de facturation des clients. Grâce aux développements précédents, nous pouvons construire des factures, mettre à jour les stocks, archiver les commandes et éditer ces factures au format PDF. Ici, l'enjeu consiste à livrer une console capable de restituer toutes les factures avec les informations attachées, sur demande précise.



Source et problématique
Ce développement requiert de réceptionner les précédents travaux.

• Télécharger le fichier gerer-factures-clients.rar en cliquant sur ce lien,
• Puis, le décompresser dans le dossier de votre choix,

La décompression fournit le classeur accompagné d'un sous dossier nommé archives. Nous y retrouvons les factures construites automatiquement par notre application de gestion et de facturation des clients.

• Double cliquer sur le classeur pour l'ouvrir dans Excel,
• Cliquer ensuite sur le bouton Activer la modification du bandeau de sécurité,

Nous débouchons sur la première feuille de ce classeur. Elle est nommée Gestion. Elle livre la console qu'il s'agit de développer. L'utilisateur doit être capable de retrouver les factures émises en fonction d'un identifiant client, du nom du client ou encore d'une date de commande.



Pour des résultats affinés, les trois critères peuvent être recoupés ou seulement deux d'entre eux. Mais il est aussi possible de ne fournir que l'une des trois indications. Le code VBA à attacher au bouton Extraire, doit s'adapter au contexte.

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

Nous y retrouvons toutes les commandes passées par les clients. Elles sont archivées avec un niveau de détail défini sur 6 champs.



Certes, à ce stade, elles ne sont pas encore très nombreuses. Vous pouvez en créer de nouvelles à votre guise grâce à l'interface de la feuille Facture. Il s'agit de tous les développements que nous avons aboutis lors des formations précédentes. Néanmoins, ce volume de commandes fera l'affaire pour les besoins de ce développement et des simulations.

Initialisation de l'extraction
Comme toujours, il est nécessaire de déclarer premièrement les variables utiles au traitement.

• En bas de la fenêtre Excel, cliquer sur l'onglet Gestion pour revenir sur sa feuille,
• 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 Excel, double cliquer sur le bouton Extraire,

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

• Entre les bornes de cette procédure, ajouter les déclarations et affectations suivantes :

...
Dim lid As Integer: Dim le_nom As String: Dim la_date As String
Dim ligne_bd As Long: Dim ligne_ext As Integer: Dim trouve As Boolean
Dim feuille As Worksheet: Dim nom_fichier As String

Set feuille = Sheets("Archives")
lid = Range("C6").Value: le_nom = Range("E6").Value: la_date = Range("G6").Value
ligne_bd = 3: ligne_ext = 10

purger
...

Nous déclarons tout d'abord les trois variables nécessaires pour réceptionner les trois potentiels critères. Ils correspondent aux cellules C6, E6 et G6. C'est pourquoi, ces variables sont ensuite initialisées sur les valeurs de ces cases. La variable ligne_bd doit permettre de parcourir toutes les archives. C'est pourquoi elle est ensuite affectée sur l'indice de la première d'entre elles (3). La variable ligne_ext doit permettre de restituer les archives concordantes, les unes en-dessous des autres, sur la feuille gestion. Cette énumération doit débuter à partir de la ligne 10. C'est la raison de son affectation par la suite. Nous déclarons aussi le booléen trouve. Son état renseignera sur le succès ou l'échec de la recherche. C'est lui qui permettra d'identifier les archives à restituer. Ensuite, nous déclarons la variable feuille que nous typons comme un objet Worksheet. Nous l'affectons dès lors de manière à ce qu'il représente la feuille Archives (Sheets("Archives")). Cette technique est destinée à alléger la syntaxe des expressions à suivre. Enfin, nous déclarons la variable nom_fichier. Elle permettra de désigner la facture archivée sur le disque dur, afin de pouvoir l'atteindre par lien hypertexte.

Dans l'enchaînement, nous appelons une procédure qui n'existe pas encore (purger). Chaque nouvelle recherche doit débuter par le nettoyage de la précédente extraction.

Certes, nous sommes loin d'avoir terminé le codage de la procédure événementielle. Mais comme la procédure purger est appelée, elle doit exister. Nous reprendrons ensuite le fil du développement.

• Sous la procédure événementielle, créer la procédure purger comme suit :

Private Sub purger()
Dim ligne_ext As Integer

ligne_ext = 10

Do While Range("B" & ligne_ext).Value <> ""
Range("B" & ligne_ext & ":" & "J" & ligne_ext).Value = ""
ligne_ext = ligne_ext + 1
If (ligne_ext > 1000) Then Exit Do
Loop

End Sub

Nous déclarons la variable ligne_ext que nous initialisons sur la première ligne des extractions à produire. Grâce à elle, nous enclenchons une boucle destinée à parcourir toutes les lignes, tant qu'une cellule non vide est détectée (Range("B" & ligne_ext).Value <> ""). Tant que ce critère est vrai, nous supprimons le contenu pour l'intégralité de la ligne. Bien sûr, nous n'oublions pas d'incrémenter la variable de boucle à chaque passage.

Extraction multicritère
Il est temps de poursuivre le développement de la procédure événementielle Extraire_Click. L'enjeu consiste à parcourir toutes les archives à la recherche d'une correspondance. Mais cette correspondance peut s'avérer complexe à dénicher. Pour des raisons d'ergonomie, le choix du ou des critères est laissé libre à l'utilisateur. Il peut définir une seule condition, deux parmi les trois ou encore les trois à recouper pour une extraction affinée. La variable booléenne trouve va s'avérer précieuse pour la résolution de cette problématique. En effet ici, nous ne pouvons pas raisonner avec des requêtes SQL comme nous le ferions avec un gestionnaire de base de données.

• A la suite du code de la procédure Extraire_Click, après l'appel de la procédure purger, construire la boucle suivante :

...
Do While feuille.Range("B" & ligne_bd).Value <> ""
trouve = True

Loop
...

Nous engageons donc un traitement récursif destiné à parcourir toutes les lignes de la feuille Archives, tant qu'une cellule non vide est effectivement détectée. Et à chaque passage, nous réinitialisons la variable booléenne à True. Cela signifie que nous considérons par défaut que la correspondance est trouvée. Et c'est là que l'astuce réside. Nous allons raisonner par élimination. Un à un, nous allons vérifier la concordance des critères spécifiés. Il suffira que l'un d'entre eux ne soit pas satisfait pour basculer l'état de cette variable à False. Cette valeur indiquera explicitement au code VBA de ne pas importer la ligne de l'archive.

• Dans les bornes de la boucle, ajouter le premier test suivant :

...
If (lid <> 0) Then
If (feuille.Range("C" & ligne_bd).Value = lid) Then
trouve = True
Else
trouve = False
End If
End If
...

Nous commençons par rechercher la correspondance sur l'identifiant. Mais pour cela, nous nous assurons tout d'abord qu'il a été spécifié (lid <> 0). Dans le cas d'une cellule numérique non renseignée, Excel ne l'interprète pas comme vide ("") mais équivalente à zéro. C'est ainsi que nous n'altérons pas la valeur de la variable booléenne. Dans le cas où il est effectivement spécifié et que la correspondance est trouvée (feuille.Range("C" & ligne_bd).Value = lid), nous confirmons l'état du booléen. Ainsi, nous pouvons poursuivre l'analyse sur les autres potentiels critères, selon la même méthode. Dans le cas contraire en revanche, nous basculons sont état à False. D'ores et déjà donc, et avant même de poursuivre l'analyse, nous savons que l'archive en cours ne concorde pas. Nous devons donc vérifier la correspondance des deux autres potentiels critères de la même façon.

• A la suite du code, toujours dans les bornes de la boucle, ajouter les tests suivants :

...
If (trouve = True) Then
If (le_nom <> "") Then
If (feuille.Range("D" & ligne_bd).Value = le_nom) Then
trouve = True
Else
trouve = False
End If
End If
End If
If (trouve = True) Then
If (la_date <> "") Then
If (Left(feuille.Range("F" & ligne_bd).Value, 10) = la_date) Then
trouve = True
Else
trouve = False
End If
End If
End If
...

Le principe est identique dans les deux cas. Mais avant de poursuivre, nous nous assurons que la variable booléenne porte toujours la valeur indiquant de poursuivre l'analyse (True). Nous vérifions ensuite et tout d'abord que la condition a été émise par l'utilisateur (le_nom <> ""). Si tel est le cas, nous engageons le test de correspondance (feuille.Range("D" & ligne_bd).Value = le_nom). S'il réussit, nous conservons la variable booléenne enclenchée à True, pour poursuivre l'analyse avec le potentiel troisième critère. Dans le cas contraire, nous basculons son état à False. C'est ainsi que la boucle passe à l'archive suivante pour continuer la recherche.

A l'issue de ces tests, si la variable booléenne vaut toujours True, cela signifie que l'enregistrement de l'archive en cours d'analyse est concordant. Nous devons donc rapatrier ses informations sur la feuille Gestion.

• Toujours dans la boucle et à lasuite du code, ajouter les instructions VBA suivantes :

...
If (trouve = True) Then
Range("B" & ligne_ext).Value = feuille.Range("B" & ligne_bd).Value
Range("C" & ligne_ext).Value = feuille.Range("C" & ligne_bd).Value
Range("D" & ligne_ext).Value = " " 'Pour les bordures de la mef
Range("E" & ligne_ext).Value = feuille.Range("D" & ligne_bd).Value
Range("F" & ligne_ext).Value = feuille.Range("E" & ligne_bd).Value
Range("G" & ligne_ext).Value = feuille.Range("F" & ligne_bd).Value
Range("H" & ligne_ext).Value = feuille.Range("G" & ligne_bd).Value
nom_fichier = ThisWorkbook.Path & "\archives\" & Range("H" & ligne_ext).Value
Range("H" & ligne_ext).Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:=nom_fichier, TextToDisplay:=Range("H" & ligne_ext).Value

ligne_ext = ligne_ext + 1
End If

ligne_bd = ligne_bd + 1
...

A l'issue des tests par élimination, si la variable booléenne vaut toujours True (trouve = True), nous engageons le rapatriement des informations de l'archive correspondante. Ce traitement consiste en une simple correspondance entre les cellules de la feuille Gestion (Range("B" & ligne_ext).Value) et de la feuille Archives (feuille.Range("B" & ligne_bd).Value). Notez le saut réalisé en colonne D de la feuille Gestion. Pour des raisons de présentation, elle est conservée vide. Mais le code prend soin d'y inscrire un espace. Une mise en forme conditionnelle est effectivement posée sur ce tableau. Lorsqu'elle détecte un contenu, elle encadre la cellule.

Ensuite, nous stockons le chemin d'accès complet au fichier dans la variable nom_fichier. C'est la propriété Path de l'objet VBA ThisWorkbook qui retourne le chemin d'accès complet au dossier du classeur en cours. Nous concaténons ce chemin avec le sous dossier archives et le nom du fichier pour pointer sur la facture correspondante. Dès lors, après avoir sélectionné sa cellule, nous exploitons la méthode Add de l'attribut Hyperlinks de l'objet ActiveSheet pour créer un lien hypertexte. Nous lui passons l'adresse du fichier à ouvrir en deuxième paramètre et le texte à afficher dans la cellule, en troisième paramètre.

Nous n'oublions pas d'incrémenter la variable ligne_ext. Ainsi, la prochaine extraction potentielle peut être effectuée sur la ligne du dessous. Ensuite et dans tous les cas, nous incrémentons la variable de boucle ligne_bd, pour poursuivre l'analyse sur les autres archives.

• Enregistrer les modifications et basculer sur la feuille Gestion,
• Dans le ruban Développeur, cliquer sur le bouton Mode création pour le désactiver,
• En cellule G6, taper la date suivante : 09/07/2019 et cliquer sur le bouton Extraire,

Comme vous pouvez le voir, toutes les factures correspondantes sont automatiquement extraites avec leurs informations attachées. En l'occurrence ici, il s'agit de l'ensemble des factures. Pour les besoins des manipulations, nous les avions créées dans l'enchaînement, donc le même jour. En dernière colonne, un lien hypertexte permet d'afficher la facture archivée au format PDF.

En cliquant sur ces liens, une première alerte apparaît, puis une seconde. Il s'agit de règles de sécurité de Microsoft Office. La propriété VBA DisplayAlerts de l'objet Application ne permet pas de les neutraliser. Même si elles sont répétitives, il est conseillé de s'en accommoder. Pour les neutraliser, c'est une valeur d'une clé de la base de registre qu'il faut modifier. Mais cette modification est globale.

• Désormais en E6, taper le nom de client suivant : Audine,
• Puis, cliquer de nouveau sur le bouton Extraire,

La liste des résultats s'affine. Seules les commandes archivées pour le client et la date mentionnés sont importées. Notre console de gestion des factures client est donc parfaitement opérationnelle. Et c'est ainsi que s'achève notre application de gestion et de facturation des clients.