Recherche v dans un classeur fermé avec variable

**JonathanF** · 20/02/2013, 16h36

Bonjour tout le monde,

Je suis en train de construire une macro qui me servirait à réaliser une recherchev sur un classeur fermé, avec en variable le nom du classeur.

Mon soucis principal est que la requête fonctionne bien mais lorsque le classeur est fermé la formule ne me renvoit plus rien.

Un exemple de formule finale dans la feuille de calcul:
=XRECHERCHEV(A2;"'C:\Perso\[" & D2 & "]_Synthèse'!$A$2:$F$35";6)

Code:

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
Option Explicit
 
Public Function XRECHERCHEV(ByVal valRecherchee As Variant, _
                            ByVal TabMatrice As Variant, _
                            ByVal colonneIndex As Integer)
 
 
If TypeName(TabMatrice) = "Range" Then
    XRECHERCHEV = Application.WorksheetFunction.VLookup(valRecherchee, _
                                                        TabMatrice, _
                                                        colonneIndex, _
                                                        True)
Else
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim sRange As String
    Dim sSheet As String
    Dim sWbook As String
    Dim sFPath As String
    Dim sSQL   As String
    Dim str As String
 
 
    sRange = Replace(Split(TabMatrice, "!")(1), "$", vbNullString)
    sSheet = Split(Split(TabMatrice, "]")(1), "'")(0)
    sWbook = Split(Split(TabMatrice, "[")(1), "]")(0)
    sFPath = Mid(Split(TabMatrice, "[")(0), 2)
 
    valRecherchee = "'" & Replace(valRecherchee, "'", "''") & "'"
 
    sSQL = "SELECT [F" & colonneIndex & "] " & _
           "FROM [" & sSheet & "$" & sRange & "] " & _
           "WHERE [F1] = " & valRecherchee
 
 
    Set db = DAO.OpenDatabase(sFPath & sWbook, False, False, "Excel 8.0;HDR=NO;")
    Set rs = db.OpenRecordset(sSQL, DAO.dbOpenSnapshot)
 
    If rs.EOF And rs.BOF Then
        XRECHERCHEV = "no match"
    Else
        XRECHERCHEV = rs.Fields(0)
    End If
    Set rs = Nothing
    Set db = Nothing
End If
 
End Function

Et ci-dessous mon code. Mon soucis majeur est que le code fonctionne bien quand le classeur cible est ouvert. Lorsqu'il est fermé, le code me renvoi #valeur!.

Quelqu'un aurait-il une idée ?
Merci d'avance pour votre aide,
Jonathan

**Theze** · 20/02/2013, 21h29

Bonsoir,

C'est vrai, une fois le classeur fermé, plus de retour d'info. Tu peux par contre essayer avec ExecuteExcel4Macro, ça marche sur un classeur fermé. Attention, la plage doit être en référence R1C1. Ci-dessous un petit exemple que tu dois adapter dans la proc "Test" pour voir si ça te convient :

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
 
Sub Test()
 
    'Attention, la plage doit être passée en référence R1C1. Ici, A1:C34
    MsgBox XRECHERCHEV("C:\Dossier1\Dossier2\", _
                       "Classeur.xls", _
                       "Feuil1", _
                       "R1C1:R34C3", _
                       "MonMot", _
                       2)
 
End Sub
 
Function XRECHERCHEV(Chemin As String, _
                     Classeur As String, _
                     Feuille As String, _
                     Plage As String, _
                     ValRecherchee As String, _
                     IndexColonne As Integer)
 
    XRECHERCHEV = ExecuteExcel4Macro("VLOOKUP(""" & ValRecherchee & """,'[" & Classeur & "]" & Feuille & "'!" & Plage & "," & IndexColonne & ",TRUE)")
 
End Function

Hervé.

**JonathanF** · 21/02/2013, 15h43

Bonjour Hervé,

Merci beaucoup pour ta réponse mais je ne vois pas la démarche.
Tu veux que je copie / colle ton code après le mien ?

Merci d'avance.

**Oliv-** · 22/02/2013, 16h22

Salut,
Tu cherches une valeur exacte ou pas ?

edit :tu cherches quoi du texte ou des chiffres ?

**Theze** · 22/02/2013, 17h15

Bonjour,

J'ai incorporé ma proposition dans ta fonction pour que VlookUp marche sur un classeur fermé par l'intermédiaire de ExecuteExcel4Macro. Pour ça, il faut passer en argument le chemin du classeur, le nom du classeur et le nom de la feuille. Comme il est possible, à priori, que tu ne passe pas toujours une plage, les arguments cités ci-dessus sont optionnels. Dans le code de ta fonction, j'ai mis en commentaire ton appel à la fonction VlookUp. Pour tester, adapter la proc "Test" plus bas dans le code et regarde si le résultat retourné te convient :

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
 
Public Function XRECHERCHEV(ByVal ValRecherchee As Variant, _
                            ByVal TabMatrice As Variant, _
                            ByVal colonneIndex As Integer, _
                            Optional ByVal Chemin As String, _
                            Optional ByVal Classeur As String, _
                            Optional ByVal Feuille As String)
 
 
    If TypeName(TabMatrice) = "Range" Then
 
        'au lieu de ceci ;
    '''''    XRECHERCHEV = Application.WorksheetFunction.VLookup(ValRecherchee, _
    '''''                                                        TabMatrice, _
    '''''                                                        colonneIndex, _
    '''''                                                        True)
 
        'tu utilise VLOOKUP de cette façon :
        XRECHERCHEV = ExecuteExcel4Macro("VLOOKUP(""" & ValRecherchee & _
                                                  """,'[" & Classeur & "]" & _
                                                  Feuille & "'!" & _
                                                  TabMatrice.Address(1, 1, xlR1C1) & "," & _
                                                  colonneIndex & ",TRUE)")
    Else
 
        Dim db As DAO.Database
        Dim rs As DAO.Recordset
        Dim sRange As String
        Dim sSheet As String
        Dim sWbook As String
        Dim sFPath As String
        Dim sSQL   As String
        Dim str As String
 
 
        sRange = Replace(Split(TabMatrice, "!")(1), "$", vbNullString)
        sSheet = Split(Split(TabMatrice, "]")(1), "'")(0)
        sWbook = Split(Split(TabMatrice, "[")(1), "]")(0)
        sFPath = Mid(Split(TabMatrice, "[")(0), 2)
 
        ValRecherchee = "'" & Replace(ValRecherchee, "'", "''") & "'"
 
        sSQL = "SELECT [F" & colonneIndex & "] " & _
               "FROM [" & sSheet & "$" & sRange & "] " & _
               "WHERE [F1] = " & ValRecherchee
 
 
        Set db = DAO.OpenDatabase(sFPath & sWbook, False, False, "Excel 8.0;HDR=NO;")
        Set rs = db.OpenRecordset(sSQL, DAO.dbOpenSnapshot)
 
        If rs.EOF And rs.BOF Then
            XRECHERCHEV = "no match"
        Else
            XRECHERCHEV = rs.Fields(0)
        End If
        Set rs = Nothing
        Set db = Nothing
 
    End If
 
End Function
 
Sub Test()
 
    MsgBox XRECHERCHEV("MonMot", _
                       Range("A1:C34"), _
                       2, _
                       "C:\Dossier1\Dossier2\", _
                       "Classeur1.xls", _
                       "Feuil1")
 
 
End Sub

Hervé.

**JonathanF** · 25/02/2013, 09h43

Bonjour Hervé,

Merci beaucoup pour ton aide.
Du coup la formule change ? Elle n'est plus du type:
=XRECHERCHEV(A2;"'C:\Perso\[" & D2 & "]_Synthèse'!$A$2:$F$35";6)
?

Je n'arrive pas à la retrouver.

Merci d'avance,
Jonathan

**Theze** · 26/02/2013, 13h27

Bonjour,

Désolé, étant plutôt tourné VBA, je n'ai pas percuté que l'appel de la fonction était fait depuis une feuille Excel et là, effectivement ça ne marche pas plus que la fonction que tu as créé (je ne sais d'ailleurs pas pourquoi puisque ça fonctionne très bien en appelant depuis le VBE ???).

Hervé.

**Godzestla** · 26/02/2013, 13h53

Salut Theze,
je passe par hazard et je vois ce post.

J'ai vu ceci dans ton code.

Code :

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

1
2
3
4
5
6
        'tu utilise VLOOKUP de cette façon :
        XRECHERCHEV = ExecuteExcel4Macro("VLOOKUP(""" & ValRecherchee & _
                                                  """,'[" & Classeur & "]" & _
                                                  Feuille & "'!" & _
                                                  TabMatrice.Address(1, 1, xlR1C1) & "," & _
                                                  colonneIndex & ",TRUE)")

J'ai un code qui fait aussi du VLOOKUP sur un autre Classeur et j'ai une syntaxe un poil différente.

Ce code tourne sur le classeur ouvert.
Je n'ai jamais testé sur un classeur fermé.

Code :

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

                   Resu = Evaluate("VLOOKUP(""" & Qry_arg & """,[KSTXXX.xlsm]Pivot_Month!$H$5:$I$1851,2,FALSE)")

Au delà de l'Evaluate et de l'absence de simples quotes autour du nom du classeur et de la feuille (je n'en ai pas besoin, car pas de blanc), tu remarqueras que la différence est dans la formulation de la plage qui est un string.

J'ignore si cela peut aider en quoi que ce soit.

Bonne journée.

**Theze** · 26/02/2013, 15h47

Bonjour,

Hello Godzestla ;o)

Hormis le fait que j'ai oublié de passer le chemin du classeur dans mon dernier exemple, oups :o((, la fonction marche très bien sur un classeur fermé en VBA mais pas en appel depuis une feuille Excel (ci-dessous la fonction corrigé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
 
Public Function XRECHERCHEV(ValRecherchee As Variant, _
                            TabMatrice As Range, _
                            ColonneIndex As Integer, _
                            Chemin As String, _
                            Classeur As String, _
                            Feuille As String)
 
    XRECHERCHEV = ExecuteExcel4Macro("VLOOKUP(""" & ValRecherchee & _
                                              """,'" & Chemin & _
                                              "[" & Classeur & _
                                              "]" & Feuille & _
                                              "'!" & TabMatrice.Address(1, 1, xlR1C1) & _
                                              "," & ColonneIndex & _
                                              ",TRUE)")
 
 
End Function

Cette proc indique bien la valeur cherchée (adapter d'abords les arguments) retournée par la fonction XRECHERCHEV :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
 
Sub Test()
 
    MsgBox XRECHERCHEV("Hervé", _
                       Range("A1:F19"), _
                       3, _
                       "D:\Dossier Test\", _
                       "Classeur1.xls", _
                       "Feuil1")
 
 
End Sub

Dans ton exemple, VlookUp, même avec la fonction Evaluate ne marche pas sur un classeur fermé, seulement si il est ouvert. Je ne comprends pas pourquoi ma fonction (avec ExecuteExcel4Macro) marche avec VBA mais pas avec Excel ???

Hervé.

Recherche v dans un classeur fermé avec variable

Macros et VBA Excel

Discussions similaires

Partager

Partager