Discussion :

[geoffrey.pfirsch]

Bonjour à tous,

Une macro qui a été créée sous Excel 2007, et qui s'exécute normalement sous cette même version prend un temps infini sur les versions supérieures Excel 2013/2016.

Je suis condamné a utilisé Excel 2007 pour exécuter la macro mais ça n'est pas une solution à long terme.

Avez-vous une idée pour savoir quoi faire pour lancer la macro sur les dernières versions d'Excel, sans attendre éternellement que la macro soit exécutée ?

Pour information, la macro compare les données d'un autre fichier Excel pour finalement en créer un nouveau avec les données remplacé et actualisé.

Merci pour vos réponses,

Cordialement,

Voici la macro en question :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
'File
Dim SIMFILE As String 'Name of the SIM File
Dim EXTRACTFILE As String 'Name of the Extract file
'Sheet
Dim SIMSHEET As String 'Name of the SIM's sheet
Dim EXTRACTSHEET As String 'Name of the extract's sheet
Dim MODIFICATIONSHEET As String ' Name of the modification sheet
Dim SUPPRESSIONSHEET As String 'Name of the deleted row sheet
'Index
Dim MODIFICATION As Integer 'Index of the last modification in the sheet "Modifications"
Dim SUPPRIMEE As Integer 'Index of the last suppression in the sheet "Deleted rows"
'MPM
Dim MPM As String

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
Function InsertionSub(EXTRACT As Integer, SIM As Integer)
    'Creation of the new line
    Workbooks(SIMFILE).Worksheets(SIMSHEET).Select
    Workbooks(SIMFILE).Worksheets(SIMSHEET).Rows(SIM & ":" & SIM).Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    If SIM = 4 Then
        Workbooks(SIMFILE).Worksheets(SIMSHEET).Rows(SIM + 1 & ":" & SIM + 1).Select
        Selection.Copy
        Workbooks(SIMFILE).Worksheets(SIMSHEET).Rows(SIM & ":" & SIM).Select
        Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    End If
 
    Workbooks(EXTRACTFILE).Sheets(EXTRACTSHEET).Activate
    Workbooks(EXTRACTFILE).Sheets(EXTRACTSHEET).Range(Workbooks(EXTRACTFILE).Sheets(EXTRACTSHEET).Cells(EXTRACT, 1).address(RowAbsolute:=False, ColumnAbsolute:=False) & ":" & Workbooks(EXTRACTFILE).Sheets(EXTRACTSHEET).Cells(EXTRACT, 51).address(RowAbsolute:=False, ColumnAbsolute:=False)).Select
    Selection.Copy
    Workbooks(SIMFILE).Sheets(SIMSHEET).Activate
    Workbooks(SIMFILE).Sheets(SIMSHEET).Cells(SIM, 2).Select
            'ActiveSheet.Paste
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Workbooks(SIMFILE).Sheets(SIMSHEET).Cells(SIM, 1).Value = 0
 
    'Update de l'onglet modifications
    Workbooks(SIMFILE).Sheets(MODIFICATIONSHEET).Cells(MODIFICATION, 1).Value = "Ajout"
    Workbooks(SIMFILE).Sheets(MODIFICATIONSHEET).Cells(MODIFICATION, 2).Value = Workbooks(SIMFILE).Sheets(SIMSHEET).Cells(SIM, 2).Value
    Workbooks(SIMFILE).Sheets(MODIFICATIONSHEET).Cells(MODIFICATION, 3).Value = Workbooks(SIMFILE).Sheets(SIMSHEET).Cells(SIM, 7).Value
    Workbooks(SIMFILE).Sheets(MODIFICATIONSHEET).Cells(MODIFICATION, 4).Value = Workbooks(SIMFILE).Sheets(SIMSHEET).Cells(SIM, 4).Value
    Workbooks(SIMFILE).Sheets(MODIFICATIONSHEET).Cells(MODIFICATION, 5).Value = Workbooks(SIMFILE).Sheets(SIMSHEET).Cells(SIM, 48).Value
    Workbooks(SIMFILE).Sheets(MODIFICATIONSHEET).Cells(MODIFICATION, 6).Value = Workbooks(SIMFILE).Sheets(SIMSHEET).Cells(SIM, 29).Value
    Workbooks(SIMFILE).Sheets(MODIFICATIONSHEET).Cells(MODIFICATION, 7).Value = Workbooks(SIMFILE).Sheets(SIMSHEET).Cells(SIM, 34).Value
    Workbooks(SIMFILE).Sheets(MODIFICATIONSHEET).Cells(MODIFICATION, 8).Value = Workbooks(SIMFILE).Sheets(SIMSHEET).Cells(SIM, 51).Value
    Workbooks(SIMFILE).Sheets(MODIFICATIONSHEET).Cells(MODIFICATION, 9).Value = Workbooks(SIMFILE).Sheets(SIMSHEET).Cells(SIM, 19).Value
    Workbooks(SIMFILE).Sheets(MODIFICATIONSHEET).Cells(MODIFICATION, 34).Value = 0
    Workbooks(SIMFILE).Sheets(MODIFICATIONSHEET).Select
    Range("AI" & MODIFICATION).Select
    ActiveCell.FormulaR1C1 = _
        "=SUM(VLOOKUP(RC2,INDIRECT(R1C30&R1C31),{66;67},FALSE))"
    Range("AJ" & MODIFICATION).Select
    ActiveCell.FormulaR1C1 = "=RC[-1]-RC[-2]"
    MODIFICATION = MODIFICATION + 1
End Function

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
Sub SuppressionSub(SIM As Integer)
    'Suppression of the old line
    Workbooks(SIMFILE).Sheets(MODIFICATIONSHEET).Cells(MODIFICATION, 1).Value = "Suppression"
    Workbooks(SIMFILE).Sheets(MODIFICATIONSHEET).Cells(MODIFICATION, 2).Value = Workbooks(SIMFILE).Sheets(SIMSHEET).Cells(SIM, 2).Value
    Workbooks(SIMFILE).Sheets(MODIFICATIONSHEET).Cells(MODIFICATION, 3).Value = Workbooks(SIMFILE).Sheets(SIMSHEET).Cells(SIM, 7).Value
    Workbooks(SIMFILE).Sheets(MODIFICATIONSHEET).Cells(MODIFICATION, 4).Value = Workbooks(SIMFILE).Sheets(SIMSHEET).Cells(SIM, 4).Value
    Workbooks(SIMFILE).Sheets(MODIFICATIONSHEET).Cells(MODIFICATION, 5).Value = Workbooks(SIMFILE).Sheets(SIMSHEET).Cells(SIM, 48).Value
    Workbooks(SIMFILE).Sheets(MODIFICATIONSHEET).Cells(MODIFICATION, 6).Value = Workbooks(SIMFILE).Sheets(SIMSHEET).Cells(SIM, 29).Value
    Workbooks(SIMFILE).Sheets(MODIFICATIONSHEET).Cells(MODIFICATION, 7).Value = Workbooks(SIMFILE).Sheets(SIMSHEET).Cells(SIM, 34).Value
    Workbooks(SIMFILE).Sheets(MODIFICATIONSHEET).Cells(MODIFICATION, 8).Value = Workbooks(SIMFILE).Sheets(SIMSHEET).Cells(SIM, 51).Value
    Workbooks(SIMFILE).Sheets(MODIFICATIONSHEET).Cells(MODIFICATION, 9).Value = Workbooks(SIMFILE).Sheets(SIMSHEET).Cells(SIM, 19).Value
    Workbooks(SIMFILE).Sheets(MODIFICATIONSHEET).Cells(MODIFICATION, 34).Value = Workbooks(SIMFILE).Sheets(SIMSHEET).Cells(SIM, 67).Value + Workbooks(SIMFILE).Sheets(SIMSHEET).Cells(SIM, 68).Value
    Workbooks(SIMFILE).Sheets(MODIFICATIONSHEET).Select
    Range("AI" & MODIFICATION).Value = 0
    Range("AJ" & MODIFICATION).Select
    ActiveCell.FormulaR1C1 = "=RC[-1]-RC[-2]"
 
    MODIFICATION = MODIFICATION + 1
 
    Workbooks(SIMFILE).Worksheets(SIMSHEET).Select
    Workbooks(SIMFILE).Worksheets(SIMSHEET).Rows(SIM & ":" & SIM).Select
    Selection.Copy
    Workbooks(SIMFILE).Sheets(SUPPRESSIONSHEET).Select
    Rows(SUPPRIMEE & ":" & SUPPRIMEE).Select
    ActiveSheet.Paste
    Range("A" & SUPPRIMEE).Select
    ActiveCell.FormulaR1C1 = "=TODAY()"
    Range("A" & SUPPRIMEE).Select
    Selection.Copy
    Range("A" & SUPPRIMEE).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    SUPPRIMEE = SUPPRIMEE + 1
    Workbooks(SIMFILE).Worksheets(SIMSHEET).Activate
    Workbooks(SIMFILE).Worksheets(SIMSHEET).Rows(SIM & ":" & SIM).Select
    Selection.Delete Shift:=xlUp
End Sub

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
Function ModficationSub(EXTRACT As Integer, SIM As Integer)
    'Search changes
    Dim Changes As String
    Changes = ""
    'TODO Check and update modification SHEET
    If Workbooks(EXTRACTFILE).Sheets(EXTRACTSHEET).Cells(EXTRACT, 6).Value <> Workbooks(SIMFILE).Sheets(SIMSHEET).Cells(SIM, 7).Value Then
            Changes = Changes & "LEVER" & Chr(10)
            'Avant
            Workbooks(SIMFILE).Sheets(MODIFICATIONSHEET).Cells(MODIFICATION, 20).Value = Workbooks(SIMFILE).Sheets(SIMSHEET).Cells(SIM, 7).Value
            'Après
            Workbooks(SIMFILE).Sheets(MODIFICATIONSHEET).Cells(MODIFICATION, 32).Value = Workbooks(EXTRACTFILE).Sheets(EXTRACTSHEET).Cells(EXTRACT, 6).Value
        End If
        '
        If Workbooks(EXTRACTFILE).Sheets(EXTRACTSHEET).Cells(EXTRACT, 43).Value <> Workbooks(SIMFILE).Sheets(SIMSHEET).Cells(SIM, 44).Value Then
            Changes = Changes & "Delta in volume" & Chr(10)
            'Avant
            Workbooks(SIMFILE).Sheets(MODIFICATIONSHEET).Cells(MODIFICATION, 21).Value = Workbooks(SIMFILE).Sheets(SIMSHEET).Cells(SIM, 44).Value
            'Après
            Workbooks(SIMFILE).Sheets(MODIFICATIONSHEET).Cells(MODIFICATION, 33).Value = Workbooks(EXTRACTFILE).Sheets(EXTRACTSHEET).Cells(EXTRACT, 43).Value
        End If
        '
        If Workbooks(EXTRACTFILE).Sheets(EXTRACTSHEET).Cells(EXTRACT, 5).Value <> Workbooks(SIMFILE).Sheets(SIMSHEET).Cells(SIM, 6).Value Then
            Changes = Changes & "TEMPO ID" & Chr(10)
            'Avant
            Workbooks(SIMFILE).Sheets(MODIFICATIONSHEET).Cells(MODIFICATION, 10).Value = Workbooks(SIMFILE).Sheets(SIMSHEET).Cells(SIM, 6).Value
            'Après
            Workbooks(SIMFILE).Sheets(MODIFICATIONSHEET).Cells(MODIFICATION, 22).Value = Workbooks(EXTRACTFILE).Sheets(EXTRACTSHEET).Cells(EXTRACT, 5).Value
        End If
 
        If Workbooks(EXTRACTFILE).Sheets(EXTRACTSHEET).Cells(EXTRACT, 8).Value <> Workbooks(SIMFILE).Sheets(SIMSHEET).Cells(SIM, 9).Value Then
            Changes = Changes & "PHASE" & Chr(10)
            'Avant
            Workbooks(SIMFILE).Sheets(MODIFICATIONSHEET).Cells(MODIFICATION, 11).Value = Workbooks(SIMFILE).Sheets(SIMSHEET).Cells(SIM, 9).Value
            'Après
            Workbooks(SIMFILE).Sheets(MODIFICATIONSHEET).Cells(MODIFICATION, 23).Value = Workbooks(EXTRACTFILE).Sheets(EXTRACTSHEET).Cells(EXTRACT, 8).Value
        End If
 
        If Workbooks(EXTRACTFILE).Sheets(EXTRACTSHEET).Cells(EXTRACT, 12).Value <> Workbooks(SIMFILE).Sheets(SIMSHEET).Cells(SIM, 13).Value Then
            Changes = Changes & "Main Program" & Chr(10)
            'Avant
            Workbooks(SIMFILE).Sheets(MODIFICATIONSHEET).Cells(MODIFICATION, 12).Value = Workbooks(SIMFILE).Sheets(SIMSHEET).Cells(SIM, 13).Value
            'Après
            Workbooks(SIMFILE).Sheets(MODIFICATIONSHEET).Cells(MODIFICATION, 24).Value = Workbooks(EXTRACTFILE).Sheets(EXTRACTSHEET).Cells(EXTRACT, 12).Value
        End If
 
        If Workbooks(EXTRACTFILE).Sheets(EXTRACTSHEET).Cells(EXTRACT, 14).Value <> Workbooks(SIMFILE).Sheets(SIMSHEET).Cells(SIM, 15).Value Then
            Changes = Changes & "Product Line" & Chr(10)
            'Avant
            Workbooks(SIMFILE).Sheets(MODIFICATIONSHEET).Cells(MODIFICATION, 13).Value = Workbooks(SIMFILE).Sheets(SIMSHEET).Cells(SIM, 15).Value
            'Après
            Workbooks(SIMFILE).Sheets(MODIFICATIONSHEET).Cells(MODIFICATION, 25).Value = Workbooks(EXTRACTFILE).Sheets(EXTRACTSHEET).Cells(EXTRACT, 14).Value
        End If
 
        If Workbooks(EXTRACTFILE).Sheets(EXTRACTSHEET).Cells(EXTRACT, 25).Value <> Workbooks(SIMFILE).Sheets(SIMSHEET).Cells(SIM, 26).Value Then
            Changes = Changes & "End date" & Chr(10)
            'Avant
            Workbooks(SIMFILE).Sheets(MODIFICATIONSHEET).Cells(MODIFICATION, 14).Value = Workbooks(SIMFILE).Sheets(SIMSHEET).Cells(SIM, 26).Value
            'Après
            Workbooks(SIMFILE).Sheets(MODIFICATIONSHEET).Cells(MODIFICATION, 26).Value = Workbooks(EXTRACTFILE).Sheets(EXTRACTSHEET).Cells(EXTRACT, 25).Value
        End If
 
        If Workbooks(EXTRACTFILE).Sheets(EXTRACTSHEET).Cells(EXTRACT, 36).Value <> Workbooks(SIMFILE).Sheets(SIMSHEET).Cells(SIM, 37).Value Then
            Changes = Changes & "Manuf Country" & Chr(10)
            'Avant
            Workbooks(SIMFILE).Sheets(MODIFICATIONSHEET).Cells(MODIFICATION, 15).Value = Workbooks(SIMFILE).Sheets(SIMSHEET).Cells(SIM, 37).Value
            'Après
            Workbooks(SIMFILE).Sheets(MODIFICATIONSHEET).Cells(MODIFICATION, 27).Value = Workbooks(EXTRACTFILE).Sheets(EXTRACTSHEET).Cells(EXTRACT, 36).Value
        End If
 
        If Workbooks(EXTRACTFILE).Sheets(EXTRACTSHEET).Cells(EXTRACT, 37).Value <> Workbooks(SIMFILE).Sheets(SIMSHEET).Cells(SIM, 38).Value Then
            Changes = Changes & "P&L date" & Chr(10)
            'Avant
            Workbooks(SIMFILE).Sheets(MODIFICATIONSHEET).Cells(MODIFICATION, 16).Value = Workbooks(SIMFILE).Sheets(SIMSHEET).Cells(SIM, 38).Value
            'Après
            Workbooks(SIMFILE).Sheets(MODIFICATIONSHEET).Cells(MODIFICATION, 28).Value = Workbooks(EXTRACTFILE).Sheets(EXTRACTSHEET).Cells(EXTRACT, 37).Value
        End If
 
        If Workbooks(EXTRACTFILE).Sheets(EXTRACTSHEET).Cells(EXTRACT, 38).Value <> Workbooks(SIMFILE).Sheets(SIMSHEET).Cells(SIM, 39).Value Then
            Changes = Changes & "Initial spend" & Chr(10)
            'Avant
            Workbooks(SIMFILE).Sheets(MODIFICATIONSHEET).Cells(MODIFICATION, 17).Value = Workbooks(SIMFILE).Sheets(SIMSHEET).Cells(SIM, 39).Value
            'Après
            Workbooks(SIMFILE).Sheets(MODIFICATIONSHEET).Cells(MODIFICATION, 29).Value = Workbooks(EXTRACTFILE).Sheets(EXTRACTSHEET).Cells(EXTRACT, 38).Value
        End If
 
        If Workbooks(EXTRACTFILE).Sheets(EXTRACTSHEET).Cells(EXTRACT, 39).Value <> Workbooks(SIMFILE).Sheets(SIMSHEET).Cells(SIM, 40).Value Then
            Changes = Changes & "FYS" & Chr(10)
            'Avant
            Workbooks(SIMFILE).Sheets(MODIFICATIONSHEET).Cells(MODIFICATION, 18).Value = Workbooks(SIMFILE).Sheets(SIMSHEET).Cells(SIM, 40).Value
            'Après
            Workbooks(SIMFILE).Sheets(MODIFICATIONSHEET).Cells(MODIFICATION, 30).Value = Workbooks(EXTRACTFILE).Sheets(EXTRACTSHEET).Cells(EXTRACT, 39).Value
        End If
 
        If Workbooks(EXTRACTFILE).Sheets(EXTRACTSHEET).Cells(EXTRACT, 41).Value <> Workbooks(SIMFILE).Sheets(SIMSHEET).Cells(SIM, 42).Value Then
            Changes = Changes & "CF" & Chr(10)
            'Avant
            Workbooks(SIMFILE).Sheets(MODIFICATIONSHEET).Cells(MODIFICATION, 19).Value = Workbooks(SIMFILE).Sheets(SIMSHEET).Cells(SIM, 42).Value
            'Après
            Workbooks(SIMFILE).Sheets(MODIFICATIONSHEET).Cells(MODIFICATION, 31).Value = Workbooks(EXTRACTFILE).Sheets(EXTRACTSHEET).Cells(EXTRACT, 41).Value
        End If
 
        If Changes <> "" Then
            'Modification de l'Format, changement du statut
            Workbooks(SIMFILE).Sheets(SIMSHEET).Cells(SIM, 1).Value = 2
            'Ajout d'une ligne dans le tableau des modifications
            Workbooks(SIMFILE).Sheets(MODIFICATIONSHEET).Cells(MODIFICATION, 1).Value = "Modification"
            Workbooks(SIMFILE).Sheets(MODIFICATIONSHEET).Cells(MODIFICATION, 2).Value = Workbooks(SIMFILE).Sheets(SIMSHEET).Cells(SIM, 2).Value
            Workbooks(SIMFILE).Sheets(MODIFICATIONSHEET).Cells(MODIFICATION, 3).Value = Workbooks(SIMFILE).Sheets(SIMSHEET).Cells(SIM, 7).Value
            Workbooks(SIMFILE).Sheets(MODIFICATIONSHEET).Cells(MODIFICATION, 4).Value = Workbooks(SIMFILE).Sheets(SIMSHEET).Cells(SIM, 4).Value
            Workbooks(SIMFILE).Sheets(MODIFICATIONSHEET).Cells(MODIFICATION, 5).Value = Workbooks(SIMFILE).Sheets(SIMSHEET).Cells(SIM, 48).Value
            Workbooks(SIMFILE).Sheets(MODIFICATIONSHEET).Cells(MODIFICATION, 6).Value = Workbooks(SIMFILE).Sheets(SIMSHEET).Cells(SIM, 29).Value
            Workbooks(SIMFILE).Sheets(MODIFICATIONSHEET).Cells(MODIFICATION, 7).Value = Workbooks(SIMFILE).Sheets(SIMSHEET).Cells(SIM, 34).Value
            Workbooks(SIMFILE).Sheets(MODIFICATIONSHEET).Cells(MODIFICATION, 8).Value = Workbooks(SIMFILE).Sheets(SIMSHEET).Cells(SIM, 51).Value
            Workbooks(SIMFILE).Sheets(MODIFICATIONSHEET).Cells(MODIFICATION, 9).Value = Workbooks(SIMFILE).Sheets(SIMSHEET).Cells(SIM, 19).Value
            Workbooks(SIMFILE).Sheets(MODIFICATIONSHEET).Cells(MODIFICATION, 34).Value = Workbooks(SIMFILE).Sheets(SIMSHEET).Cells(SIM, 67).Value + Workbooks(SIMFILE).Sheets(SIMSHEET).Cells(SIM, 68).Value
            Workbooks(SIMFILE).Sheets(MODIFICATIONSHEET).Select
            Range("AI" & MODIFICATION).Select
            ActiveCell.FormulaR1C1 = _
                "=SUM(VLOOKUP(RC2,INDIRECT(R1C30&R1C31),{66;67},FALSE))"
            Range("AJ" & MODIFICATION).Select
            ActiveCell.FormulaR1C1 = "=RC[-1]-RC[-2]"
            MODIFICATION = MODIFICATION + 1
        Else
            Workbooks(SIMFILE).Sheets(SIMSHEET).Cells(SIM, 1).Value = 1
        End If
    'Copy the new line
    Workbooks(EXTRACTFILE).Sheets(EXTRACTSHEET).Activate
    Workbooks(EXTRACTFILE).Sheets(EXTRACTSHEET).Range(Workbooks(EXTRACTFILE).Sheets(EXTRACTSHEET).Cells(EXTRACT, 1).address(RowAbsolute:=False, ColumnAbsolute:=False) & ":" & Workbooks(EXTRACTFILE).Sheets(EXTRACTSHEET).Cells(EXTRACT, 51).address(RowAbsolute:=False, ColumnAbsolute:=False)).Select
    Selection.Copy
    Workbooks(SIMFILE).Sheets(SIMSHEET).Activate
    Workbooks(SIMFILE).Sheets(SIMSHEET).Cells(SIM, 2).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
End Function

Code :

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

1
2
3
4
5
6
7
8
9
10
11
Function OpenExtract(partialName As String) As String
Application.DisplayAlerts = False 'retire les alertes Excel si le fichier est déjà ouvert
If Dir(ActiveWorkbook.Path & "\" & partialName) <> "" Then 'Vérification de l'existance du fichier extract
    Workbooks.Open (ActiveWorkbook.Path & "\" & Dir(ActiveWorkbook.Path & "\" & partialName))
    Workbooks(Dir(ActiveWorkbook.Path & "\" & partialName)).Activate
    Application.DisplayAlerts = True
Else
    MsgBox ("Extract file not found.")
End If
OpenExtract = ActiveWorkbook.Name
End Function

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
Function SortFile()
'
' Macro3 Macro
'
Workbooks(SIMFILE).Activate
Sheets(SIMSHEET).Rows("3:3").Select
If Not ActiveSheet.AutoFilter Is Nothing Then
    'A filter exist
    Selection.AutoFilter
    Selection.AutoFilter
Else
    Selection.AutoFilter
End If
    ActiveWorkbook.Worksheets(SIMSHEET).AutoFilter.Sort.SortFields.Clear
    ActiveWorkbook.Worksheets(SIMSHEET).AutoFilter.Sort.SortFields.Add Key:=Range( _
        "B3"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets(SIMSHEET).AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
 
Workbooks(EXTRACTFILE).Activate
Sheets(EXTRACTSHEET).Rows("2:2").Select
If Not ActiveSheet.AutoFilter Is Nothing Then
    'A filter exist
    Selection.AutoFilter
    Selection.AutoFilter
Else
    Selection.AutoFilter
End If
    ActiveWorkbook.Worksheets(EXTRACTSHEET).AutoFilter.Sort.SortFields.Clear
    ActiveWorkbook.Worksheets(EXTRACTSHEET).AutoFilter.Sort.SortFields.Add Key:=Range( _
        "A2"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets(EXTRACTSHEET).AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
Workbooks(SIMFILE).Activate
SortFile = 1
End Function

Code :

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

1
2
3
Public Function checkMPM(SIMCount As Integer, EXTRACTCount As Integer)
checkMPM = Workbooks(EXTRACTFILE).Worksheets(EXTRACTSHEET).Cells(EXTRACTCount, 52).Value = MPM
End Function

Code :

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

1
2
3
4
5
6
7
Function Clean_modification() ' Vide le tableau des modifications
Dim i As Integer
i = 4
Do While Workbooks(SIMFILE).Sheets(MODIFICATIONSHEET).Cells(i, 2).Value <> ""
        Workbooks(SIMFILE).Sheets(MODIFICATIONSHEET).Rows(i).EntireRow.Delete
Loop
End Function

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
Sub FillFormule()
Dim i As Integer
i = 4
Sheets(SIMSHEET).Select
Do While Cells(i, 1).Value <> ""
    If Cells(i, 1) = 0 Then
    Range("BA" & i).Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC55,Tables!R2C51:R6C52,2,FALSE)"
    Range("BB" & i).Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-36],Tables!R2C1:R86C5,2,FALSE)"
    Range("BC" & i).Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-37],Tables!R2C1:R86C5,4,FALSE)"
    Range("BD" & i).Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-38],Tables!R2C1:R86C5,5,FALSE)"
    Range("BE" & i).Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-48],Tables!R2C28:R21C29,2,0)"
    Range("BF" & i).Select
    ActiveCell.FormulaR1C1 = _
        "=IF(ISERROR(VLOOKUP(RC[-43],Tables!R2C39:R48C45,2,FALSE)),""Others"",VLOOKUP(RC[-43],Tables!R2C39:R48C45,2,FALSE))"
    Range("BG" & i).Select
    ActiveCell.FormulaR1C1 = _
        "=IF(ISERROR(VLOOKUP(RC[-44],Tables!R2C39:R48C45,3,FALSE)),""Others"",VLOOKUP(RC[-44],Tables!R2C39:R48C45,3,FALSE))"
    Range("BH" & i).Select
    ActiveCell.FormulaR1C1 = _
        "=IF(ISERROR(VLOOKUP(RC[-45],Tables!R2C39:R48C45,4,FALSE)),""Others"",VLOOKUP(RC[-45],Tables!R2C39:R48C45,4,FALSE))"
    Range("BI" & i).Select
    ActiveCell.FormulaR1C1 = _
        "=IF(ISERROR(VLOOKUP(RC[-46],Tables!R2C39:R48C45,7,FALSE)),""Others"",VLOOKUP(RC[-46],Tables!R2C39:R48C45,7,FALSE))"
    Range("BJ" & i).Select
    ActiveCell.FormulaR1C1 = _
        "=IF(AND(RC[1]=""MEC"",RC[2]=""NEC"",RC[-52]<>""CANCELLED""),""ACCESS"",""Other"")"
    Range("BK" & i).Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(VLOOKUP(RC[-32],Tables!R2C35:R244C36,2,FALSE),"""")"
    Range("BL" & i).Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(VLOOKUP(RC[-28],Tables!R2C35:R244C36,2,FALSE),"""")"
    Range("BM" & i).Select
    ActiveCell.FormulaR1C1 = "=YEAR(RC[-27])"
    Range("BN" & i).Select
    ActiveCell.FormulaR1C1 = "=RC[-27]*RC[-24]/100"
    Range("BO" & i).Select
    ActiveCell.FormulaR1C1 = _
        "=IF(RC[-2]= R2C[-2],IF(RC[-57]=""REC"",IF(RC11=""YES - SPOT"", RC[-24],( RC[-24]*(13-MONTH(RC[-29]))/12)),0),0)"
    Range("BP" & i).Select
    ActiveCell.FormulaR1C1 = _
        "=IF(RC[-3]= R2C[-3]-1,(IF(RC[-58]=""NRC"",0,IF(RC11=""YES - SPOT"",0,(( RC[-25]/12)*(12-(13-MONTH(RC[-30])))*(1+(RC[-24]/100)))))),0)"
    Range("BQ" & i).Select
    ActiveCell.FormulaR1C1 = _
        "=IF(RC68<>0,IF(MONTH(RC38)>R3C,RC43*(1+(RC44/100))/12,0),IF(RC67<>0,IF(MONTH(RC38)<=R3C,IF(RC11=""YES - REC"",RC43/12,IF(MONTH(RC38)= R3C,RC67,0)),0),0))"
    Range("BR" & i).Select
    ActiveCell.FormulaR1C1 = _
        "=IF(RC68<>0,IF(MONTH(RC38)>R3C,RC43*(1+(RC44/100))/12,0),IF(RC67<>0,IF(MONTH(RC38)<=R3C,IF(RC11=""YES - REC"",RC43/12,IF(MONTH(RC38)= R3C,RC67,0)),0),0))+RC[-1]"
    Range("BS" & i).Select
    ActiveCell.FormulaR1C1 = _
        "=IF(RC68<>0,IF(MONTH(RC38)>R3C,RC43*(1+(RC44/100))/12,0),IF(RC67<>0,IF(MONTH(RC38)<=R3C,IF(RC11=""YES - REC"",RC43/12,IF(MONTH(RC38)= R3C,RC67,0)),0),0))+RC[-1]"
    Range("BT" & i).Select
    ActiveCell.FormulaR1C1 = _
        "=IF(RC68<>0,IF(MONTH(RC38)>R3C,RC43*(1+(RC44/100))/12,0),IF(RC67<>0,IF(MONTH(RC38)<=R3C,IF(RC11=""YES - REC"",RC43/12,IF(MONTH(RC38)= R3C,RC67,0)),0),0))+RC[-1]"
    Range("BU" & i).Select
    ActiveCell.FormulaR1C1 = _
        "=IF(RC68<>0,IF(MONTH(RC38)>R3C,RC43*(1+(RC44/100))/12,0),IF(RC67<>0,IF(MONTH(RC38)<=R3C,IF(RC11=""YES - REC"",RC43/12,IF(MONTH(RC38)= R3C,RC67,0)),0),0))+RC[-1]"
    Range("BV" & i).Select
    ActiveCell.FormulaR1C1 = _
        "=IF(RC68<>0,IF(MONTH(RC38)>R3C,RC43*(1+(RC44/100))/12,0),IF(RC67<>0,IF(MONTH(RC38)<=R3C,IF(RC11=""YES - REC"",RC43/12,IF(MONTH(RC38)= R3C,RC67,0)),0),0))+RC[-1]"
    Range("BW" & i).Select
    ActiveCell.FormulaR1C1 = _
        "=IF(RC68<>0,IF(MONTH(RC38)>R3C,RC43*(1+(RC44/100))/12,0),IF(RC67<>0,IF(MONTH(RC38)<=R3C,IF(RC11=""YES - REC"",RC43/12,IF(MONTH(RC38)= R3C,RC67,0)),0),0))+RC[-1]"
    Range("BX" & i).Select
    ActiveCell.FormulaR1C1 = _
        "=IF(RC68<>0,IF(MONTH(RC38)>R3C,RC43*(1+(RC44/100))/12,0),IF(RC67<>0,IF(MONTH(RC38)<=R3C,IF(RC11=""YES - REC"",RC43/12,IF(MONTH(RC38)= R3C,RC67,0)),0),0))+RC[-1]"
    Range("BY" & i).Select
    ActiveCell.FormulaR1C1 = _
        "=IF(RC68<>0,IF(MONTH(RC38)>R3C,RC43*(1+(RC44/100))/12,0),IF(RC67<>0,IF(MONTH(RC38)<=R3C,IF(RC11=""YES - REC"",RC43/12,IF(MONTH(RC38)= R3C,RC67,0)),0),0))+RC[-1]"
    Range("BZ" & i).Select
    ActiveCell.FormulaR1C1 = _
        "=IF(RC68<>0,IF(MONTH(RC38)>R3C,RC43*(1+(RC44/100))/12,0),IF(RC67<>0,IF(MONTH(RC38)<=R3C,IF(RC11=""YES - REC"",RC43/12,IF(MONTH(RC38)= R3C,RC67,0)),0),0))+RC[-1]"
    Range("CA" & i).Select
    ActiveCell.FormulaR1C1 = _
        "=IF(RC68<>0,IF(MONTH(RC38)>R3C,RC43*(1+(RC44/100))/12,0),IF(RC67<>0,IF(MONTH(RC38)<=R3C,IF(RC11=""YES - REC"",RC43/12,IF(MONTH(RC38)= R3C,RC67,0)),0),0))+RC[-1]"
    Range("CB" & i).Select
    ActiveCell.FormulaR1C1 = _
        "=IF(RC68<>0,IF(MONTH(RC38)>R3C,RC43*(1+(RC44/100))/12,0),IF(RC67<>0,IF(MONTH(RC38)<=R3C,IF(RC11=""YES - REC"",RC43/12,IF(MONTH(RC38)= R3C,RC67,0)),0),0))+RC[-1]"
    Range("CC" & i).Value = 0
    Range("CD" & i).Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-75],Tables!R2C48:R10C49,2,FALSE)"
        Range("DG" & i).Select
    ActiveCell.FormulaR1C1 = _
        "=IF(C[-101]<>""NRC"",IF(C[-100] <> ""YES - SPOT"",RC[-71],IF(RC[-46]=R2C65,RC[-71],0)),0)"
    Range("DF" & i).Select
    ActiveCell.FormulaR1C1 = _
        "=IF(C[-100]<>""NRC"",IF(C[-99] <> ""YES - SPOT"",(RC[-70]/12)*(13-MONTH(RC[-72])),IF(RC[-45]=R2C65,RC[1],0)),0)"
 
    End If
    i = i + 1
Loop
End Sub

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
Sub Update()
Dim SIM, EXTRACT As String
SIM = ""
EXTRACT = ""
'File
SIMFILE = ActiveWorkbook.Name
Dim start As Single
Dim temps As Integer
Dim emptyVar As Integer
Dim partialName As String
Dim NomFichier As String
NomFichier = "_SIM_GSC-Europe_Consolidated.xlsm"
Dim SIMCount As Integer
Dim EXTRACTCount As Integer
SIMCount = 4
EXTRACTCount = 3
partialName = "*OTTO_data_Patrizio*"
'MPM
MPM = "Conso"
'Sheet
SIMSHEET = "OTTO"
EXTRACTSHEET = "Global without cancelled"
MODIFICATIONSHEET = "Modifications"
SUPPRESSIONSHEET = "Deleted rows"
'Index
MODIFICATION = 4
Dim i As Integer
i = 4
Do While Workbooks(SIMFILE).Sheets(SUPPRESSIONSHEET).Cells(i, 2).Value <> ""
    i = i + 1
Loop
SUPPRIMEE = i
'OPEN
EXTRACTFILE = OpenExtract(partialName)
Workbooks(SIMFILE).Activate
If EXTRACTFILE <> SIMFILE Then
    If MsgBox("Are you sure you want to update the file with the extraction " & EXTRACTFILE & " ?", vbYesNo, "Demande de confirmation") = vbYes Then
        Application.Calculation = xlManual
        Application.ScreenUpdating = False
        Range("A1").Value = Date
        'SORT
        emptyVar = SortFile
        'CHECK
        Clean_modification
        start = Timer
        Do While Workbooks(EXTRACTFILE).Worksheets(EXTRACTSHEET).Cells(EXTRACTCount, 1).Value <> "" Or Workbooks(SIMFILE).Worksheets(SIMSHEET).Cells(SIMCount, 2).Value <> ""
            SIM = Workbooks(SIMFILE).Worksheets(SIMSHEET).Cells(SIMCount, 2).Value
            EXTRACT = Workbooks(EXTRACTFILE).Worksheets(EXTRACTSHEET).Cells(EXTRACTCount, 1).Value
 
            If SIM <> "" Then
                If EXTRACT <> "" Then
                    Select Case (EXTRACT)
                        Case Is > SIM:
                            SuppressionSub (SIMCount)
                        Case Is = SIM:
                            emptyVar = ModficationSub(EXTRACTCount, SIMCount)
                            EXTRACTCount = EXTRACTCount + 1
                            SIMCount = SIMCount + 1
                        Case Is < SIM:
                            If checkMPM(SIMCount, EXTRACTCount) Then
                                emptyVar = InsertionSub(EXTRACTCount, SIMCount)
                                EXTRACTCount = EXTRACTCount + 1
                                SIMCount = SIMCount + 1
                            Else
                                EXTRACTCount = EXTRACTCount + 1
                            End If
                    End Select
                Else
                    SuppressionSub (SIMCount)
                End If
            Else
                If checkMPM(SIMCount, EXTRACTCount) Then
                    emptyVar = InsertionSub(EXTRACTCount, SIMCount)
                    EXTRACTCount = EXTRACTCount + 1
                    SIMCount = SIMCount + 1
                Else
                    EXTRACTCount = EXTRACTCount + 1
                End If
            End If
        Loop
        Workbooks(SIMFILE).Activate
        FillFormule
        Application.ScreenUpdating = True
        Application.Calculation = xlCalculationAutomatic
        temps = Timer - start
        Sheets(SIMSHEET).Range("B1").Value = EXTRACTFILE & " - " & temps & " secondes."
        MsgBox "File updated successfully. Duration of treatment: " & temps & " secondes " & Chr(13) & " File save as '" & Replace(Format(Sheets(SIMSHEET).Range("A1").Value, "YYYY/MM/dd"), "/", "_") & NomFichier & "'."
 
        'Sauvegarde à la date du jour
        ActiveWorkbook.SaveAs Filename:= _
        ActiveWorkbook.Path & "\" & Replace(Format(Sheets(SIMSHEET).Range("A1").Value, "YYYY/MM/dd"), "/", "_") & NomFichier _
        , FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
        Else
            MsgBox "Update canceled."
        End If
        Application.DisplayAlerts = False
        Workbooks(EXTRACTFILE).Close
        Application.DisplayAlerts = True
End If
End Sub

[Trystan441]

Bonjour,

Dans un premier temps, supprime TOUS les "select" et "activate" de ton code !

[Igloobel]

Bonjour,

Bonjour,

Dans un premier temps, supprime TOUS les "select" et "activate" de ton code !

+1

mais aussi tu utilses beaucoup trop les insertions de formules.

Les formules ont 2 défauts :
- elles font grossir énormement la taille du fichier
- Les insertions de celles-ci font ralentir les macros (action de la macro + action de la formule)

Je suis surpris que utilises le "=TODAY()" pour la suppression, je vois pas l'interêt
plutôt que de même une formule on peut balancer la date directement.

et je suis sûr que la formule "=VLOOKUP(....)" peut être transformé par "WorksheetFunction.VLookup(....)" ou n'importe quelle boucle avec un test associé

l'inconvénient de VLOOKUP c'est que l'on est obligé de faire la recherche sur la colonne 1 de la matrice et qu'il s'arrête au premier ce qui n'est pas le cas avec une boucle associé de test.

Enfin ceci n'est que mon avis inspiré de mon vécu

[geoffrey.pfirsch]

Bonjour,

Merci pour vos retours, je vais faire quelques tests. Les "Select" et "Activate" je les remplace par quoi ?

Néanmoins, avez-vous une explication concernant le fait que la macro s'exécute en quelques minutes sous Excel 2007 et indéfiniement sur des versions d'Excel supérieures ? Alors que c'est le même code qui s'exécute….

[Igloobel]

.... Merci pour vos retours, je vais faire quelques tests. Les "Select" et "Activate" je les remplace par quoi ?

par rien

exemple :

Code :

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

1
2
3
4
Workbooks(SIMFILE).Sheets(MODIFICATIONSHEET).Select
    Range("AI" & MODIFICATION).Select
    ActiveCell.FormulaR1C1 = _
        "=SUM(VLOOKUP(RC2,INDIRECT(R1C30&R1C31),{66;67},FALSE))"

devient

Code :

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

Workbooks(SIMFILE).Sheets(MODIFICATIONSHEET).Range("AI" & MODIFICATION).formula = "=SUM(VLOOKUP(RC2,INDIRECT(R1C30&R1C31),{66;67},FALSE))"