VBFacturation : Le code le + simple possible en VBA pour gérer vos factures

www.vbfrance.com/code.aspx?ID=50477

Documentation : VBFacturation.html

Par Patrice Dargenton

http://patrice.dargenton.free.fr/CodesSources/index.html

Date : 23/08/2009

Voici le code le plus simple possible en VBA pour gérer vos factures : l'idée est d'illustrer quelques astuces techniques pour éditer facilement des factures avec peu de code. J'ai préféré faire certaines simplifications dans la base de données de façon à ce que cette source soit réutilisable en pratique : vous pouvez ensuite modifier ce code pour ajouter les fonctionnalités que vous avez besoin, mais il est probable que ce soit du développement spécifique non réutilisable par d'autres dans ce cas.

Il y a une base Access pour les données et une autre pour l'application elle-même, ce qui vous permettra de diffuser des mises à jour sans perdre les données de votre client (sauf pour la mise à jour de structure des données : c'est plus complexe). Pour s'affranchir des problèmes de chemins, l'application doit être lancée dans C:\VBFacturation (je n'utilise plus le répertoire "C:\Program Files" car ses droits d'accès sont plus compliqués avec Vista).

Chaque facture est imprimée depuis un classeur Excel connecté à la base de données avec le même chemin : il suffit d'autoriser l'actualisation des données lors de l'ouverture pour imprimer la facture. La facture peut imprimée en Pdf via l'utilitaire CutePdf (voir ici pour l'installation).

Enfin il est possible d'éditer un bon de livraison, et de faire des statistiques de ventes dans un tableau croisé dynamique sur les critères associés à chaque vente, en récupérant de la même façon la totalité des commandes cette fois.

 

Mots clés : Facture, Facturation, Bon de livraison, Statistiques de ventes, VBA, Tableur, Feuille de calcul, Excel, Pdf.

 

 

Tables des matières

Mode d'emploi 1

Astuces techniques. 2

Limitations. 2

Problématique. 3

Divers. 3

Procédure pour refaire les liens sur la base de données depuis une feuille Excel 3

Faire automatiquement des sauvegardes. 3

Imprimer en Pdf 3

Gestion des macros sous Access 2007. 3

Comment distribuer une base Access par courriel ?. 3

 

 

Mode d'emploi

 

1°) Sélectionner un client ou bien créer un nouveau client ;

2°) Sélectionner une commande ou bien créer une nouvelle commande dans le sous formulaire Commandes du client ;

3°) Saisir une quantité du produit 1 et éventuellement une quantité du produit 2 : lorsque l'on presse Entrée, les champs Montant HT 1 et 2 sont mis à jour ;

4°) Facultatif : On peut saisir la TVA si le pays de vente n'est pas la France (TVA autre que 19.6% par défaut) ;

5°) Facultatif : Si on saisie la remise en %, on en déduit le montant final hors taxes ; et si on saisie le prix unitaire hors taxes, on en déduit la remise en % et le montant final hors taxes ;

6°) Cliquer sur le bouton Facturer permet de sélectionner cette facture, de façon à ce que les informations de cette facture soient transmises lors de l'ouverture du fichier VBFacturation.xls : la facture peut alors être imprimée depuis Excel. Note : on n'applique la TVA sur la facture seulement si le pays du client est la France ; un bon de livraison peut aussi être imprimé ;

7°) Analyse des statistiques de ventes : en renseignant les critères de 1 à 8, il est ensuite possible de déterminer quels critères sont favorables pour les ventes, ceci en analysant leurs corrélations dans un tableau croisé dynamique Excel : Stat.xls. Si vos ventes dépassent 65000 commandes (bravo pour votre succès commercial !), il faut baser le tableau croisé dynamique directement sur la base de données Access, sans passer par une feuille Excel.

 

 

Astuces techniques

 

Ces astuces sont en fait très basiques, c'est simplement le faire de les combiner ensemble qui permet de conserver une application simple mais complète :

- Séparation de l'application en deux bases distinctes : une pour les données et une pour l'application, de façon à pouvoir faire des mises à jour de l'application facilement, sans écraser les données de votre client (mais pour la mise à jour de structure des données, c'est plus complexe : vous devrez récupérer au préalable les données de votre client) ;

- Champ calculé (MontantHT1 et 2) dans une requête qui est la source du formulaire frmCommande (dans Access 2010, les champs calculés pourront être intégrés au niveau de la table cette fois) ;

- Formulaires Maître/Détail pour avoir les commandes (formulaire détail : frmCommande) du client (formulaire principal VBFacturation basée sur la table Client) : le lien entre les deux formulaires est le champ IdClient ;

- Contrôle indépendant (IdClientSelect : Nom) dans le formulaire principal VBFacturation pour pouvoir rechercher un client existant ou bien en créer un autre (on laisse quand même le contrôle lié Nom pour pouvoir corriger une orthographe). Note : il faudra probablement ajouter un index unique dans la table Client (Nom + Prénom + Ville ou bien Courriel, ou bien un autre index) ;

- Contrôle indépendant (PrixUnitaireHT1) dans le formulaire frmCommande pour pouvoir saisir aussi directement le "Prix HT remise déduite" et d'en déduire la remise correspondante : pour cela il faut traiter l'événement PrixUnitaireHT1_AfterUpdate ;

- Champ booléen bSelection pour sélectionner un et un seul enregistrement parmi les commandes : pour cela on efface ce champ pour toutes les commandes ("UPDATE Commande SET Commande.bSelection = False"), puis on fixe à vrai ce champ pour la facture en cours ("UPDATE Commande SET Commande.bSelection = True Where NumeroCmd=" & Me.NumeroCmd) ;

- Feuille Excel liée à une requête de l'application MS Access : le fait de conserver le chemin absolu (C:\VBFacturation\VBFacturation.mdb) facilite la distribution de l'application ;

- En cochant "Actualiser à l'ouverture du fichier" dans les "Propriétés de la plage de données externes", il ne reste plus qu'à confirmer cette actualisation lors de l'ouverture de VBFacturation.xls pour que l'application soit opérationnelle. Pour conserver les factures émises, on peut les imprimer en pdf, et conserver la version pdf. On peut aussi conserver la version Excel, mais il faut enregistrer le fichier VBFacturation.xls sous un autre nom pour éviter d'écraser le modèle Excel de facturation : pour cela, les deux fichiers modèles ont l'attribut "lecture seule" pour éviter une fausse manipulation. On pourrait se passer d'Excel en utilisant à la place un état Access, mais les états Access fonctionnement différemment : les connaissances de mise en page sous Excel ne serviront peu ou pas sous Access.

 

 

Limitations

 

- Les prix de chaque produit sont indiqués dans la valeur par défaut du champ PrixProduit1 ou PrixProduit2 de la table Commande (la référence et la désignation de l'article : produit 1 ou 2 est codé en dur dans le fichier Excel) : on pourrait faire une table des articles (avec la référence, la désignation et prix de chaque article), mais on commence le développement spécifique dans ce cas. Si vous voulez toujours faire du développement générique, alors bienvenue dans le monde du développement des PGI !

- Lorsqu'on ajoute un champ qui doit être utilisé dans les tableaux Excel, cela peut décaler l'ordre des colonnes : cela peut alors être fastidieux de corriger les modèles Excel. Une solution pourrait être de nommer dans l'ordre chaque champ de la requête source, plutôt que d'utiliser Select * ;

- Pour modifier la facture sous Excel, il faut d'abord déplacer les zones arrondies, puis les remettre en place après la modification, ce n'est pas très pratique.

 

 

Problématique

 

Access est bien pour la conception rapide d'une base de donnée, mais programmer en DotNet est mieux qu'en VBA pour écrire du code source (c'est plus productif à moyen et à long terme), cependant DotNet n'est pas franchement convainquant pour remplacer un formulaire Access (je n'ai toujours pas trouver comment faire de façon aussi efficace que sous Access l'équivalent d'un formulaire, surtout en mode utilisateurs multiples connectés simultanément à une même base de données partagée : d'après ce que j'en ai vu, c'est obligatoirement complexe et fastidieux en DotNet/SQL serveur) : il faut donc bien réfléchir avant d'ajouter des fonctionnalités si celles-ci doivent être codées en VBA Access ou bien en DotNet dans une application Externe.

 

 

Divers

 

Procédure pour refaire les liens sur la base de données depuis une feuille Excel

Sous Excel (version < 2007) : Créer une nouvelle feuille : Menu Données : Données externes : Créer une requête... : Base de données : MS Access Databases* : OK : Sélectionner la base de données : C:\VBFacturation\VBFacturation.mdb : OK : RqStat ou bien RqFacturation : Bouton > : Suivant : Suivant : Suivant : Terminé : OK.

 

Faire automatiquement des sauvegardes

Pour faire automatiquement des sauvegardes, vous pouvez utiliser AccessBackup, voici la syntaxe :

"C:\Program Files\AccessBackup\AccessBackup.exe" CheminSrc C:\VBFacturation\VBFacturation_Donnees.mdb

 

Imprimer en Pdf

Télécharger GhostScript ici : http://sourceforge.net/projects/ghostscript/ (gs870w32.exe) et installer-le.

Puis télécharger et installer CutePdf : il suffit ensuite d'imprimer sur l'imprimante virtuelle "CutePDF Writer" pour produire un fichier pdf depuis n'importe quel logiciel.

 

Gestion des macros sous Access 2007

Pour modifier les options de sécurité, le menu est dans "Centre de gestion de la confidentialité" dans les "Options Access" : Paramètres du centre... (le mieux est d'ajouter le répertoire C:\VBFacturation comme source fiable de macro).

 

Comment distribuer une base Access par courriel ?

Les fichiers .mdb dans les pièces jointes de courriel sont éliminés par défaut (dans MS Exchange) : le courriel ne sera jamais reçu, considéré à tord comme du spam : une solution consiste à renommer l'extension avant de zipper toute l'application (par exemple renommer VBFacturation.mdb en VBFacturation._mdb_). Il peut arriver aussi que vous arriviez à transmettre l'application, mais aucun bouton ne fonctionne : Windows désactive les macros associées à l'application (lorsqu'elle a été téléchargée d'une manière ou d'une autre), c'est-à-dire tout le code VBA pour faire fonctionner les boutons ! Dans ce cas aussi il faut renommer l'extension, puis la rétablir à la réception. Envoyer toujours un courriel sans pièce jointe au préalable pour prévenir de l'envoie d'une pièce jointe dans le courriel suivant : un administrateur peut souvent récupérer un courriel dans sa boite de spam.

Cette solution sert aussi pour envoyer des exécutables. Dézipper toujours un zip contenant des exécutables : n'ouvrer jamais un document depuis un zip si vous avez un doute sur son expéditeur : le contenu affiché d'un zip peut différer de son contenu réel (vous pensez ouvrir un document mais en fait vous lancer un exécutable).

 

Note : cette source est en grande partie récupérée et adaptée depuis un développement rapide dans mon environnement professionnel.