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

[loulouange]

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]

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

Bonjour Loulouange,

Si tu as fait des modifications de présentation dans ton tableau, il faut sûrement modifier des lignes de code dans la macro.

Pourrais-tu remettre le fichier modifié ? Au moins la base.

Concernant les accords je ne vois pas trop où il y a des accords à faire dans tes fichiers Word.

A te relire

Martial

 

[loulouange]

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

merci Martial d'etre encore là pour m'aider.

je remets ici le répertoire avec les lettres types et la base de données sources que j'ai modifiée.

j'ai rajouter des macros de tri alphabétique, j'ai réussi à faire en sorte que les données de la feuille "genéral" soit reproduite sur la feuille2. J'ai voulu par la suite faire trois feuilles pour chaque conseiller "cb" "el" "em" mais je n'ai pas trouvé la formule permettant de dire que par exemple

dans la feuille général si je choisis dans la liste déroulante "CB" dans la colonne "CT"
la ligne se recopie dans la feuille "CB", idem pour les autres conseillers.

Je ne sais pas si je suis bien claire.
Au sujet de la liste déroulante est il possible de mettre les noms de couleurs différentes ?

Pour en revenir au bouton, j'ai voulu formuler les noms comme "AI" ou "AJ" du coup j'ai du réajuster les bordures et là j'ai fait un peu n'importe quoi désolée.

Ah et j'oubliais pour l'histoire des accords, j'ai bien compris comment tu avais fait pour mettre les signet dans la lettre word au niveau de l'adresse, j'ai d'ailleurs rajouter la civilité qui manquait;

Mais si peux voir mes lettres d'origines en début de post, tu peux voir dans word que j'ai mis des champs avec des conditions par rapport à la liste de contrôle civilité que j'avais mise auparavant et qui à présent n'existe plus.

Donc à présent je ne sais pas comment recopier la civilité en début et dans la formule de politesse; ainsi que faire les accord "é" et "ée".

Voila ou j'en suis j'ai encore du boulot, mais j'y arriverais....

 

[Yaloo]

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

Bonjour loulouange,

Pour l'envoi des données dans les feuilles CB, EL et EM. Tu veux envoyer uniquement ce qui concerne CB ou ce qui concerne EL et enfin ce qui concerne EM dans les feuilles concernées ? C'est bien ça ? Car actuellement, tu envoies toutes les lignes dans la même feuille.

Pour la couleur, avoir de la couleur dans la liste déroulante, il ne me semble pas que ce soit possible, pour mettre par contre de la couleur dans la cellule ou sur toute la ligne, il faut faire une mise en forme conditionnelle. Dis-moi ce que tu souhaites exactement.

Pour le moment je n'ai pas trop réfléchit aux accords mais il doit être possible de créer un signet et si dans la colonne B il est inscrit Madame on met un e au signet. A voir ....

Réponds à mes premières questions, et je te réponds demain dans la journée.

A te relire

Martial

 

[loulouange]

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

Bonjour Martial,

Oui c'est bien ça, j'aimerais avoir une feuille par conseiller "CB" "EL" et "EM" où l'on trouverait leur bénéficiaire respectifs.

Pour les couleurs, c'est vrai que j'aurais préféré que se soit la couleur de la police qui change. mais bon si ce n'est pas possible à la limite juste la cellule, mais ça risque de charger un peu trop car je suis amenée à coloriser des lignes entière avec couleur différentes selon le stade de l'accompagnement.

Ca serait super s'il était possible de faire un signet pour les accords, et aussi pour répéter le signet 1 celui de civilité, car j'ai voulu le faire et donc dans word je l'avais mis aux endroits souhaités mais ça ne le prend qu'une fois. A moins de dire que la colonne civilité est égale à un autre signet que le 1 ?

Sinon lorsque je vais dans "visualiser le code" je m'y perd un peu et je n'arrive plus à trouver le code que tu avais fait avec tes explications, je l'avais copier coller dans un format word pour le retrouver rapidement :


Option Explicit

Private Sub CommandButton1_Click()
Dim oWord As Object
Dim oDoc As Object
Dim i&
'Si aucune ligne de la ListBox1 n'est sélectionnée alors on a un message puis on sort
If ListBox1.ListIndex = -1 Then MsgBox "Il faut sélectionner une personne": Exit Sub
'On ferme l'UserForm1
Unload Me
'Détermine l'Objet oWord qui sera l'application
Set oWord = CreateObject("Word.Application")
'Détermine l'Objet oDoc qui sera le document Word de l'application oWord
'On en profite pour l'ouvrir en même temps
'Il faut mettre le chemin d'accès et le nom du fichier avec son extension
'Le chemin d'accès, dans ce cas-là, est le même que celui de ce fichier Excel
Set oDoc = oWord.Documents.Open(ThisWorkbook.Path & "\Convov2 suite à AJ avec rdv ateliers.docx")
'Avec ce document
With oDoc
'Pour i de 1 à 6, puisque nous avons 6 signets dans le document Word
'et 6 colonnes dans le fichier Excel
For i = 1 To 6
'Le signet prend la valeur de la colonne, on fonction de i
.Bookmarks("Signet" & i).Range = ListBox1.Column(i - 1)
Next
End With
'On rend Word visible
oWord.Visible = True
End Sub
'Idem que la macro ci-dessus, on change juste le nom du fichier
Private Sub CommandButton2_Click()
Dim oWord As Object
Dim oDoc As Object
Dim i&
If ListBox1.ListIndex = -1 Then MsgBox "Il faut sélectionner une personne": Exit Sub
Unload Me
Set oWord = CreateObject("Word.Application")
Set oDoc = oWord.Documents.Open(ThisWorkbook.Path & "\Convov2 suite à AI avec rdv ateliers.docx")
With oDoc
For i = 1 To 6
.Bookmarks("Signet" & i).Range = ListBox1.Column(i - 1)
Next
End With
oWord.Visible = True
End Sub
Private Sub UserForm_Initialize()
Dim i&, j&, DerL&, Plage
'Cherche la dernière ligne de la colonne A
'Comme la feuille n'est pas précisée, il s'agit de la feuille active
DerL = [A65536].End(xlUp).Row
'Détermine les données à mettre dans la ListBox1
'Colonne de A à F, et ligne de 3 à la dernière ligne
Plage = Range("A3:F" & [A65536].End(xlUp).Row)
'Remplis la ListBox1 avec les valeurs de Plage
ListBox1.List = Plage
''''On pourrait aussi écrire en une seule ligne, mais moins facile à comprendre
'ListBox1.List = Range("A3:F" & [A65536].End(xlUp).Row).Value
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("E2:E65536")) Is Nothing Then
If Target.Value = "Ready for upload" Then
With Sheets("Commande traitée")
LigVide = .Range("B65536").End(xlUp).Row + 1
.Cells(LigVide, 2) = Target.Offset(0, -4).Value
.Cells(LigVide, 3) = Target.Offset(0, -3).Value
.Cells(LigVide, 4) = Target.Offset(0, -2).Value
.Cells(LigVide, 10) = Target.Offset(0, -1).Value
End With
End If
End If
End Sub

Entre temps je l'avais modifié car j'ai rajouté le signet pour civilité, mais là je le vois plu dans excel, je m'y perds un peu avec l'arborescence de la liste.

Dans l'attente de te lire.

Je te souhaite une bonne journée.

 

[Yaloo]

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

Re,

Pour le moment je me penche plutôt sur l'envoi des données dans les feuilles respectives.

Dans ce que tu as fait, tu envoies toutes les données de la feuille "Général" colonnes de B à G dans les feuilles "Conseiller", le problème est si tu as déjà des données dans les feuilles "Conseiller" notamment dans les colonnes de H à X, que fait-on ? Car les lignes risquent de ne pas être copiées dans le même ordre.

Comme tu as construit ta macro avec un Private Sub Worksheet_Change(ByVal Target As Range) je suppose que dès que l'on change une donnée dans l'onglet "Général", on copie toutes les données dans les feuilles "Conseiller".

Sinon, nous avons la possibilité de copier uniquement la ligne qui comprend des modifications. C'est-à-dire que l'on modifie une ligne existante ou que l'on créer une nouvelle ligne lorsque la ligne dans la feuille "Conseiller" n'existe pas.

Un autre problème risque d'arriver si tu modifies le Nom (par exemple, parce que tu as fait une faute d'orthographe) cela va créer une nouvelle ligne.

De même, si tu décides de changer le conseillé, car erreur de saisie, la ligne sera copiée une première fois dans la feuille d'un conseiller puis du nouveau.

Il me semble qu'il serait plus judicieux de mettre un N° d'Identifiant, en colonne A, il suffit de mettre Id, et les chiffres de 1 à .....

A toi de me dire

Martial

 

[loulouange]

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

bonsoir le forum, bonsoir Martial,

Oui c'est bien ça, Je pense qu'il faut faire des conditions avec if mais je visualise mal le code pour l'instant.

J'ai essayé de comprendre tes interrogations et je suis retournée sur le fichier données.

Actuellement si je modifie des données, par exemple l'orthographe d'un nom dans les colonnes de B à G dans la feuille "général" ça corrige aussi dans la feuille CB.

Est ce que tu veux dire que si l'on mets une formule disant de ne prendre que les lignes correspondantes aux conseillers soient uniquement reportées dans leur feuille respective cela ne fera pas la modification ?

Je ne comprends pas quand tu dit que les lignes risquent de ne pas être copiées dans le meme ordre ?

J'ai mis une macro tri de A à Z donc lorsque je clique sur le bouton dans la feuille "général" ça fait également le tri dans les autres feuilles.

Ensuite les lignes sont rajouter au fur et à mesure au meme niveau non ?
je veux dire par exemple que dans la feuille "général" j'ai rentré des noms de la ligne 3 à ligne 10, si je vais dans la feuille CB c'est la même chose.

j'ai un peu de mal à saisir l'histoire des lignes qui s'ajoutent et des risques de doublons. comme je le disais plus haut, j'ai testé si je modifie le nom suite à une erreur, ça modifie aussi sur la feuille CB sur la meme ligne, donc pas de doublons.

Désolée si je ne suis pas assez claire et pour toutes ces questions. Mais si c'est vraiment trop compliqué c'est pas bien grave, je ne ferais pas de feuille par conseiller.

Au plaisir de te lire.

Bonne soirée.

 

[Yaloo]

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

Bonsoir loulouange,

Comme tu ne copies que les colonnes de B à G et que tu fais ton tri, les données sont mises dans l'ordre alpha des noms.

Si par exemple, tu as ALBIN, BRUNO, MARTIN et SYLVESTRE et que tu as des données dans tes colonnes de H à X correspondant à ces lignes, et que tu rajoutes DUPONT. Avec le tri, tu risques d'avoir un problème d'ordre alphabétique puisque DUPONT va venir s'insérer entre BRUNO et MARTIN. Me trompe-je ?

A+

Martial

 

[Yaloo]

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

Re,

J'ai peut-être vu un problème où il n'y en avait pas. Si le tri est fait après la copie des données, il n'y a peut-être pas de problème.

Tu veux toujours faire la copie avec une procédure Change ? ou par bouton ?

Dans le fichier ci-dessous, je l'ai fait avec la procédure Change. Surtout ne pas fusionner les cellules des entêtes de colonnes des feuilles.

A+

Martial

 

[loulouange]

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

Bonjour Martial,

Génial ce que tu as fait !

Je suis allée voir comment tu as fait la mise en forme conditionnelle, et j'ai vu que l'on pouvait aussi changer la couleur de la police en allant dans format police, donc c'est possible alors !

Autrement j'ai voulu visualiser le code pour savoir comment tu as fait pour que les lignes soit copiées automatiquement dans les feuille des conseillers mais je n'ai pas trouvé.

Autrement le system "change" est très bien je pense. Je ferais des essais pour être sure que les lignes recopiées ne soient pas mélangées après la colonne G.

Je suis bien contente, grâce à toi ça avance bien. une fois que j'aurais compris le code pour les conseillers, faudra que je penche sur le bouton des lettres types.

Encore merci toi.

Je te souhaite une bonne journée.

 

[Yaloo]

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

Bonjour loulouange, le forum,

Voici ton fichier modifié, tu trouveras des explications de la code Change. J'en ai profité pour inclure le tri dans la procédure Change et pour modifié ta macro de tri.

A+

Martial

 

[loulouange]

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

bonjour Martial,

Je te remercie pour ton travail.

J'ai regardé ton code et tes explications, je comprends à peu près la logique, je voulais juste savoir en fait à quoi correspondait le "i" ?

De plus, je n'ai pas trouvé comment fait pour dire à excel de recopier la ligne par exemple ou il y a "CB" dans la feuille "général" vers la feuille "CB', ainsi que pour EM et EL.

J'ai vu que le tri se faisait automatiquement dans toutes les feuilles mais là non plus je n'ai pas vu où tu avais mis le code. Désolée mais j'ai un peu de mal avec l’arborescence de la fenêtre des codes.

Par contre, lorsque je remplis la feuille "général" au départ, je ne sais pas quel conseiller va suivre la personne donc la case va rester un moment, j'aurais voulu faire le tri alphabétique meme avec la case vide. Je pensais rajouter mon bouton que j'avais mis au début juste pour faire le tri dans la feuille "générale", je ne sais pas si ça va poser problème avec ton code tri.

Voila pour mes interrogations.

Encore merci pour ta disponibilité.

A bientot.

 

[Yaloo]

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

Bonjour loulouange,

Dans un premier temps, i prend la valeur de 2 (parcequ'il ne faut pas supprimer les données de la feuille 1 "Général") jusqu'au Nb total de feuille (dans ton cas 4) cela permet de faire une boucle sur toutes les feuilles afin de supprimer les données présentes dans chaque feuille de 2 à 4.

Ensuite i prend la valeur du Nb de ligne remplies dans la feuille 1 "Général".
For i = 3 to [A65536].End(xlUp).Row (de 3 jusqu'à la dernière ligne).
Cette fois, on regarde toutes les lignes et on envoie les données dans la feuille adéquate. CT prend la valeur de la cellule de la colonne A, ensuite on copie les données dans la feuille Sheets(CT) ... puis l'endroit où l'on colle les données.

Si tu ne remplies pas le nom du conseiller pour chaque ligne, il faut changer [A65536] par [C65536] à chaque fois.

Pour le tri, j'avais laissé le tri indépendant de la macro Change, je le rappelle juste à la fin de Change. Tu peux donc l'utiliser avec un bouton.

A te relire

Martial

 

[loulouange]

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

Bonjour Martial,

J'ai relu plusieurs fois tes explications, et je pense avoir saisi !! en fait pas besoin de préciser dans la macro les noms des conseillers "CB" 'EL" ou "EM", si j'ai compris, tu lui demandes de repérer dans la feuille 'général" dans la colonne A3 le nom du conseiller et de chercher le meme nom dans les feuilles que j'ai nommées par conseiller, et là il copie les lignes correspondantes, c'est bien ça ?

Par contre j'ai pas bien compris cette phrase :

"Si tu ne remplies pas le nom du conseiller pour chaque ligne, il faut changer [A65536] par [C65536] à chaque fois."

Quel est le risque si je ne fais pas ce changement ?

Merci pour ton aide.

 

[Yaloo]

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

Bonjour,
Oui c'est bien ça.
Pour le deuxième point, si tu ne remplies pas toutes les données du conseiller, tu risques d'avoir la dernière ligne remplie avec le nom et coordonnées mais la colonne A sera vide, donc la macro ne prendra pas en compte la dernière ligne. C'est pour cela qu'il vaut mieux chercher la dernière cellule vide de la colonne C puisque c'est le nom et qu'il y aura toujours un nom.
A+
Martial

 

[loulouange]

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

Bonjour Martial,

J'ai essayé de comprendre tes explications mais j'avoue avoir un peu de mal.

Je suis désolée avec toutes mes questions, tu passes déjà beaucoup de temps pour me répondre et je ne voudrais pas abuser.

Dans ce que j'ai compris :

La macro affecte la ligne de la personne dès qu'elle connait le nom du conseiller se trouvant dans la colonne A1 de la feuille "Général"

Donc si j'ai bien compris il va chercher à chaque fois la dernière ligne, ce qui veut dire si j'ai une ligne avec la colonne AI vide qui se retrouve en haut et si par la suite je la remplie la cellule A1 avec le nom du CT, la macro ne la détectera pas parce qu'elle ne se trouvait pas sur la dernière ligne ?


J'ai fait un test avec le tableau :

1 : j'ai rajouté deux nouvelles personnes à la fin de la liste avec les colonnes A1 vides


2 : j'ai fait comme tu as m'a dit j'ai remplacé [C65536] par [C65536] sur cette ligne

For i = 3 to [A65536].End(xlUp).Row

3 : j'ai pris la dernière ligne et remplis la case A1 par le nom du CT (la ligne au dessus elle a toujours la case A1 vide)

J'ai eu un message d'erreur, j'ai fait une capture d'écran pour te montrer :


J'ai voulu du coup changer aussi le code sur cette ligne jaune par [C65536] mais le problème reste le même.

Je ne sais pas si ce que je demande est possible. Sinon je recopierais les colonnes manuellement c'est pas bien grave.

Par contre j'ai une dernière question, comment peut on annuler les dernières modifications, car je dois à chaque fois fermer le fichier sans l'enregistrer et recommencer.

A bientôt pour te lire.

Bonne journée.