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

**sky-olive** · 08/11/2011, 17h28

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 :

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
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** · 08/11/2011, 17h50

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 :

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

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** · 09/11/2011, 12h03

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 :

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
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** · 09/11/2011, 12h08

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** · 09/11/2011, 13h33

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

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
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** · 09/11/2011, 14h21

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 :

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
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** · 09/11/2011, 14h25

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** · 09/11/2011, 14h52

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

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
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** · 09/11/2011, 15h51

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** · 09/11/2011, 16h07

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

**sky-olive** · 09/11/2011, 16h18

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** · 09/11/2011, 16h23

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** · 09/11/2011, 16h28

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

Cordialement,
Clair

**Daniel.C** · 09/11/2011, 16h38

Essaie comme ça :

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
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** · 09/11/2011, 16h53

Merci pour votre aide Daniel,

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

Code :

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

If Mois < 0 Then Mois + 12

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

Cordialement,
Clair

**yoyo3d** · 10/11/2011, 09h31

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** · 10/11/2011, 10h10

@yoyo3d :

Code :

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

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** · 10/11/2011, 10h39

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

**sky-olive** · 14/11/2011, 09h14

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 :

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

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