Bonjour,
Voici le code d'un Userform permettant d'exporter un publipostage sous Word depuis Excel en fichiers séparés avec choix du nom parmi les entêtes du fichier Excel source et choix d'un séparateur entre les champs (utile pour utilisation ultérieure de MailMerge sous Thunderbird).
Possibilité de personnaliser également le début du nom des fichiers.
Exemple : "Convocation_NOM_Prénom.pdf "
Dans l'ordre :
1- Créer un nouveau document Word
2- Créer un Userform et y copier le code ci-dessous
3- Créer un bouton Activex et y copier le code ci-dessous
Une fois le fichier créer, le sauvegarder au format modèle ou vierge pour utiliser à volonté...
Code du bouton active x :
Private Sub CommandButton1_Click()
UserForm1.Show
End Sub
Code du Userform1 :
Private Sub ComboBox1_Change()
w1 = ComboBox1.ListIndex
If ComboBox1.Text = "" Then
ComboBox2.Enabled = False
ComboBox3.Enabled = False
ComboBox4.Enabled = False
CommandButton2.Enabled = False
Else
ComboBox2.Enabled = True
ComboBox3.Enabled = False
ComboBox4.Enabled = False
CommandButton2.Enabled = True
End If
If Len(TextBox2.Text) > 0 Then
Label1.Caption = TextBox2.Text & TextBox3.Text & ComboBox1.Text
Else
Label1.Caption = ComboBox1.Text
End If
End Sub
Private Sub ComboBox1_Enter()
Call ComboBox1_Change
End Sub
Private Sub ComboBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Call ComboBox1_Change
End Sub
Private Sub ComboBox2_Change()
w2 = ComboBox2.ListIndex
If ComboBox2.Text = "" Then
ComboBox1.Enabled = True
ComboBox2.Enabled = True
ComboBox3.Enabled = False
ComboBox4.Enabled = False
If Len(TextBox2.Text) > 0 Then
Label1.Caption = TextBox2.Text & TextBox3.Text & ComboBox1.Text
Else
Label1.Caption = ComboBox1.Text
End If
Else
ComboBox1.Enabled = False
ComboBox2.Enabled = True
ComboBox3.Enabled = True
ComboBox4.Enabled = False
If Len(TextBox2.Text) > 0 Then
Label1.Caption = TextBox2.Text & TextBox3.Text & ComboBox1.Text & TextBox3.Text & ComboBox2.Text
Else
Label1.Caption = ComboBox1.Text & TextBox3.Text & ComboBox2.Text
End If
End If
End Sub
Private Sub ComboBox2_Enter()
Call ComboBox2_Change
End Sub
Private Sub ComboBox2_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Call ComboBox2_Change
End Sub
Private Sub ComboBox3_Change()
w3 = ComboBox3.ListIndex
If ComboBox3.Text = "" Then
ComboBox1.Enabled = False
ComboBox2.Enabled = True
ComboBox3.Enabled = True
ComboBox4.Enabled = False
If Len(TextBox2.Text) > 0 Then
Label1.Caption = TextBox2.Text & TextBox3.Text & ComboBox1.Text & TextBox3.Text & ComboBox2.Text
Else
Label1.Caption = ComboBox1.Text & TextBox3.Text & ComboBox2.Text
End If
Else
ComboBox1.Enabled = False
ComboBox2.Enabled = False
ComboBox3.Enabled = True
ComboBox4.Enabled = True
If Len(TextBox2.Text) > 0 Then
Label1.Caption = TextBox2.Text & TextBox3.Text & ComboBox1.Text & TextBox3.Text & ComboBox2.Text & TextBox3.Text & ComboBox3.Text
Else
Label1.Caption = ComboBox1.Text & TextBox3.Text & ComboBox2.Text & TextBox3.Text & ComboBox3.Text
End If
End If
End Sub
Private Sub ComboBox3_Enter()
Call ComboBox3_Change
End Sub
Private Sub ComboBox3_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Call ComboBox3_Change
End Sub
Private Sub ComboBox4_Change()
If ComboBox4.Text = "" Then
ComboBox1.Enabled = False
ComboBox2.Enabled = False
ComboBox3.Enabled = True
ComboBox4.Enabled = True
If Len(TextBox2.Text) > 0 Then
Label1.Caption = TextBox2.Text & TextBox3.Text & ComboBox1.Text & TextBox3.Text & ComboBox2.Text & TextBox3.Text & ComboBox3.Text
Else
Label1.Caption = ComboBox1.Text & TextBox3.Text & ComboBox2.Text & TextBox3.Text & ComboBox3.Text
End If
Else
ComboBox1.Enabled = False
ComboBox2.Enabled = False
ComboBox3.Enabled = False
ComboBox4.Enabled = True
If Len(TextBox2.Text) > 0 Then
Label1.Caption = TextBox2.Text & TextBox3.Text & ComboBox1.Text & TextBox3.Text & ComboBox2.Text & TextBox3.Text & ComboBox3.Text & TextBox3.Text & ComboBox4.Text
Else
Label1.Caption = ComboBox1.Text & TextBox3.Text & ComboBox2.Text & TextBox3.Text & ComboBox3.Text & TextBox3.Text & ComboBox4.Text
End If
End If
End Sub
Private Sub ComboBox4_Enter()
Call ComboBox4_Change
End Sub
Private Sub ComboBox4_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Call ComboBox4_Change
End Sub
Private Sub CommandButton2_Click()
If pdf = True Then
Call pdf1
Else
If doc = True Then
Call doc1
Else
Call docx1
End If
End If
End Sub
Private Sub pdf1()
' Déclaration des variables
Dim iR As Integer
Dim i As Integer
Dim oDoc As Document
Dim DocName As String
Dim export As String
Dim extendion As String
Dim oDS As MailMergeDataSource
UserForm1.hide
CommandButton2.Enabled = False
' disparition bouton activex
w1 = ComboBox1.ListIndex
w2 = ComboBox2.ListIndex
w3 = ComboBox3.ListIndex
w4 = ComboBox4.ListIndex
' Affectation des objets
Set oDoc = ActiveDocument
Set oDS = oDoc.MailMerge.DataSource
iR = oDoc.MailMerge.DataSource.RecordCount
Debug.Print iR
For i = 1 To iR
With oDoc.MailMerge
'Définition du premier et dernier enregistrement
.DataSource.FirstRecord = i
.DataSource.LastRecord = i
' Envoi des données dans un nouveau document
.Destination = wdSendToNewDocument
' Exécution du publipostage
.Execute
' Actualisation de l'enregistrement pour la sauvegarde
.DataSource.ActiveRecord = i
'Utilisation de un à quatre champs pour obtenir le nom du document
If .DataSource.DataFields(w1).Value = "" Then
ActiveDocument.Close SaveChanges:=wdDoNotSaveChanges
Exit For
End If
DocName = .DataSource.DataFields(w1).Value
If w2 > 0 Then
DocName = DocName & TextBox3.Text & .DataSource.DataFields(w2).Value
If w3 > 0 Then
DocName = DocName & TextBox3.Text & .DataSource.DataFields(w3).Value
If w4 > 0 Then
DocName = DocName & TextBox3.Text & .DataSource.DataFields(w4).Value
End If
End If
End If
If DocName = "" Then
ActiveDocument.Close SaveChanges:=wdDoNotSaveChanges
'
Exit For
Else
End If
Debug.Print DocName; i
End With
' Sauvegarde du document publiposté
Application.DisplayAlerts = False
With ActiveDocument
ActiveDocument.CommandButton1.Width = 1
ActiveDocument.CommandButton1.Height = 1
If Len(TextBox2.Text) > 0 Then
.ExportAsFixedFormat OutputFileName:=TextBox1.Text & TextBox2.Text & TextBox3.Text & DocName & ".pdf", \_
ExportFormat:=wdExportFormatPDF, OpenAfterExport:=False
.Close SaveChanges:=wdDoNotSaveChanges
Application.DisplayAlerts = True
Else
.ExportAsFixedFormat OutputFileName:=TextBox1.Text & DocName & ".pdf", \_
ExportFormat:=wdExportFormatPDF, OpenAfterExport:=False
.Close SaveChanges:=wdDoNotSaveChanges
Application.DisplayAlerts = True
End If
End With
Next i
ActiveDocument.Close SaveChanges:=wdDoNotSaveChanges
Application.DisplayAlerts = False
End Sub
Private Sub doc1()
' Déclaration des variables
Dim iR As Integer
Dim i As Integer
Dim oDoc As Document
Dim DocName As String
Dim export As String
Dim extendion As String
Dim oDS As MailMergeDataSource
UserForm1.hide
CommandButton2.Enabled = False
' disparition bouton activex
w1 = ComboBox1.ListIndex
w2 = ComboBox2.ListIndex
w3 = ComboBox3.ListIndex
w4 = ComboBox4.ListIndex
' Affectation des objets
Set oDoc = ActiveDocument
Set oDS = oDoc.MailMerge.DataSource
iR = oDoc.MailMerge.DataSource.RecordCount
Debug.Print iR
For i = 1 To iR
With oDoc.MailMerge
'Définition du premier et dernier enregistrement
.DataSource.FirstRecord = i
.DataSource.LastRecord = i
' Envoi des données dans un nouveau document
.Destination = wdSendToNewDocument
' Exécution du publipostage
.Execute
' Actualisation de l'enregistrement pour la sauvegarde
.DataSource.ActiveRecord = i
'Utilisation de un à quatre champs pour obtenir le nom du document
If .DataSource.DataFields(w1).Value = "" Then
ActiveDocument.Close SaveChanges:=wdDoNotSaveChanges
Exit For
End If
DocName = .DataSource.DataFields(w1).Value
If w2 > 0 Then
DocName = DocName & TextBox3.Text & .DataSource.DataFields(w2).Value
If w3 > 0 Then
DocName = DocName & TextBox3.Text & .DataSource.DataFields(w3).Value
If w4 > 0 Then
DocName = DocName & TextBox3.Text & .DataSource.DataFields(w4).Value
End If
End If
End If
If DocName = "" Then
ActiveDocument.Close SaveChanges:=wdDoNotSaveChanges
'
Exit For
Else
End If
Debug.Print DocName; i
End With
' Sauvegarde du document publiposté
Application.DisplayAlerts = False
With ActiveDocument
ActiveDocument.CommandButton1.Width = 1
ActiveDocument.CommandButton1.Height = 1
If Len(TextBox2.Text) > 0 Then
.SaveAs2 FileName:=TextBox1.Text & TextBox2.Text & TextBox3.Text & DocName & ".doc", \_
FileFormat:=wdFormatDocument
.Close SaveChanges:=wdDoNotSaveChanges
Application.DisplayAlerts = True
Else
.SaveAs2 FileName:=TextBox1.Text & DocName & ".doc", \_
FileFormat:=wdFormatDocument
.Close SaveChanges:=wdDoNotSaveChanges
Application.DisplayAlerts = True
End If
End With
Next i
ActiveDocument.Close SaveChanges:=wdDoNotSaveChanges
Application.DisplayAlerts = False
End Sub
Private Sub docx1()
' Déclaration des variables
Dim iR As Integer
Dim i As Integer
Dim oDoc As Document
Dim DocName As String
Dim export As String
Dim extendion As String
Dim oDS As MailMergeDataSource
UserForm1.hide
CommandButton2.Enabled = False
' disparition bouton activex
w1 = ComboBox1.ListIndex
w2 = ComboBox2.ListIndex
w3 = ComboBox3.ListIndex
w4 = ComboBox4.ListIndex
' Affectation des objets
Set oDoc = ActiveDocument
Set oDS = oDoc.MailMerge.DataSource
iR = oDoc.MailMerge.DataSource.RecordCount
Debug.Print iR
For i = 1 To iR
With oDoc.MailMerge
'Définition du premier et dernier enregistrement
.DataSource.FirstRecord = i
.DataSource.LastRecord = i
' Envoi des données dans un nouveau document
.Destination = wdSendToNewDocument
' Exécution du publipostage
.Execute
' Actualisation de l'enregistrement pour la sauvegarde
.DataSource.ActiveRecord = i
'Utilisation de un à quatre champs pour obtenir le nom du document
If .DataSource.DataFields(w1).Value = "" Then
ActiveDocument.Close SaveChanges:=wdDoNotSaveChanges
Exit For
End If
DocName = .DataSource.DataFields(w1).Value
If w2 > 0 Then
DocName = DocName & TextBox3.Text & .DataSource.DataFields(w2).Value
If w3 > 0 Then
DocName = DocName & TextBox3.Text & .DataSource.DataFields(w3).Value
If w4 > 0 Then
DocName = DocName & TextBox3.Text & .DataSource.DataFields(w4).Value
End If
End If
End If
If DocName = "" Then
ActiveDocument.Close SaveChanges:=wdDoNotSaveChanges
'
Exit For
Else
End If
Debug.Print DocName; i
End With
' Sauvegarde du document publiposté
Application.DisplayAlerts = False
With ActiveDocument
ActiveDocument.CommandButton1.Width = 1
ActiveDocument.CommandButton1.Height = 1
If Len(TextBox2.Text) > 0 Then
.SaveAs2 FileName:=TextBox1.Text & TextBox2.Text & TextBox3.Text & DocName & ".docx", \_
FileFormat:=wdFormatXMLDocument
.Close SaveChanges:=wdDoNotSaveChanges
Application.DisplayAlerts = True
Else
.SaveAs2 FileName:=TextBox1.Text & DocName & ".docx", \_
FileFormat:=wdFormatXMLDocument
.Close SaveChanges:=wdDoNotSaveChanges
Application.DisplayAlerts = True
End If
End With
Next i
ActiveDocument.Close SaveChanges:=wdDoNotSaveChanges
Application.DisplayAlerts = False
End Sub
Private Sub Frame3_Click()
End Sub
Private Sub Frame4_Click()
End Sub
Private Sub Frame6_Click()
End Sub
Private Sub Label1_Click()
End Sub
Private Sub TextBox1_Change()
End Sub
Private Sub TextBox1_Enter()
Dim fd As Office.FileDialog
Set fd = Application.FileDialog(msoFileDialogFolderPicker)
With fd
.InitialFileName = "C:"
.Show
TextBox1 = fd.SelectedItems(1) & ""
End With
End Sub
Private Sub TextBox2_Change()
If Len(TextBox2.Text) = 0 Then
If ComboBox4.Enabled = True And Len(ComboBox4.Text) > 0 Then
Label1.Caption = ComboBox1.Text & TextBox3.Text & ComboBox2.Text & TextBox3.Text & ComboBox3.Text & TextBox3.Text & ComboBox4.Text
Else
If ComboBox4.Enabled = True And Len(ComboBox4.Text) = 0 Then
Label1.Caption = ComboBox1.Text & TextBox3.Text & ComboBox2.Text & TextBox3.Text & ComboBox3.Text
Else
If ComboBox3.Enabled = True And Len(ComboBox3.Text) > 0 Then
Label1.Caption = ComboBox1.Text & TextBox3.Text & ComboBox2.Text & TextBox3.Text & ComboBox3.Text
Else
If ComboBox3.Enabled = True And Len(ComboBox3.Text) = 0 Then
Label1.Caption = ComboBox1.Text & TextBox3.Text & ComboBox2.Text
Else
If ComboBox2.Enabled = True And Len(ComboBox2.Text) > 0 Then
Label1.Caption = ComboBox1.Text & TextBox3.Text & ComboBox2.Text
Else
If ComboBox2.Enabled = True And Len(ComboBox2.Text) = 0 Then
Label1.Caption = ComboBox1.Text
Else
If ComboBox1.Enabled = True And Len(ComboBox1.Text) > 0 Then
Label1.Caption = ComboBox1.Text
End If
End If
End If
End If
End If
End If
End If
End If
If Len(TextBox2.Text) > 0 Then
If ComboBox4.Enabled = True And Len(ComboBox4.Text) > 0 Then
Label1.Caption = TextBox2.Text & TextBox3.Text & ComboBox1.Text & TextBox3.Text & ComboBox2.Text & TextBox3.Text & ComboBox3.Text & TextBox3.Text & ComboBox4.Text
Else
If ComboBox4.Enabled = True And Len(ComboBox4.Text) = 0 Then
Label1.Caption = TextBox2.Text & TextBox3.Text & ComboBox1.Text & TextBox3.Text & ComboBox2.Text & TextBox3.Text & ComboBox3.Text
Else
If ComboBox3.Enabled = True And Len(ComboBox3.Text) > 0 Then
Label1.Caption = TextBox2.Text & TextBox3.Text & ComboBox1.Text & TextBox3.Text & ComboBox2.Text & TextBox3.Text & ComboBox3.Text
Else
If ComboBox3.Enabled = True And Len(ComboBox3.Text) = 0 Then
Label1.Caption = TextBox2.Text & TextBox3.Text & ComboBox1.Text & TextBox3.Text & ComboBox2.Text
Else
If ComboBox2.Enabled = True And Len(ComboBox2.Text) > 0 Then
Label1.Caption = TextBox2.Text & TextBox3.Text & ComboBox1.Text & TextBox3.Text & ComboBox2.Text
Else
If ComboBox2.Enabled = True And Len(ComboBox2.Text) = 0 Then
Label1.Caption = TextBox2.Text & TextBox3.Text & ComboBox1.Text
Else
If ComboBox1.Enabled = True And Len(ComboBox1.Text) > 0 Then
Label1.Caption = TextBox2.Text & TextBox3.Text & ComboBox1.Text
End If
End If
End If
End If
End If
End If
End If
End If
End Sub
Private Sub TextBox3_Change()
Call TextBox2_Change
End Sub
Private Sub TextBox3_Enter()
End Sub
Private Sub UserForm_Activate()
End Sub
Private Sub UserForm_Click()
End Sub
Private Sub UserForm_Initialize()
Dim i As Integer
Dim oDoc As Document
Dim oDS As MailMergeDataSource
Set oDoc = ActiveDocument
Set oDS = oDoc.MailMerge.DataSource
w1 = 0
w2 = 0
w3 = 0
w4 = 0
i = 1
doc.Enabled = True
docx.Enabled = True
pdf.Enabled = True
pdf = True
debut = True
debut.Visible = False
fin.Visible = False
ComboBox1.AddItem ""
ComboBox2.AddItem ""
ComboBox3.AddItem ""
ComboBox4.AddItem ""
champ = ActiveDocument.MailMerge.DataSource.DataFields(1).Name
While i < 10 And Len(champ) > 0
champ = ActiveDocument.MailMerge.DataSource.DataFields(i).Name
ComboBox1.AddItem champ
ComboBox2.AddItem champ
ComboBox3.AddItem champ
ComboBox4.AddItem champ
i = i + 1
Wend
ComboBox2.Enabled = False
ComboBox3.Enabled = False
ComboBox4.Enabled = False
CommandButton2.Enabled = False
TextBox1.Text = ""
TextBox2.Text = ""
End Sub
Private Sub remplacement()
For i = 1 To 10
Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting
With Selection.Find
.Text = ", , "
.Replacement.Text = ", "
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
Selection.Find.Execute Replace:=wdReplaceAll
Next
Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting
With Selection.Find
.Text = "//"
.Replacement.Text = ""
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
Selection.Find.Execute Replace:=wdReplaceAll
Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting
With Selection.Find
.Text = "//, "
.Replacement.Text = ""
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
Selection.Find.Execute Replace:=wdReplaceAll
Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting
With Selection.Find
.Text = "//"
.Replacement.Text = ""
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
Selection.Find.Execute Replace:=wdReplaceAll
Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting
With Selection.Find
.Text = " , "
.Replacement.Text = " "
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
Selection.Find.Execute Replace:=wdReplaceAll
Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting
With Selection.Find
.Text = ", ."
.Replacement.Text = "."
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
Selection.Find.Execute Replace:=wdReplaceAll
End Sub