Générer des courriers types word à partir d'excel

loulouange · 7 Juin 2013

Bonsoir,

Je me permets de faire appel à vos compétences en Excel afin de savoir s'il est possible de créer dans un tableau excel comprenant une liste de personnes avec coordonnées, un bouton ou autre permettant d'ouvrir un document spécifique dans Word reprenant des éléments dans le tableau excel.

Etant donné que se sont des lettres personnalisées, il n'y pas de possibilité de faire un publipostage.

Jusqu'ici je n'ai pu qu'améliorer les lettres types en appliquant des champs, (je n'y connaissais rien avant).

Je sais qu'Excel propose de grandes possibilités mais je crains que ce que je demande nécessite une très bonne maîtrise d'Excel.

Ceci dit, si vous pensez que ma demande est réalisable, et s'il existe un tuto pas à pas ou si vous pouviez m'expliquer au moins pour un exemple, je pourrais tenter de comprendre le fonctionnement des macros.

Pour vous permettre de mieux cerner ma demande, je vous mets en pièce jointe un tableau et deux exemples de lettres types, une pour absence injustifiée (AI) et l'autre pour absence justifiée (AJ).

Le tableau est exhaustif, d'autres lettres types et documents existent, le but étant de pouvoir en cliquant sur un bouton (dans chaque colonne sur chaque ligne ??) de générer le document et de reprendre l'adresse du destinataire.

Je vous remercie par avance pour vos suggestions et conseils qui me seront bien précieux.

Yaloo · 26 Septembre 2013

Re : Générer des courriers types word à partir d'excel

Bonjour Loulou,

C'est en forgeant que l'on devient forgeron .... ça va venir, il faut persister.

Généralement, les If, With, For etc... sont comme des parenthèses, lorsque tu en ouvres une, il faut la refermer :
If ....End If , For (ou For Each) .... Next, With ....End With

La seule exception, si je ne me trompe pas, est lorsque tu écris If ta condition Then ton action tout sur la même ligne, dans ce cas-là tu n'es pas obliger d'utiliser End If.

Donc, pour :

VB:

Private Sub Worksheet_Activate()
 Dim i&, j&, c&, SemS&, Plage, SemB&
 Application.DisplayAlerts = 0: Application.EnableEvents = 0
 End With
 Application.Goto Cells(1, Application.Match(Sheets("Menu Déroulant").[H3] - 1, Plage, 0)), -1
 Application.DisplayAlerts = -1: Application.EnableEvents = -1
 End Sub

Il suffit d'enlever

End With

tu peux aussi en profiter pour enlever les déclarations de variables n'existant plus, puisque tu n'as plus ces variables dans la macro, et enlever également les lignes correspond à l'affichage des messages et d'évènements, donc ta macro devient :

VB:

Private Sub Worksheet_Activate()
Dim Plage
Set Plage = Rows(3)
Application.Goto Cells(1, Application.Match(Sheets("Menu Déroulant").[H3] - 1, Plage, 0)), -1
End Sub

Si l'on souhaite encore simplifier, on peut intégrer la plage directement dans la ligne de code, et faire :

VB:

Private Sub Worksheet_Activate()
Application.Goto Cells(1, Application.Match(Sheets("Menu Déroulant").[H3] - 1, Rows(3), 0)), -1
End Sub

Concernant le tri, c'est normal, puisque en début de macro, tu supprimes toutes les données de la feuille 2, puis tu remets toutes les lignes, donc tu ne rajoutes pas ta ligne à la fin mais tu recrées toutes les lignes une par une. D'où un décalage possible de ces lignes nouvellement créées par rapport aux X. Je pense toujours que la meilleur solution reste avec la Base.

Pour voir ce qui se passe avec chaque ligne de la macro, tu peux mettre un point d'arrêt sur n'importe quelle ligne de code (en faisant F9) puis lorsque tu lances ta macro, celle-ci s'arrêtera au point d'arrêt, tu peux ensuite faire du pas à pas (en faisant F8, pour descendre ligne par ligne.

En espérant avoir répondu à tes questions.

Bonne après-midi

Martial

loulouange · 2 Octobre 2013

Re : Générer des courriers types word à partir d'excel

Bonjour Martial,

J'ai lu avec attention toutes tes explications, j'essaye de comprendre mais ce n'est pas encore bien clair dans ma tête mais bon je ne désespère pas.

J'ai essayé ta technique avec la touche F9 pour arrêter la macro à certaines lignes mais hélas ça ne m'a pas beaucoup aidé, car soit ça bloquait tout soit ça aller jusqu'au bout et je n'ai pas réussi à voir à quel moment la ligne se copiait en fin de tableau et à quel moment le tri se faisait.

Je viens de voir que dans le dernier fichier, celui avec la colonne ID (là où j'ai modifié une partie de la macro dans WORKSHEET ACTIVATE :

les lignes qui se trouvaient dans la feuilles "liste des participants" étaient recopiées dans la feuille "Affectation" dès que le numéro ID était attribué. DE plus il semblerait que dans cette feuille le tri se fasse par ordre de numéro et non alphabétique.
Or avant la ligne n'était recopiée que lorsque la colonne CT de la feuille "liste participants" était remplie et ensuite triée par ordre alphabétique suivant le NOM puis le Prénom. Est-il possible de le faire ainsi ?

J'ai essayé de modifier la macro mais c'est trop complexe pour moi désolée.

De même, pour la feuille "Suivi" les lignes étaient recopiées dès que l'on renseignait le numéro de semaine, comme finalement il ne faut rien effacer dans cette feuille au niveau des colonnes ateliers, j'ai donc comme je l'ai dit plus haut annulé la macro dans WORKSHEET ACTIVATE en mettant le code que tu m'avais donné.
J'aurais aimé que cela fasse comme dans la feuille "affectation", c'est à dire : que les personnes soient recopiées identiquement, juste avec CT, NOM et Prénom, et éviter le décalage avec les colonnes et lignes que l'on avait l'autre fois.

Je te renvoie ton fichier afin que tu puisses mieux cerner le problème.

En te remerciant pour ton aide précieuse.

Bonne journée.

Yaloo · 2 Octobre 2013

Re : Générer des courriers types word à partir d'excel

Bonjour Loulou,

Pour les feuilles "Liste de participants" et "Affectation" c'est bon dans le fichier ci-dessous.

Pour la feuille "Suivi", cela va poser le même problème que précédemment, si l'on ne recopie pas tout de la base (nom + x) vers cette feuille. Les x vont être décalés.

Soit il faut reprendre toutes les données soit rien du tout.

A te relire

Martial

Yaloo · 2 Octobre 2013

Re : Générer des courriers types word à partir d'excel

Re,

Où alors il faut envoyer toutes les données dans la base, y compris les x que tu places dans la feuille "Suivi".

Je vais y regarder.

A+

Yaloo · 2 Octobre 2013

Re : Générer des courriers types word à partir d'excel

Salut Loulou,

Pour finir, j'ai inclus l'envoi de chaque valeur de stage vers la Base.
Le conseiller remplit la feuille Affectation (en mettant la semaine dans la cellule souhaitée), celle-ci va aller mettre un x dans la base suivant la semaine, le jour et heure du stage.
Lorsque tu actives la feuille Suivi, toutes les données sont récupérées dans la Base, y compris les x. Lorsque tu modifies un stage dans la feuille suivi, cela modifie également dans la feuille Base.
Comme ça il n'y a plus de problème de tri avec ou sans les x.

A te relire

Martial

loulouange · 2 Octobre 2013

Re : Générer des courriers types word à partir d'excel

Super Martial pour ton fichier, merci d'avoir répondu si vite.

Je viens de voir que pour le tri alphabétique c'est ok, j'ai regardé dans la macro pour comprendre, mais dur dur, par contre j'ai l'impression que tout se fait à partir de la feuille "base", mais faut il commencer par cette feuille lorsque je rentre un nouveau stagiaire ?
ou alors est ce que le schéma est le suivant :
1 : je rentre le nouveau bénéficiaire dans la feuille générale "liste participants"
2 : la ligne va d'abord se copier dans la feuille "base"
3 : ensuite se répercute dans la feuille "affectation" en faisant le tri ?

Pour ce qui concerne les croix comme je te l'avais expliqué dans l'un de mes posts précédents, il n'y aura pas de numéro de semaines dans la feuille "affectations" car se sont en général toujours les mêmes personnes pour les mêmes ateliers (ils viennent toutes les semaines), donc le conseiller renseigne par une croix (c'est beaucoup plus simplement. A partir de là je peux vérifier qui devait assister à tel atelier.

J'ai bien l'impression que cette histoire de croix pose soucis, mais on pourrait faire plus simple :
Moi je voudrais juste que dans la feuille "affectation" et "suivi" soient recopiés uniquement les colonnes CT, NOM et Prénom, le reste sera fait manuellement.

D'autre part, j'ai remarqué aussi lors d'un essai dans la feuille "affectation" j'ai rentré une semaine dans la première ligne dans une des colonnes "ateliers", sans rien avoir changé après sur les autres feuilles, lorsque je clique à nouveau sur la feuille "affectation" cette dernière se rafraîchit donc et là je constate que la semaine que j'ai rentrée à disparue.

Voila je te donne encore du fil à retordre, merci pour tous tes efforts, j'apprécie beaucoup o)

loulouange · 3 Octobre 2013

Re : Générer des courriers types word à partir d'excel

Bonjour Martial,

J'ai regardé le premier fichier que tu avais joint aussi et ce dernier n'a pas le problème que j'avais cité plus haut à savoir :

loulouange à dit:
D'autre part, j'ai remarqué aussi lors d'un essai dans la feuille "affectation" j'ai rentré une semaine dans la première ligne dans une des colonnes "ateliers", sans rien avoir changé après sur les autres feuilles, lorsque je clique à nouveau sur la feuille "affectation" cette dernière se rafraîchit donc et là je constate que la semaine que j'ai rentrée à disparue.

Donc je suis en train de travailler dessus, j'essaye de reproduire la recopie des noms et prénoms dans la feuille suivi, c'est long mais j'avance on dirait je te l'enverrais en espérant avoir trouvé la solution.

Bonne journée

Lou

loulouange · 10 Octobre 2013

Re : Générer des courriers types word à partir d'excel

Bonsoir Martial,

Me revoila enfin, difficile en ce moment de trouver un peu de temps.

J'ai quand même pas mal travailler sur le fichier, cela n'a pas été une mince affaire.

J'ai réussi à faire fonctionner la macro pour la feuille "suivi" , j'ai finalement crée toutes les semaines dans la feuille "base" car autrement ce que j'écrivais dans la feuille "suivi" disparaissait.

J'ai crée une nouvelle feuille nommée "typo". Là j'ai vraiment eu du mal pour trouver les bons codes mais j'y suis arrivée mais honnêtement je ne comprends pas comment j'ai fait.

Cela concerne la partie ACTIVATE

Voici celui j'ai mis dans la feuille TYPO :

Code:

Private Sub Worksheet_Activate()
Application.DisplayAlerts = 0: Application.EnableEvents = 0
'A l'activation de la feuille
  'On cherche la dernière ligne pleine pour supprimer les données
  j = [A65536].End(xlUp).Row
  If j > 4 Then Rows("5:" & j).ClearContents
  With Sheets("Base")
  'Pour toutes les lignes de "Base"
  For j = 3 To .[A65536].End(xlUp).Row
    If .Cells(j, 2) <> "" Then
      'On copie les données de Base dans la feuille activée
      i = [A65536].End(xlUp)(5).Row
      Cells(i, 1).Resize(, 5).Value = .Cells(j, 1).Resize(, 5).Value
      Cells(i, 6) = .Cells(j, 21)
      Cells(i, 7).Resize(, 22).Value = .Cells(j, 671).Resize(, 22).Value
  End If
  Next
  End With
Range("A5:AB" & [D65536].End(xlUp).Row).Sort [D5], xlAscending, [E5], , xlAscending, , , xlNo
Application.DisplayAlerts = -1: Application.EnableEvents = -1
End Sub

Au début je n'arrivais pas à recopier les données à partir de la 5ème ligne, ça mettait plus haut donc j'ai changé un peu les chiffres mais je voyais aucune différence.
Cela a fonctionné lorsque j'ai mis 5 au lieu de 2 sur cette ligne

Code:

 i = [A65536].End(xlUp)(5).Row

Alors que dans la feuille "SUIVI" c'est noté "2" et là les données sont pourtant recopiées à partir de la ligne 7.

De meme que j'ai écrit ceci :

Code:

If j > 4 Then Rows("5:" & j).ClearContents

J'avais changé par un 4 (j'ai tout tenté..) et là je viens de tester en remettant un 2 et ça fonctionne toujours ? j'aimerais bien comprendre ce qui change ?

J'avais une autre question, peut on mettre cette feuille TYPO dans un classeur à part ? cela aurait été plus simple car ce tableau doit être envoyé régulièrement pas mail, cela m'éviterrait de devoir à chaque fois masquer les autres feuilles et protéger le classeur .

Je te joins le tableau modifié.

Au plaisir de te lire.

Loulou

Staple1600 · 11 Octobre 2013

Re : Générer des courriers types word à partir d'excel

Bonjour à tous

Une suggestion pour ce point:

J'avais une autre question, peut on mettre cette feuille TYPO dans un classeur à part ? cela aurait été plus simple car ce tableau doit être envoyé régulièrement pas mail, cela m'éviterrait de devoir à chaque fois masquer les autres feuilles et protéger le classeur .

Tu pourrais envisager de faire en VBA un export PDF de la feuille contenant le tableau à envoyer par mail.

Ou sinon toujours par VBA tu copies la feuille du tableau (en valeurs seules) et sans son VBA et c'est cette copie que tu envoies par mail (donc pas de feuilles à masquer et pas de protection à appliquer)

Staple1600 · 11 Octobre 2013

Re : Générer des courriers types word à partir d'excel

Re

Voici un exemple de code pour copier une feuille en valeurs seules et sans macros

Code:

Sub CopierFeuilleValSeulesNoVBA()
Dim WbkCopy As Workbook
Sheets("Feuil1").Copy
Set WbkCopy = ActiveWorkbook
With WbkCopy
   With .ActiveSheet
    .UsedRange.Value = .UsedRange.Value
   End With
   Application.DisplayAlerts = False
   .SaveAs "c:\temp\test.xlsx", xlOpenXMLWorkbook
   End With
End Sub

Yaloo · 11 Octobre 2013

Re : Générer des courriers types word à partir d'excel

Bonsoir Loulou, Staple,

loulouange à dit:

Code:

Private Sub Worksheet_Activate()
Application.DisplayAlerts = 0: Application.EnableEvents = 0
'A l'activation de la feuille
  'On cherche la dernière ligne pleine pour supprimer les données
  j = [A65536].End(xlUp).Row
  If j > 4 Then Rows("5:" & j).ClearContents
  With Sheets("Base")
  'Pour toutes les lignes de "Base"
  For j = 3 To .[A65536].End(xlUp).Row
    If .Cells(j, 2) <> "" Then
      'On copie les données de Base dans la feuille activée
      i = [A65536].End(xlUp)(5).Row
      Cells(i, 1).Resize(, 5).Value = .Cells(j, 1).Resize(, 5).Value
      Cells(i, 6) = .Cells(j, 21)
      Cells(i, 7).Resize(, 22).Value = .Cells(j, 671).Resize(, 22).Value
  End If
  Next
  End With
Range("A5:AB" & [D65536].End(xlUp).Row).Sort [D5], xlAscending, [E5], , xlAscending, , , xlNo
Application.DisplayAlerts = -1: Application.EnableEvents = -1
End Sub

VB:

j = [A65536].End(xlUp).Row
  If j > 4 Then Rows("5:" & j).ClearContents

Ces 2 lignes te permettent de trouver la dernière cellule pleine de la colonne A puis si cette ligne est supérieure à 4 (donc à partir de 5) de supprimer les lignes de 5 jusqu'à la dernière.

Maintenant pour la copie à partir de la ligne 5 :
Comme dans la macro tu demandes de supprimer les lignes au-delà de la ligne 4, ta première ligne vide est la 5

VB:

With Sheets("Base")
  For j = 3 To .[A65536].End(xlUp).Row
    If .Cells(j, 2) <> "" Then
      'On copie les données de Base dans la feuille activée
      i = [A65536].End(xlUp)(5).Row
      Cells(i, 1).Resize(, 5).Value = .Cells(j, 1).Resize(, 5).Value

Pour les lignes dans la feuille Base de 3 jusqu'à la dernière ligne pleine de la colonne A (variable j), si la cellule B de cette ligne n'est pas vide, alors on va copier ....
Mais copier où ? On recherche donc la première cellule vide de la colonne A (variable i)
Si tu mets
i = [A65536].End(xlUp).Row --> dernière cellule pleine de la colonne A
i = [A65536].End(xlUp)(2).Row --> première cellule vide de la colonne A
i = [A65536].End(xlUp)(3).Row --> deuxième cellule vide de la colonne A
i = [A65536].End(xlUp)(4).Row --> troisième cellule vide de la colonne A
i = [A65536].End(xlUp)(5).Row --> quatrième cellule vide de la colonne A

Dans ta macro, les lignes sont copiées à la quatrième cellule vide de la colonne A, mais comme en dernière ligne de ta macro, tu fais un tri, les lignes vides de ta feuille sont supprimées.
Fais un essai en supprimant la ligne de tri ou en mettant ' pour la passer en commentaire.

Tu peux voir l'action de chaque ligne de code en faisant avancer la macro en pas à pas, mets toi sur une ligne en début de macro puis fais F9, la ligne va être surlignée en foncé (avec un point dans la marge) (tu peux aussi directement cliquer dan la lmarge) puis lances la macro (il suffit d'activer ta feuille), la macro va s'arrêter au point d'arrêt, ensuite avances en faisant F8, la ligne est en jaune, si tu réduit ta fenêtre VBE, tu vas voir ce qui se passe dans ton fichier.

Tu peux aussi voir les données de tes variables, en cliquant sur "Affichage" (dans la barre de menu) puis "Fenêtre variables locales", une fenêtre s'ouvre, à chaque fois que tu fera F8 pour avancer d'une ligne si une variable prend une valeur, cela sera inscrit dans la fenêtre.

loulouange à dit:
J'avais une autre question, peut on mettre cette feuille TYPO dans un classeur à part ? cela aurait été plus simple car ce tableau doit être envoyé régulièrement pas mail, cela m'éviterrait de devoir à chaque fois masquer les autres feuilles et protéger le classeur .

Comme l'a indiqué Staple, il est possible de créer un nouveau fichier avec seulement la feuille active (soit au format Excel soit en PDF), il est même possible de l'envoyer directement avec une macro dans Excel.

A te relire

Martial

loulouange · 11 Octobre 2013

Re : Générer des courriers types word à partir d'excel

Bonsoir Staple, Bonsoir Martial,

Merci à vous deux d'avoir pris le temps pour me répondre et pour tous ces renseignements.

Je vais regarder cela en détail ce week end. Je vais tester les manipulations pour mieux comprendre la logique des codes.

Pour ce qu concerne la possibilité de créer un fichier typo à part, en fait le tableau doit etre anonyme donc je dois masquer les noms et prénoms et aussi faire un tri et enlever les personnes qui sont sorties de l'action. Donc peut etre que ça serait plus simple de passer par une macro vers excel.

Encore merci pour votre aide.

Je vous souhaite une bonne soirée.

loulouange · 12 Octobre 2013

Re : Générer des courriers types word à partir d'excel

Bonjour Martial, bonjour Staple,

J'ai pu prendre un peu de temps pour suivre toutes vos suggestions.

Staple, j'ai essayé de mettre ton code mais je dois surement mal m'y prendre désolée je découvre au fur et à mesure les fonctionnalités d'excel.

En fait j'ai inséré le code dans la feuille nommée "typo" que je veux recopier comme ceci :

Code:

Sub CopierFeuilleValSeulesNoVBA()
Dim WbkCopy As Workbook
Sheets("typo").Copy
Set WbkCopy = ActiveWorkbook
With WbkCopy
   With .ActiveSheet
    .UsedRange.Value = .UsedRange.Value
   End With
   Application.DisplayAlerts = False
   .SaveAs "c:\lou\typotest.xlsx", xlOpenXMLWorkbook
   End With
End Sub

Mais rien ne se passe, pas de message d'erreur non plus.

J'ai cherché des infos sur le net j'ai lu que certains mettaient le code dans le fichier de destination, est ce que je me suis trompée ?

J'ai testé une autre méthode en suivant le tuto sur un site (je ne sais ps si je peux mettre le lien), c'est la méthode où l'on se positionne sur la feuille en faisant un clic droit "déplacer ou copier" et là on choisi l'autre classeur. C'est pas mal, mais il ne faut pas oublier de cocher "créer une copie" sinon celui de ma feuille source disparaît.

Mais j'avoue que j'aurais préféré une manipulation plus simple.

Martial, j'ai enfin compris mes erreurs !! surtout avec cette explication :

Code:

Pour les lignes dans la feuille Base de 3 jusqu'à la dernière ligne pleine de la colonne A (variable j), si la cellule B de cette ligne n'est pas vide, alors on va copier ....
Mais copier où ? On recherche donc la première cellule vide de la colonne A (variable i)

If .Cells(j, 2) <> "" Then

(j,2) : je pensais que cela correspondait au numéro de la ligne et non de la colonne. Donc en effet c'est plus logique !

Et pour l'histoire de la première cellule vide etc... ça y est j'ai saisi la formule.

J'ai testé avec ta méthode en me mettant dans la macro du code pour voir ce qui se passait, c'est vrai que ça aide bien.

Par contre j'ai pas réussi avec la méthode "Fenêtre variables locales" mais bon l'essentiel c'est qu'à présent ça me semble plus clair.

J'avais aussi une autre question, dans l'un de tes précédents posts tu m'avais conseillée ceci :

Code:

Si rien ne se passe lorsque tu fais des modifs ou lorsque tu actives les feuilles. Tu lances la macro Relance ou tu fais Ctrl + n.

Chez moi ça ne fonctionne pas, lorsque je fais Ctrl+n j'ai un nouveau classeur qui s'ouvre. et je n'ai pas trouvé le bouton "Relance'.

En tout cas un grand merci pour votre aide, c'est vraiment très agréable de trouver des personnes disponibles et volontaires.

Au plaisir de vous lire.

Bonne soirée.

Yaloo · 12 Octobre 2013

Re : Générer des courriers types word à partir d'excel

Bonsoir Loulou,

Lorsque je disais d'utiliser ctrl + n, c'était uniquement dans mon fichier. Cela permettant de réactiver la gestion des évènements.

Pour lancer la macro de Staple, il te faut un évènement, soit tu crées un Bouton et lorsque tu cliques sur le bouton tu crées un évènement, soit la macro est déclenchée lorsque tu changes de sélection, de valeur dans une cellule, tu double-clic, tu clic-droit etc...

Pour la macro de Staple, toutes les données de la feuille sont copiées dans un nouveau fichier.

Si tu veux modifier certaines données tu peux le faire avant l'enregistrement .SaveAs ....

A+

Martial

Staple1600 · 12 Octobre 2013

Re : Générer des courriers types word à partir d'excel

Bonsoir à tous, Yaloo

loulouange

loulouange à dit:
.../...
En fait j'ai inséré le code dans la feuille nommée "typo" que je veux recopier comme ceci :

Code:

Sub CopierFeuilleValSeulesNoVBA() Dim WbkCopy As Workbook Sheets("typo").Copy Set WbkCopy = ActiveWorkbook With WbkCopy With .ActiveSheet .UsedRange.Value = .UsedRange.Value End With Application.DisplayAlerts = False .SaveAs "c:\lou\typotest.xlsx", xlOpenXMLWorkbook End With End Sub

Mais rien ne se passe, pas de message d'erreur non plus.
.../...

Tu as été dans le répertoire C:\lou si il n'y a pas un classeur nommé typotest.xlsx?
NB: Le répertoire C:\lou existe bien sur ton PC?

Générer des courriers types word à partir d'excel

loulouange

XLDnaute Occasionnel

Pièces jointes

Yaloo

XLDnaute Barbatruc

loulouange

XLDnaute Occasionnel

Pièces jointes

Yaloo

XLDnaute Barbatruc

Pièces jointes

Yaloo

XLDnaute Barbatruc

Yaloo

XLDnaute Barbatruc

Pièces jointes

loulouange

XLDnaute Occasionnel

loulouange

XLDnaute Occasionnel

loulouange

XLDnaute Occasionnel

Pièces jointes

Staple1600

XLDnaute Barbatruc

Staple1600

XLDnaute Barbatruc

Yaloo

XLDnaute Barbatruc

Pièces jointes

loulouange

XLDnaute Occasionnel

loulouange

XLDnaute Occasionnel

Yaloo

XLDnaute Barbatruc

Staple1600

XLDnaute Barbatruc

Discussions similaires