[XL-2007] Code VBA pour demander à la macro d'aller rechercher un chemin d'accès afin d'ouvrir un fichier XLS

[sky-olive]

Bonjour à tous et merci d'avance pour votre aide.

Mon intitulé de discussion ne doit pas être très clair, désolé !

Je m'explique,

je suis apprentie en contrôle de gestion et tous les mois je suits un indicateur via un fichier Excel.
Les données utilisées dans ce fichier vienne d'un autre fichier Excel appelé RATIO.
Mais pour implanter les données du fichier RATIO dans mon fichier Excel de base j'ai besoin de faire une recherchev.

Donc, j'aimerai automatiser mon fichier Excel de base en créant une macro qui ferait la rechercherv automatiquement et surtout qui m'ouvrirai mon fichier RATIO du mois encours.

Pour la recherchev, j'ai lancer une macro automatique. Cette macro marche mais que si j'ai le fichier RATIO ouvert ! De plus, comme mon fichier RATIO change tout les mois, je ne sais plus comment faire !

Voici le début de ma macro pour vous donner une idée :

Code :

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
With Worksheets("Détails Site").Activate
    ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(RC[-16],'[Ratio_tx_AA_suivi_2011_2012_09.xls]Rapport Final'!C8:C15,8,FALSE)"
    Range("X4").Select
    Selection.Copy
    Range("X5").Select
    ActiveWindow.ScrollRow = 10
    ActiveWindow.ScrollRow = 19
    ActiveWindow.ScrollRow = 28
    ActiveWindow.ScrollRow = 47
    ActiveWindow.ScrollRow = 66
    ActiveWindow.ScrollRow = 94
    ActiveWindow.ScrollRow = 132
    ActiveWindow.ScrollRow = 165
    ActiveWindow.ScrollRow = 207
    ActiveWindow.ScrollRow = 263
    ActiveWindow.ScrollRow = 325
    ActiveWindow.ScrollRow = 390
    ActiveWindow.ScrollRow = 451
    ActiveWindow.ScrollRow = 513
    ActiveWindow.ScrollRow = 574
    ActiveWindow.ScrollRow = 635
    ActiveWindow.ScrollRow = 700
    ActiveWindow.ScrollRow = 766
    ActiveWindow.ScrollRow = 827
    ActiveWindow.ScrollRow = 907
    ActiveWindow.ScrollRow = 973
     Range("X5:X3427").Select
    ActiveSheet.Paste
    ActiveWindow.ScrollRow = 3374
    ActiveWindow.ScrollRow = 3370
    ActiveWindow.ScrollRow = 3365
    ActiveWindow.ScrollRow = 3360
    ActiveWindow.ScrollRow = 3351
    ActiveWindow.ScrollRow = 3341
    ActiveWindow.ScrollRow = 3332
    ActiveWindow.ScrollRow = 3309
    ActiveWindow.ScrollRow = 3294
    ActiveWindow.ScrollRow = 3266
    ActiveWindow.ScrollRow = 3233
    ActiveWindow.ScrollRow = 3210
    ActiveWindow.ScrollRow = 3177
    ActiveWindow.ScrollRow = 3135
    ActiveWindow.ScrollRow = 3074
    ActiveWindow.ScrollRow = 3022
    ActiveWindow.ScrollRow = 2961
    ActiveWindow.ScrollRow = 2895
    ActiveWindow.ScrollRow = 2834
    ActiveWindow.ScrollRow = 2773
    ActiveWindow.ScrollRow = 2702
    ActiveWindow.ScrollRow = 2641
    ActiveWindow.ScrollRow = 2580
    ActiveWindow.ScrollRow = 2510
    ActiveWindow.ScrollRow = 2449
    ActiveWindow.ScrollRow = 2378
    ActiveWindow.ScrollRow = 2223
    ActiveWindow.ScrollRow = 2157
    ActiveWindow.ScrollRow = 2073
    ActiveWindow.ScrollRow = 2002
    ActiveWindow.ScrollRow = 1941
    ActiveWindow.ScrollRow = 1871
    ActiveWindow.ScrollRow = 1809
    ActiveWindow.ScrollRow = 1748
    ActiveWindow.ScrollRow = 1687
    ActiveWindow.ScrollRow = 1617
    ActiveWindow.ScrollRow = 1556
    ActiveWindow.ScrollRow = 1495
    ActiveWindow.ScrollRow = 1438
          Application.CutCopyMode = False
    Range("Y4").Select
    ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(RC[-17],'[Ratio_tx_AA_suivi_2011_2012_09.xls]Rapport Final'!C8:C18,11,FALSE)"
    Range("Y4").Select
    Selection.Copy
    Range("Y5").Select
    ActiveWindow.ScrollRow = 10
    ActiveWindow.ScrollRow = 24
    ActiveWindow.ScrollRow = 33
    ActiveWindow.ScrollRow = 52
    ActiveWindow.ScrollRow = 71
    ActiveWindow.ScrollRow = 108
    ActiveWindow.ScrollRow = 146
    ActiveWindow.ScrollRow = 188
    ActiveWindow.ScrollRow = 245
    ActiveWindow.ScrollRow = 306
    ActiveWindow.ScrollRow = 372
    ActiveWindow.ScrollRow = 428

En espérant que vous ailliez compris mon problème.
Et merci pour votre aide.

Bien cordialement,

[Daniel.C]

Bonjour,

J'ai élagué ton code qui a l'air de sortir de l'enregistreur de macros. Je pense qu'il en manque un morceau.
Essaie de remplacer ce que tu as posté par :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
Sub test4()
Dim Fich As String
Fich = Application.GetOpenFilename(("Excel Files (*.xls*), *.xls*"))
Workbooks.Open Fich
ThisWorkbook.Activate
With Worksheets("Détails Site")
    ActiveCell.FormulaR1C1 = _
    "=VLOOKUP(RC[-16],'[Ratio_tx_AA_suivi_2011_2012_09.xls]Rapport Final'!C8:C15,8,FALSE)"
    Range("X4").Copy Range("X5:X3427")
    Application.CutCopyMode = False
    Range("Y4").FormulaR1C1 = _
    "=VLOOKUP(RC[-17],'[Ratio_tx_AA_suivi_2011_2012_09.xls]Rapport Final'!C8:C18,11,FALSE)"
    Range("Y4").Copy
End With
End Sub

[sky-olive]

Bonjour Daniel,

Merci beaucoup pour le langage VBA, ma macro marche niquel !

Malheuresement j'ai encore un problème
Comme mon fichier RATIO change tout les mois, ma recherchev qui vient prendre ses source dans ce même fichier va obligatoirement changer.
Le truc c'est que je ne sais pas comment faire pour dire à ma macro qu'elle doit faire la recherchev sur le fichier XLS qu'on lui a dit d'ouvrir au début !!

De plus, comment faire dire à la macro que chaque mois, la recherchev ne se fera pas sur les même cellules !

Ps : en rouge, les éléments variables d'un mois sur l'autre

J'espère que mes explications sont assez claires.
C'est fou comme ça devient compliqué quand il commence à y avoir trop de variables !

Encore merci,
Cordialement,
Clair

Code :

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
Dim Fich As String
Fich = Application.GetOpenFilename(("Excel Files (*.xls*), *.xls*"))
Workbooks.Open Fich
ThisWorkbook.Activate
With Worksheets("Détails Site")
    ActiveCell.FormulaR1C1 = _
    "=VLOOKUP(RC[-16],'[Ratio_tx_AA_suivi_2011_2012_09.xls]Rapport Final'!C8:C15,8,FALSE)"
    Range("X4").Copy Range("X5:X3427")
    Application.CutCopyMode = False
    
    Range("Y4").FormulaR1C1 = _
    "=VLOOKUP(RC[-17],'[Ratio_tx_AA_suivi_2011_2012_09.xls]Rapport Final'!C8:C18,11,FALSE)"
    Range("Y4").Copy Range("Y5:Y3427")
    Application.CutCopyMode = False
    
    Range("AA4").FormulaR1C1 = _
    "=VLOOKUP(RC[-19],'[Ratio_tx_AA_suivi_2011_2012_09.xls]Rapport Final'!C8:C20,13,FALSE)"
    Range("AA4").Copy Range("AA4:AA3427")
    Application.CutCopyMode = False
    
    Range("AB4").FormulaR1C1 = _
    "=VLOOKUP(RC[-20],'[Ratio_tx_AA_suivi_2011_2012_09.xls]Rapport Final'!C8:C23,16,FALSE)"
    Range("AB4").Copy Range("AB4:AB3427")
    Application.CutCopyMode = False
    
    
    ActiveWorkbook.BreakLink Name:= _
        "C:\Documents and Settings\claire.nonglaton\Mes documents\E-mail Important\TX AA\Année_2012\RATIOS\Ratio_tx_AA_suivi_2011_2012_09.xls" _
        , Type:=xlExcelLinks
    ActiveCell.Offset(3398, -3).Range("A1").Select
    Selection.Copy
    Cells.Replace What:="#N/A", Replacement:="0", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Application.CutCopyMode = False

[Daniel.C]

De plus, comment faire dire à la macro que chaque mois, la recherchev ne se fera pas sur les même cellules !

Le nom de fichier ne pose pas de problème. Par contre comment déterminer la plage sur laquelle se fait la recherche ?

[mercatog]

Une première proposition qui mets le fichier à ouvrir en variable

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
Sub Test4()
Dim Wbk As Workbook
Dim Fich As Variant
 
Application.ScreenUpdating = False
Fich = Application.GetOpenFilename(("Excel Files (*.xls*), *.xls*"))
If Fich <> False Then
    Set Wbk = Workbooks.Open(Fich)
    With ThisWorkbook.Worksheets("Détails Site")
        With .Range("X5:X3427")
            .FormulaR1C1 = "=VLOOKUP(RC[-16],'[" & Wbk.Name & "]Rapport Final'!C8:C15,8,FALSE)"
            .Value = .Value
            .Replace "#N/A", ""
        End With
        With .Range("Y5:Y3427")
            .FormulaR1C1 = "=VLOOKUP(RC[-17],'[" & Wbk.Name & "]Rapport Final'!C8:C18,11,FALSE)"
            .Value = .Value
            .Replace "#N/A", ""
        End With
    End With
    Wbk.Close False
    Set Wbk = Nothing
End If
End Sub

[sky-olive]

Haaaaa Génial, merci beaucoup pour votre aide!
Reste plus qu'une chose et ça sera parfait, comme la re-précisé Daniel, comment demander à la macro de faire la recherchev non pas en colonne X; Y; AA; AB mais en colonne AH; AI; AK.... sachant que ça rechangera encore le mois d'après !

Code :

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
Dim Wbk As Workbook
Dim Fich As Variant

Application.ScreenUpdating = False
Fich = Application.GetOpenFilename(("Excel Files (*.xls*), *.xls*"))
If Fich <> False Then
    Set Wbk = Workbooks.Open(Fich)
    With ThisWorkbook.Worksheets("Détails Site")
        With .Range("X4:X3427")
            .FormulaR1C1 = "=VLOOKUP(RC[-16],'[" & Wbk.Name & "]Rapport Final'!C8:C15,8,FALSE)"
            .Value = .Value
            .Replace "#N/A", ""
        End With
        With .Range("Y4:Y3427")
            .FormulaR1C1 = "=VLOOKUP(RC[-17],'[" & Wbk.Name & "]Rapport Final'!C8:C18,11,FALSE)"
            .Value = .Value
            .Replace "#N/A", ""
        End With
        With .Range("AA4:AA3427")
            .FormulaR1C1 = "=VLOOKUP(RC[-19],'[" & Wbk.Name & "]Rapport Final'!C8:C20,13,FALSE)"
            .Value = .Value
            .Replace "#N/A", ""
        End With
        With .Range("AB4:AB3427")
            .FormulaR1C1 = "=VLOOKUP(RC[-20],'[" & Wbk.Name & "]Rapport Final'!C8:C23,16,FALSE)"
            .Value = .Value
            .Replace "#N/A", ""
        End With
        
    End With
    Wbk.Close False
    Set Wbk = Nothing
End If
End Sub

Cordialement
Clair

[mercatog]

Et comme tu distingue pour chaque mois la colonne de formules X ou AH?
As tu un lien entre fichier mensuel et colonne?

[Daniel.C]

En reprenant le code de mercatog et en faisant choisir l'utilisateur (non testé) :

Code :

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
Sub Test4()
Dim Wbk As Workbook
Dim Fich As Variant
Dim Plage As Variant
Dim Plage1 As String
Dim Plage2 As String
Application.ScreenUpdating = False
Fich = Application.GetOpenFilename(("Excel Files (*.xls*), *.xls*"))
If Fich <> False Then
    Set Wbk = Workbooks.Open(Fich)
    Sheets("Rapport Final").Select
    Set Plage = Application.InputBox("Sélectionnez la plage de recherche", Type:=8)
    Plage1 = Plage.Address(, , xlR1C1)
    Plage2 = Plage.Resize(, Plage.Columns + 3).Address(, , xlR1C1)
    With ThisWorkbook.Worksheets("Détails Site")
        With .Range("X5:X3427")
            .FormulaR1C1 = "=VLOOKUP(RC[-16],'[" & Wbk.Name & "]Rapport Final'!" & Plage1 & ",8,FALSE)"
            .Value = .Value
            .Replace "#N/A", ""
        End With
        With .Range("Y5:Y3427")
            .FormulaR1C1 = "=VLOOKUP(RC[-17],'[" & Wbk.Name & "]Rapport Final'!" & Plage2 & ",11,FALSE)"
            .Value = .Value
            .Replace "#N/A", ""
        End With
    End With
    Wbk.Close False
    Set Wbk = Nothing
End If
End Sub

[sky-olive]

Tout d'abord merci Daniel pour ce nouveau test !

Je me permet de vous envoyer, en pièce jointe, mon fichier de base sur lequel j'execute la macro !
Mercatog celà va sans doute répondre à votre question ! Parce que si je me lance dans des explications j'ai peur de ne pas m'en sortir

Vous pourrez ainsi mieux comprendre mon problème de colonne pour les recherchev !

Cordialement,
Clair

[Daniel.C]

OK. J'avais compris que c'était la plage du fichier RATIO qui changeait... Et le mois, on le prend où ?

[sky-olive]

Oup'ss désolé Daniel pour m'être mal exprimé !

Par exemple, sur le mois de Septembre, les données du fichier RATIO doivent être réintégrer sur la colonne X; Y; AA; AB qui corresponde au mois de septembre.
Si on était en Octobre je devrais faire mes recherchev sur les colonnes AH; AI; AK; AL et ainsi de suite pour tous les mois de l'années.

En gros, c'est moi qui choisi le mois.

Mon problème est donc là comment dire à la macro qu'elle doit lancer les recherches sur tel ou tel colonne !?

[Daniel.C]

Tu peux entrer le mois dans une inputbox ou es-ce qu'on peut prendre le numéro qui se trouve dans le nom du fichier (Ratio_tx_AA_suivi_2011_2012_09.xls = septembre) ?

[sky-olive]

Ha oui, on peut tout-à-fait prendre le mois renseigner dans l'intitulé du Ratio !

Cordialement,
Clair

[Daniel.C]

Essaie comme ça :

Code :

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
Sub Test4()
Dim Wbk As Workbook
Dim Fich As Variant
Dim Mois As Integer
Dim Plage As String
 
Application.ScreenUpdating = False
Fich = Application.GetOpenFilename(("Excel Files (*.xls*), *.xls*"))
Mois = CInt(Mid(Fich, InStr(1, Fich, ".") - 2, 2)) - 9
If Mois < 0 Then Mois + 12
If Fich <> False Then
    Set Wbk = Workbooks.Open(Fich)
    With ThisWorkbook.Worksheets("Détails Site")
        With .Range("X5:X3427").Offset(, Mois * 10)
            .FormulaR1C1 = "=VLOOKUP(RC[-16],'[" & Wbk.Name & "]Rapport Final'!C8:C15,8,FALSE)"
            .Value = .Value
            .Replace "#N/A", ""
        End With
        With .Range("Y5:Y3427").Offset(, Mois * 10)
            .FormulaR1C1 = "=VLOOKUP(RC[-17],'[" & Wbk.Name & "]Rapport Final'!C8:C18,11,FALSE)"
            .Value = .Value
            .Replace "#N/A", ""
        End With
        With .Range("AA4:AA3427").Offset(, Mois * 10)
            .FormulaR1C1 = "=VLOOKUP(RC[-19],'[" & Wbk.Name & "]Rapport Final'!C8:C20,13,FALSE)"
            .Value = .Value
            .Replace "#N/A", ""
        End With
 
        With .Range("AB4:AB3427").Offset(, Mois * 10)
            .FormulaR1C1 "=VLOOKUP(RC[-20],'[" & Wbk.Name & "]Rapport Final'!C8:C23,16,FALSE)"
            .Value = .Value
            .Replace "#N/A", ""
        End With
    End With
    Wbk.Close False
    Set Wbk = Nothing
End If
End Sub

[sky-olive]

Merci pour votre aide Daniel,

J'ai essayé le test mais il y a un souci sur le mois :

Code :

If Mois < 0 Then Mois + 12

Il me stipule une erreur de compilation : sub, Function ou Property attendu.

Cordialement,
Clair

[yoyo3d]

Salut, tu a 2 if l'un apres l'autre dans ton code, et j'ai l'impression qu'il te manque un 2eme "end if".

[Daniel.C]

@yoyo3d :

Code :

If Mois < 0 Then Mois + 12

Il ne faut pas mettre de "End If" quand il n'y a qu'une action sur la même ligne, si c'est à ça que tu penses.

@ sky-olive :

Il doit y avoir un mot de surligné. Quel est-il ?

[yoyo3d]

Ha ba oui, c'est à ça que je pensais, je ne connaissais pas cettte possibilité.

[sky-olive]

Bonjour à tous,

Désolé pour ce moment d'absence, je ne pouvais plus me connecter !

Daniel, le mot surligné est "MOIS", de plus la macro enlève le + qui se trouve devant le 12 quand je la lance.

Code :

If Mois < 0 Then Mois + 12

Mais ne serait-il pas possible de faire une Inputbox où on renseignerait le mois, donc les colonnes sur lesquelles la recherchev doit démarrer. Et celà avant d'aller rechercher le fichier RATIO !

Encore merci de vous intérresser à mon cas,

Cordialement,
Clair

[Daniel.C]

Je ne comprends pas. Remplace partout "Mois" par "leMois" et essaie.