VBA- Extraction données+mise en forme

[Sylvie]

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

 

[Abel]

Bonjour Sylvie,

Ouf !

Bon, j'ai décroché en cours de lecture.....

Je regarderai ce soir (je suppose que je ne serai pas le seul) si je ne dors pas trop debout. Hi hi !

A+

Abel.

Edition :
Rien à voir avec le sujet.
Hi hi ! Egalité au compteur ... Il est pret ton 1000 ? Arf ! ...

Message édité par: Abel, à: 03/11/2005 18:13

 

[soft]

Bonsoir Sylvie, Abel, le forum,

Ca fait beaucoup d'un coup et je ne suis pas certain d'avoir absolument tout pigé, mais ça devrait en tout cas donner une bonne base.

Je n'ai remis que le fichier MATRICE.., les 2 autres n'étant pas modifiés.
[file name=MATRICE.zip size=16653]http://www.excel-downloads.com/components/com_simpleboard/uploaded/files/MATRICE.zip[/file]

 

[myDearFriend!]

Bonsoir Sylvie, Abel, soft,

Ci-joint ma proposition... à vérifier.

Sub Traitement()
Dim TabEnc As Variant, TabFact As Variant
Dim Classeur As String
Dim L As Long, L2 As Long, NL As Long
Dim NumPiece As Integer
Dim Cumul As Single
      '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(2, 1), .Cells(L, 9)).Value
      End With
      Application.ScreenUpdating = False
      'MAJ de la Matrice d'Import
      NumPiece = TabEnc(1, 9)
      NL = 4
      With ThisWorkbook.Sheets(1)
            .Range('A5:I65536').ClearContents
            .Range('A5:I65536').Interior.ColorIndex = xlNone
            'Pour chaque ligne d'Encaissement
            For L = 1 To UBound(TabEnc, 1)
                  'Sous-Total Débit
                  If TabEnc(L, 9) <> NumPiece Then
                        NL = NL + 1
                        .Cells(NL, 2).Value = .Cells(NL - 1, 2).Value
                        .Cells(NL, 5).Value = '58200000'
                        .Cells(NL, 7).Value = 'Virements Caisses'
                        .Cells(NL, 8).Value = Cumul
                        Cumul = 0
                        NumPiece = TabEnc(L, 9)
                  End If
                  'MAJ lignes
                  NL = NL + 1
                  .Cells(NL, 1).Value = 'ENC'
                  .Cells(NL, 2).Value = TabEnc(L, 7)
                  .Cells(NL, 3).Value = TabEnc(L, 5)
                  .Cells(NL, 4).Value = TabEnc(L, 1)
                  .Cells(NL, 5).Value = '41100000'
                  For L2 = 1 To UBound(TabFact, 1)      'Code Tiers et Libellé
                        If TabFact(L2, 3) = TabEnc(L, 5) And TabFact(L2, 6) <> '' Then
                              .Cells(NL, 6).Value = TabFact(L2, 6)
                              .Cells(NL, 7).Value = TabFact(L2, 7)
                              Exit For
                        End If
                  Next L2
                  If .Cells(NL, 6).Value = '' Then .Cells(NL, 6).Interior.ColorIndex = 34
                  'Crédit
                  .Cells(NL, 9).Value = TabEnc(L, 8)
                  Cumul = Cumul + TabEnc(L, 8)
            Next L
            'Sous-Total Débit
            NL = NL + 1
            .Cells(NL, 5).Value = '58200000'
            .Cells(NL, 7).Value = 'Virements Caisses'
            .Cells(NL, 8).Value = Cumul
      End With
      Application.ScreenUpdating = False
End Sub

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

Bise à toi Sylvie et salutations à Abel et soft. [file name=mDF_MATRICE.zip size=12443]http://www.excel-downloads.com/components/com_simpleboard/uploaded/files/mDF_MATRICE.zip[/file]

Message édité par: myDearFriend!, à: 03/11/2005 20:44

 

[Sylvie]

Bonsoir Abel, Bonsoir Soft, le forum

tout d'abord merci à toi Abel d'avoir tenté de t'accrocher malgré la longueur de mon laius. J'ai du en faire fuir plus d'un !

Soft, je viens de regarder 'ton oeuvre' et ton boulot est vraiment super. Je t'assure que c'est une très très bonne base.
Je vais donc te re-préciser deux ou trois points pour te permettre de transformer ton essai :
- tu inseres bien une ligne à chaque rupture de pièce. ceci c'est Ok
Sur le ligne où tu inseres le compte 58200000 (8 positions) il convient de faire le total de la pièce (ex total de la piece n°296 , puis total de la piece 302).
Il s'agit du numero de piece du fichier encaissements octobre.xls colonne I de sorte que pour chaque pièce l'egalité suivante Débit = Credit soit toujours respectée.
Si cela te pose un problème que ce numéro de pièce n'apparaisse pas dans le fichier Matrice, rajoute eventuellement une colonne en J (je le tronquerais lors de l'import) ou bien rajoute le si tu peux dans le libellé ex 'VIREMENTS CAISSES PIECE 296'
- peux tu egalement compléter sur chaque ligne où figure le compte 5820000 le code journal ENC et la date de la pièce (col I fichier encaissements) (ex 3/10 puis 6/10 etc) (à une pièce correspond bien une seule date)
- pour la reprise du compte de tiers, je pense que tu t'es trompé de colonne car le compte à récuperer est colonne F du fichier facturation.

Encore merci car tu as parfaitement décrypté mes desiderata.
Alors si tu as encore du courage et du temps à consacrer à mon problème, j'en serais ravie

Bonne soirée

 

[soft]

Rebonjour le fil,
Sylvie,
Pour ce soir plus vraiment possible, je regarde demain en fin de journée si personne n'a pris le relais.

Bonne soirée.

 

[Sylvie]

Bonsoir MyDear Friend, Soft , Abel, Le forum

alors que je repondais à Soft, Didier tu as surgi tel Zorro, prenant au passage le baton du relais tendu par Soft .
Que dire si ce n'est d'abord 'merci' et ensuite que c'est 'parfait' !
Sacré travail ! Je referais ton guide sois en certain !

Je vais oser te demander deux petits trucs de plus :
- serait il possible de tout de meme conserver le libellé colonne G meme si le numero de tiers n'a pas été trouvé ?
- il semblerait qu'il y ait un petit souci d'arrondi qui va donc faire capoter l'import du fichier tel quel mais je ne vois pas d'où cela peut venir. Pour la piece 296, il s'affiche 48876,63671875 alors que cela devrait etre 48876,65 tout rond idem pour la pièce suivante.
Je ne vois pas d'où viennent ces decimales supplémentaires car je pense que toutes les sommes sont à deux decimales dans le fichier encaissements.

Mille merci Didier car ta proposition est vraiment à la hauteur de mes grandes espérances.


Bises
Sylvie

ps : je n'ai toujours pas trouvé pourquoi un hippopotame trainait le 24 septembre dernier dans la cour de l'ex Maison des Avocats à Lyon. Mais sois tranquille, je continue mon enquête.(heureusement que vous êtes plus rapides à nous aider à résoudre nos problèmes !!)

 

[Abel]

Re Sylvie, bonjour Soft et Didier,

Bon, ben voilà. Arf !

J'aurai été loin d'une telle solution.

A retenir.

Abel.

 

[myDearFriend!]

Re,

Sylvie, après tous tes efforts de Septembre, je suis trop content de pouvoir te rendre service à mon tour !

Tu trouveras ci-joint une tentative pour répondre à tes attentes...

Je pense que le problème de l'arrondi est maintenant résolu. Pour faire mes tests et pour pouvoir être sûr que tout allait bien à ce niveau, j'ai du supprimer les formats de cellules prédéterminés dans ta matrice de réception, si toutefois il te faut impérativement des montants à 2 décimales, n'hésite pas à m'en informer.

Par ailleurs, pour le libellé colonne G, j'ai apporté une modification. Cela dit, compte tenu des exemples de données sources fournis et de mon approximative compréhension du problème, il m'est difficile de voir si la modification est efficace ou non. Dis-moi si toutefois ce n'est pas réglé...

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

 

[Sylvie]

Bonjour Didier, Soft, Abel, Le forum



Merci Didier, pour ton super boulot. Le problème des arrondis est parfaitement résolu.
Ton oeuvre a provoqué bien des applaudissements ce matin au boulot !

Si cela est possible je souhaiterais que tu puisses apporter les modifications suivantes :
1) sur la ligne que tu insères (compte 58200000) peux tu compléter la col A avec 'ENC' ?

2) Concernant le libélle qui figure dans la colonne G du fichier matrice, tu reprends pour le moment celui du fichier facturation. Pourrais tu plutôt reprendre celui du fichier Encaissements ? et Cerise sur le gateau peux tu ajouter avant le libéllé (donc à gauche) le numéro de pièce trouvé dans la colonne I du fichier encaissement (il s'agit plus exactement d'un numéro de bordereau) suivi d'un tiret et du libellé
Ainsi en face de l'encaissement de la facture 305 on aurait
41100000 9CLIENTS 296- RON BERNARD

Si tu le peux rajoute également ce même numéro avant le libellé VIREMENTS CAISSES ex '296 - VIREMENTS CAISSES'.

Pour le format à deux décimales, ne te fais pas de souci; je ferais avant l'import un petit coup de format cellules à deux décimales et l'affaire sera jouée.

Merci encore Didier pour cette brillante démonstration et pour ton investissement.
(Soft, rassure toi je n'oublie pas que tu as tenté de m'aider aussi)

Bises

 

[soft]

Bonsoir Sylvie, Abel et 'Mon Cher ami',

Pas besoin de me rassurer, c'est moi qui le suit de voir que Didier est là et assure.

Bonne soirée.

 

[ChTi160]

Salut Sylvie
Bonsoir Didier
Bonsoir le Fil
bonsoir le Forum

je ne sais pas si Didier a déjà répondu à ta dernière demande

mais je me suis permis de modifier le Fichier de mon maître j'espère que celà correspond à ton attente, sinon Didier fera le nécessaire Lol
Voir le fichier post en dessous lol
bonne fin de Soirée et bon Week End [file
Ps Voilà ce que j'ai modifié pour formater la rubrique 'Sous-Total Débit

 NL = NL + 1
          .Cells(NL, 1).Value = 'ENC'
          .Cells(NL, 2).Value = TabEnc(UBound(TabEnc, 1), 7)
          .Cells(NL, 5).Value = '58200000'
          .Cells(NL, 7).Value = TabEnc(UBound(TabEnc, 1), 9) & '-' & 'Virements Caisses'
          .Cells(NL, 8).Value2 = Cumul

Arffffffffff je remets le fichier j'ai m---é Lol

Message édité par: Chti160, à: 04/11/2005 23:05

 

[ChTi160]

arfff

je remets le fichier que j'ai effacé au dessus lol [file name=mDF_MATRICE_IMPORT_ENCTSV2_20051104230420.zip size=13494]http://www.excel-downloads.com/components/com_simpleboard/uploaded/files/mDF_MATRICE_IMPORT_ENCTSV2_20051104230420.zip[/file]

 

[myDearFriend!]

Bonsoir Sylvie, Abel, soft, Jean-Marie (ChTi160),

En fait, les modifs sont faites depuis 21H00 ce soir, mais j'attendais une confirmation de Sylvie sur une question que je lui ai posé par messagerie privée et concernant la présence ou non d'espaces avant et après le tiret pour l'ajout du numéro de bordereau... Comme je n'ai pas de nouvelles (pas grave), je joins quand même cette nouvelle version...

J'ai à peu près effectué les mêmes modifs que toi Jean-Marie.
J'en avais toutefois profité pour réviser un peu le code et modifier une ou deux choses. J'ai également rétabli les montants avec 2 chiffres après la virgule ce qui évitera une intervention manuelle ultérieure (pourquoi faire manuellement ce qu'on peut faire en 2 lignes de code ?)

Cordialement, [file name=mDF_MATRICE3.zip size=15798]http://www.excel-downloads.com/components/com_simpleboard/uploaded/files/mDF_MATRICE3.zip[/file]

 

[Sylvie]

Bonjour Didier, Soft, Abel, Chti160 (coucou Jean Marie), le forum


Mille merci Didier !
Ton travail est en tous points parfait et répond exactement à ce que j'avais imaginé et souhaité.

J'ai lu ton code et bien sur je n'ai pas tout compris; mais en tout cas, je prends du plaisir à le lire : il est beau et fluide ! (Ca me donnerait presque des envies de VBA si je m'écoutais )

Pour ma gouverne personnelle, quelle instruction permet de repérer le fichier encaissements du fichier facturation ? je ne vois rien de tel (ou du moins rien de visible à mes yeux) .
Est ce que concretement il existe une étape intermédiaire de compilation des deux tableaux encaissements et facturation (ce que tu appelles les tableaux variants temporaires) ?

Je vais aller faire fonctionner ta macro pas à pas, peut être verrais je mieux ce qui se passe à l'insu de mon plein gré !

C'est une bien belle réalisation Monsieur My Dear Friend et je t'en remercie tres chaleureusement.

Je vous souhaite à tous un excellent week end.

Bien Cordialement
Sylvie