VBA- Extraction données+mise en forme

Sylvie · 3 Novembre 2005

Bonjour à tous,

Votre aide me serait précieuse pour compléter et mettre en forme (par macro) un fichier d’encaissements clients afin d’importer ce fichier modifié dans un logiciel de comptabilité.

La problématique est la suivante :
Je dispose chaque mois de deux fichiers excel (à l’origine il s’agit de formats txt)
- un premier fichier de facturation qui se présente sous la forme de 9 colonnes ( code journal, date facture, numéro de facture, colonne vierge, numéro de compte général, numéro de compte de tiers, libellé, montant débit, montant crédit). Dans ce fichier je vais devoir aller extraire le numéro de compte de tiers figurant colonne F en fonction du numéro de facture, critère commun à mes deux fichiers.
- un second fichier d’encaissements : 10 colonnes

Je souhaite donc par macro modifier le fichier 'encaissements'. Cette mise en forme devra aboutir à la présentation exposée dans le fichier Excel « matrice import encts ».

Pour s’affranchir des copier, coller, sous totaux et rerchercheV, que l’utilisateur effectue chaque mois « à la main » il faudrait donc à partir du fichier « encaissements octobre.xls »
- Effectuer une rupture par numéro de pièce (colonne I du fichier Encaissements) en insérant à chaque rupture de pièce, une ligne avec le total de la pièce en contrepartie Débit
- Aller extraire du fichier facturation, le numéro de compte de tiers qui figure colonne F, ceci en fonction du numéro de facture qui figure dans les deux fichiers (colonne C du fichier Export Facturation et colonne E du fichier encaissements du fichier Facturation). Au final ce numéro de compte de tiers sera inscrit colonne F du Fichier Matrice Import Encts en regard du compte général 41100000

Les colonnes du fichier Matrice Import Encts.xls devront être récupérées comme suit
Col A Code Journal : inscrire le code « ENC » (
Col B Date : reprendre la colonne G du fichier encaissements
Col C N° Piece : Reprendre le numéro de facture colonne E du fichier encaissements
Col D Référence : Reprendre le numéro de l’organisme colonne A du fichier encaissements
Col E : CG (num compte général) : pour chaque ligne client inscrire le compte 41100000 (8 positions au total)
Col F : CT (num compte tiers) : aller chercher le compte de tiers adéquat dans le fichier facturation (voir colonne F) en faisant une recherche sur le numéro de facture : inscrire ce numéro colonne F
(Si le numéro n’est pas trouvé, il faudra laisser la cellule vierge ou mieux, la mettre en couleur afin d’ attirer l’attention de l’utilisateur)
Col G Libellé : reprendre le libellé figurant colonne G du fichier encaissements
Colonne I (crédit) inscrire pour chaque ligne client le montant du règlement (colonne H du fichier encaissement)
Colonne H (débit) inscrire le total de chaque pièce en reprenant la date de règlement col B, rien col C et D, col E inscrire compte général 58200000 (8 positions) avec comme libellé « Virements Caisses ».

C’est bien le fichier « Matrice import encts.xls » qui doit être mis en forme à partir du fichier encaissements Octobre.xls (et complété du numéro de compte de tiers) pour une importation dans un progiciel comptable.

L’exemple donné devrait permettre de compléter le code client pour les factures réglées en Octobre et qui datent du mois de Septembre.
Par mesure de simplification, on conviendra que la macro sera exécutée les fichiers Excel étant ouverts.

D’avance , un immense merci à tous ceux qui vont plancher sur mon problème et y consacrer de leur temps.

Bonne fin de journée

Sylvie

[file name=encaissements.zip size=27817]http://www.excel-downloads.com/components/com_simpleboard/uploaded/files/encaissements.zip[/file]

Message édité par: Sylvie, à: 03/11/2005 16:11

Message édité par: Sylvie, à: 03/11/2005 20:18

myDearFriend! · 5 Novembre 2005

Bonjour Sylvie, le fil, le Forum,

Tout d'abord, pour récupérer les données de chacun des fichiers 'Encaissement...' ou 'Facturation...', j'ai déjà créé la fonction personnalisée suivante :

Private Function VerifClasseur(Nom As String) As String
Dim Cl As Workbook
      For Each Cl In Workbooks
            If Left(Cl.Name, Len(Nom)) = Nom Then
                  VerifClasseur = Cl.Name
                  Exit For
            End If
      Next Cl
      If VerifClasseur = '' Then MsgBox 'Fichier ''' & Nom & ''' introuvable !'
End Function

Le but étant déjà de vérifier si les fichiers sources sont effectivement ouverts... Comme on pouvait facilement imaginer que pour le fichier 'Encaissements Octobre.xls' par exemple, seul le mot 'Encaissement' devait être constant et que la suite dépendrait du mois de traitement, j'ai pensé qu'il serait opportun de faire cette vérification, non pas à l'aide du nom complet, mais avec seulement le début du nom de fichier ('Encaissement' dans l'exemple)...
Ainsi, on effectue l'appel de cette fonction par le code en lui passant comme argument une chaine de caractères représentant le début du nom de fichier souhaité. La fonction parcourt l'ensemble des classeurs ouverts (collection Workbooks) et vérifie chaque nom de fichier à la recherche de l'expression donnée. Si elle trouve une correspondance, elle retourne alors le nom complet à la procédure appelante, et dans le cas contraire, elle retourne une chaine vide en alertant l'utilisateur (MsgBox)...

Dans l'extrait de code ci-dessous, la procédure appelante est la procédure 'Traitement()'. J'appelle donc la fonction personnalisée avec le mot 'Encaissement' et le nom complet du classeur est retournée dans la variable 'Classeur'. Je récupère ensuite les données du dit fichier dans une variable temporaire (tableau variant) nommée 'TabEnc'.
J'obtiens ainsi un tableau variant à 2 dimensions (lignes et colonnes) calquant la structure du tableau source. Ainsi, dans la suite du traitement, si je souhaite obtenir par exemple la valeur de la cellule C10 du fichier 'Encaissement...', alors je demande la valeur TabEnc(10,3) pour 10ème ligne et 3ème colonne.
Je fais ensuite la même chose pour le tableau 'TabFact' correspondant au fichier 'Facturation...'.

Sub Traitement()
Dim TabEnc As Variant, TabFact As Variant
Dim Classeur As String
Dim L As Long
      ...
      'Charge les données dans 2 tableaux variant temporaires
      Classeur = VerifClasseur('Encaissement')
      If Classeur = '' Then Exit Sub
      With Workbooks(Classeur).Sheets(1)
            L = .Range('A65536').End(xlUp).Row
            TabEnc = .Range(.Cells(2, 1), .Cells(L, 10)).Value
      End With
      Classeur = VerifClasseur('Facturation')
      If Classeur = '' Then Exit Sub
      With Workbooks(Classeur).Sheets(1)
            L = .Range('A65536').End(xlUp).Row
            TabFact = .Range(.Cells(1, 1), .Cells(L, 9)).Value
      End With
      ...

Dans la suite du déroulement de la procédure, je me réfère donc aux tableaux variant TabEnc() et TabFact() pour récupérer les données souhaitées...

J'espère que ces quelques lignes t'auront permis de comprendre un peu mieux ce code... et j'espère également t'avoir donné envie de VBA .

Bon week-end.
Cordialement.

VBA- Extraction données+mise en forme

Sylvie

XLDnaute Accro

Pièces jointes

myDearFriend!

XLDnaute Barbatruc

Discussions similaires