Discussion :

[PATDRO]

Bonjour aux experts .

Lorsque j'exécute le code ci-dessous, j'obtiens l'erreur 9 quand je tape annul sur une première sélection de fichier pour en sélectionner une 2ème.
le fichier se recopie bien mais en fin de traitement l'erreur 9 survient et mon fichier programme se ferme sans s'enregistrer.
Par avance, merci de votre aide

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
Option Explicit
 
Sub Affect_email()
 
' declaration de variables
 
    Dim mDer As Long, i As Long
    Dim mMail As String
    Dim stFile As String
    Dim mIc As String
    Dim mChemin As Long
    Dim Reponse As VbMsgBoxResult
 
 
' déclaration prévention des erreurs lors exécution
    On Error GoTo Compl_Fx_err
 
    'Effacement complet de la feuille Echeances_contrats
 
    ActiveWorkbook.Sheets("Echeances_contrats").Select
    Cells.Select
    Selection.Clear
 
    'Selection fichier via fenetre windows ouvrir
    stFile = Application.GetOpenFilename
    Reponse = MsgBox("Fichier sélectionné = " & stFile, vbQuestion + vbOKCancel, "Traitement fichier source")
 
    If Reponse = 1 Then
 
    Workbooks.Open (stFile)
 
    Else: Affect_email      ' Si choix ANNUL retour en début de procédure >>> Cela provoque le code 9 en fin de traitement
 
    End If
 
 
    'copie de la feuille complete dans la feuille de destination Echeances_contrats
 
    Range("A1").Select
    Range(Selection.End(xlToRight), Selection.End(xlDown)).Copy
 
 
    'Selection du fichier de destination et collage
 
    Workbooks("Avis_Fctr_V2_4_DP.xls").Activate
    ActiveWorkbook.Sheets("Echeances_contrats").Select
    Range("A1").PasteSpecial
 
 
 ' positionnement sur la feuille Mail_cli
    ActiveWorkbook.Sheets("Mail_cli").Select
 
 ' repérage du tableau complet en dynamique pour nommage nom:complet
' la zone nommée servira ensuite pour établir la Fx Recherchev
    Range("A2").Select
    ActiveCell.CurrentRegion.Select
 
    ActiveWorkbook.Names.Add Name:="Complet", RefersToR1C1:=Selection
 
 ' retour sur A1 sur
    Range("A1").Select
 
' positionnement sur la feuille Echeances_contrats
 
    ActiveWorkbook.Sheets("Echeances_contrats").Select
 
' repérage dernière ligne renseignée sur Echeances_contrats
 
    mDer = ActiveSheet.UsedRange.Rows.Count
 
' nettoyage des cellules destinées aux calculs
    Range("O2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.ClearContents
 
' complément formules de calcul dans Echeances_contrats
    Range("I2").Select
    For i = 2 To mDer
        Cells(i, 9).Select  'positionnement dynamique sur numéro de client dans Echeances_contrats
 
        ActiveCell.Offset(0, 6).Formula = Application.WorksheetFunction.VLookup(Selection, Range("Complet"), 4, False)      ' recherche et écriture mail client
        ActiveCell.Offset(0, 7).Formula = Application.WorksheetFunction.VLookup(Selection, Range("Complet"), 5, False)      ' recherche et écriture nom IC
        ActiveCell.Offset(0, 8).Formula = Application.WorksheetFunction.VLookup(Selection, Range("Complet"), 6, False)      ' recherche et écriture mail IC
        ActiveCell.Offset(0, 9).Formula = Application.WorksheetFunction.VLookup(Selection, Range("Complet"), 7, False)      ' recherche et écriture tel IC
 
 
    Next
 
    ' message fin de traitement
    MsgBox "Le tableau fins de contrats a été complété des adresses mail clients.", vbInformation + vbOKOnly, "Fin de traitement"
 
 
 
    'Ecriture entête cellule O1 P1 Q1 R1 S1
    Range("O1").Select
    ActiveCell.Formula = "Courriel client"
    Range("P1").Select
    ActiveCell.Formula = "Nom IC"
    Range("Q1").Select
    ActiveCell.Formula = "Courriels IC"
    Range("R1").Select
    ActiveCell.Formula = "Tel IC"
    Range("S1").Select
    ActiveCell.Formula = "Commentaires manuels"
 
 
 
    'mise en forme du tableau justification colonnes
    Range("A1:S1").Font.Bold = True
    ActiveSheet.Columns("A:S").Select
    'selection.
    Selection.EntireColumn.AutoFit
 
 
 
    'retour sur A1 sur
    Range("O2").Select
 
    'FERMETURE DU FICHIER SELECTIONNE stFile
    mChemin = Len(CurDir) + 2
    stFile = Mid(stFile, mChemin, 50)        ' permet de selectionner le seul nom du fichier avant de le fermer
    Workbooks(stFile).Activate
    ActiveWorkbook.Close False
 
 
    Exit Sub
 
' gestion des erreurs rencntrées lors exécution
Compl_Fx_err:
 
 
' affiche le message erreur détectée (Mail non trouvé)
    Select Case Err.Number
        Case 1004
             'MsgBox mMail & " ... INCONNU DANS LA BASE MAIL CLIENTS "
             ActiveCell.Offset(0, 6).Formula = "INCONNU - A renseigner dans la base mail"
             ActiveCell.Offset(0, 6).Font.ColorIndex = 3
        Case Else
 
             MsgBox Err.Description
 
    End Select
 
' poursuite exécution de la procédure
    Resume Next
 
End Sub

[mercatog]

C'est évident que tu auras de tels ennuis avec tes Select et Activemachin.
Tu travailles avec plusieurs classeurs et plusieurs feuilles. Utilises des variables objets sans sélectionner ni activer quoi que ce soit.

Un range doit absolument dans ce cas indiquer la feuille mère à laquelle il appartient. La même feuille devra indiquer à quel classeur elle appartient.
de ce fait au lieu de:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
ClasseurA.Activate
FeuilleX.select
RangeP.Copy
ClasseurB.Activate
FeuilleY.Select
RangeQ.PasteSpecial

tu affecte directement comme ceci

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
ClasseurA.FeuilleX.RangeP.Copy
ClasseurB.FeuilleY.RangeQ.PasteSpecial

Dans ton cas (pour le début), au lieu de ceci:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
Range("A1").Select
Range(Selection.End(xlToRight), Selection.End(xlDown)).Copy
 
 
'Selection du fichier de destination et collage
 
Workbooks("Avis_Fctr_V2_4_DP.xls").Activate
ActiveWorkbook.Sheets("Echeances_contrats").Select
Range("A1").PasteSpecial

Fais ceci

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
Dim Wbk As Workbook
Dim Ws As Worksheet
'...........
'copie de la feuille complete dans la feuille de destination Echeances_contrats
Set Ws = ThisWorkbook.Worksheets("Echeances_contrats")
Set Wbk = Workbooks.Open(stFile)
 
Wbk.Worksheets(1).UsedRange.Copy
Ws.Range("A1").PasteSpecial xlPasteValues
Application.CutCopyMode = False

Tu es certain de copier la plage utilisée de la 1ère feuille du classeur StFile vers la feuille Echeances_contrats du classeur contenant la macro Sans se soucier quel est la classeur actif ou quelle est la feuille active et sans activer quoi que ce soit. C'est la philosophie même des objets en programmation.

A toi maintenant d'assainir ton code. Tu auras franchi un grand pas lorsque ton code final ne contiendra aucun Select/ Activate/ Activetruc/ Selection...

[PATDRO]

Merci pour ces conseils avisés, je vais suivre vos conseils rapidement.
Je débute en VBA et je n'ai semble t il pas encore compris la meilleure méthodologie de développement en langage objet.
Développeur n'est pas mon métier mais une passion, j'essaye de me former mais jusqu'ici je dois dire que je suis un peu éçu par les intervenants.

Cordialement

Patrice

J'ai testé la partie de code que vous m'avez proposé mais en ne modifiant que cette partie, c'est pire, plus rien ne fonctionne, j'obtiens ne nombreuse erreurs 9, fallait il revoir complétement ma programmation?

[mercatog]

Mets ton nouveau code en entier ici afin de pouvoir te guider

[PATDRO]

Voici mon code en entier, encore merci pour votre aide !
-------------------------------------------------------------------------

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
Option Explicit
 
Sub Affect_email()
 
' declaration de variables
 
    Dim mDer As Long, i As Long
    Dim mMail As String
    Dim stFile As String
    Dim mIc As String
    Dim mChemin As Long
    Dim Reponse As VbMsgBoxResult
    Dim Wbk As Workbook
    Dim Ws As Worksheet
 
' déclaration prévention des erreurs lors exécution
    On Error GoTo Compl_Fx_err
 
    'Effacement complet de la feuille Echeances_contrats
 
    ActiveWorkbook.Sheets("Echeances_contrats").Select
    Cells.Select
    Selection.Clear
 
    'Selection fichier via fenetre windows ouvrir
    stFile = Application.GetOpenFilename
    Reponse = MsgBox("Fichier sélectionné = " & stFile, vbQuestion + vbOKCancel, "Traitement fichier source")
 
    If Reponse = 1 Then
 
    Workbooks.Open (stFile)
 
    Else: Affect_email      ' Si choix ANNUL retour en début de procédure >>> Cela provoque le code 9 en fin de traitement
 
    End If
 
 
    'copie de la feuille complete dans la feuille de destination Echeances_contrats
    Set Ws = ThisWorkbook.Worksheets("Echeances_contrats")
    Set Wbk = Workbooks.Open(stFile)
 
    Wbk.Worksheets(1).UsedRange.Copy
    Ws.Range("A1").PasteSpecial
    Application.CutCopyMode = False
 
 ' positionnement sur la feuille Mail_cli
    ActiveWorkbook.Sheets("Mail_cli").Select
 
    ' repérage du tableau complet en dynamique pour nommage nom:complet
    ' la zone nommée servira ensuite pour établir la Fx Recherchev
    Range("A2").Select
    ActiveCell.CurrentRegion.Select
 
    ActiveWorkbook.Names.Add Name:="Complet", RefersToR1C1:=Selection
 
 ' retour sur A1 sur
    Range("A1").Select
 
' positionnement sur la feuille Echeances_contrats
 
    ActiveWorkbook.Sheets("Echeances_contrats").Select
 
' repérage dernière ligne renseignée sur Echeances_contrats
 
    mDer = ActiveSheet.UsedRange.Rows.Count
 
' nettoyage des cellules destinées aux calculs
    Range("O2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.ClearContents
 
' complément formules de calcul dans Echeances_contrats
    Range("I2").Select
    For i = 2 To mDer
        Cells(i, 9).Select  'positionnement dynamique sur numéro de client dans Echeances_contrats
 
        ActiveCell.Offset(0, 6).Formula = Application.WorksheetFunction.VLookup(Selection, Range("Complet"), 4, False)      ' recherche et écriture mail client
        ActiveCell.Offset(0, 7).Formula = Application.WorksheetFunction.VLookup(Selection, Range("Complet"), 5, False)      ' recherche et écriture nom IC
        ActiveCell.Offset(0, 8).Formula = Application.WorksheetFunction.VLookup(Selection, Range("Complet"), 6, False)      ' recherche et écriture mail IC
        ActiveCell.Offset(0, 9).Formula = Application.WorksheetFunction.VLookup(Selection, Range("Complet"), 7, False)      ' recherche et écriture tel IC
 
 
    Next
 
    ' message fin de traitement
    MsgBox "Le tableau fins de contrats a été complété des adresses mail clients.", vbInformation + vbOKOnly, "Fin de traitement"
 
 
 
    'Ecriture entête cellule O1 P1 Q1 R1 S1
    Range("O1").Select
    ActiveCell.Formula = "Courriel client"
    Range("P1").Select
    ActiveCell.Formula = "Nom IC"
    Range("Q1").Select
    ActiveCell.Formula = "Courriels IC"
    Range("R1").Select
    ActiveCell.Formula = "Tel IC"
    Range("S1").Select
    ActiveCell.Formula = "Commentaires manuels"
 
 
 
    'mise en forme du tableau justification colonnes
    Range("A1:S1").Font.Bold = True
    ActiveSheet.Columns("A:S").Select
    'selection.
    Selection.EntireColumn.AutoFit
 
 
 
    'retour sur A1 sur
    Range("O2").Select
 
    'FERMETURE DU FICHIER SELECTIONNE stFile
    mChemin = Len(CurDir) + 2
    stFile = Mid(stFile, mChemin, 50)        ' permet de selectionner le seul nom du fichier avant de le fermer
    Workbooks(stFile).Activate
    ActiveWorkbook.Close False
 
 
    Exit Sub
 
' gestion des erreurs rencntrées lors exécution
Compl_Fx_err:
 
 
' affiche le message erreur détectée (Mail non trouvé)
    Select Case Err.Number
        Case 1004
             'MsgBox mMail & " ... INCONNU DANS LA BASE MAIL CLIENTS "
             ActiveCell.Offset(0, 6).Formula = "INCONNU - A renseigner dans la base mail"
             ActiveCell.Offset(0, 6).Font.ColorIndex = 3
        Case Else
 
             MsgBox Err.Description
 
    End Select
 
' poursuite exécution de la procédure
    Resume Next
 
End Sub

[mercatog]

Je te suggère d'arrêter dès maintenant de copier des bouts de codes sans les étudier. Je t'avais donné un exemple pour que tu puisse refaire ton code en entier à la place de ce que tu avais.
Si tu n'as pas compris, tu n'as qu'à demander

Pour le début de ton code, remplace le tiens par celui là après avoir appréhendé le sens

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
 
'ICI DEBUT DECLARATION DES VARIABLES
' déclaration prévention des erreurs lors exécution
On Error GoTo Compl_Fx_err
'Effacement complet de la feuille Echeances_contrats
ThisWorkbook.Sheets("Echeances_contrats").UsedRange.Clear
'Selection fichier via fenetre windows ouvrir
stFile = Application.GetOpenFilename
Reponse = MsgBox("Fichier sélectionné = " & stFile, vbQuestion + vbOKCancel, "Traitement fichier source")
 
If Reponse = 1 Then
    Set Wbk = Workbooks.Open(stFile)
Else
    Affect_email                                   '??? Si choix ANNUL retour en début de procédure >>> Cela provoque le code 9 en fin de traitement
End If
 
'copie de la feuille complète dans la feuille de destination Echeances_contrats
Wbk.Worksheets(1).UsedRange.Copy
Ws.Range("A1").PasteSpecial
Application.CutCopyMode = False
 
' positionnement sur la feuille Mail_cli
'>>>>La feuille Mail_Cli appartient à quel classeur???? Tu dois répondre à cette question pour pouvoir soit faire:
'Wbk.Names.Add Name:="Complet", RefersToR1C1:=Wbk.Worksheets("Mail_cli").Range("A2").CurrentRegion
'si elle appartient au classeur stFile ou
'ThisWorkbook.Names.Add Name:="Complet", RefersToR1C1:=thisworkbook.Worksheets("Mail_cli").Range("A2").CurrentRegion
'si elle apprtient au classeur contenant la macro
ActiveWorkbook.Sheets("Mail_cli").Select

A toi maintenant d'assainir ton code. Tu auras franchi un grand pas lorsque ton code final ne contiendra aucun Select/ Activate/ Activetruc/ Selection...

[PATDRO]

Merci de me guider ainsi et je constate que je n'ai pas encore acquit la bonne méthodologie, j'ai fait en tout et pour tout 4 jrs de formation sur VBA et je crois que cela ne suffit pas.
J'essaye de revoir mon code complètement.

Cordialement

[PATDRO]

J'ai essayé de comprendre votre proposition de code mais je ne comprends pas comment est affectée la variable Ws ?

[mercatog]

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Set Ws = ThisWorkbook.Worksheets("Echeances_contrats")