Discussion :

[deved95]

Bonjour A tous,

je suis un nouvel utilisateur du code VBA. Je souhaite faire une recherche V avec un chemin dynamique et sur des fichiers fermés.
Il est assez simple de faire cela avec la fonction indirect mais lorsque le fichier est fermé la valeur ne s'affiche pas et j'ai un code erreur.

J'ai trouvé ce code qui permet de créer une fonction qui contournerait ce problème sauf que celle-ci ne donne rien. Le fichier doit quand même étre ouvert pour afficher la valeur. Pourriez vous m'aidez svp ?

Voici la solution apportée par @SilkyRoad mais qui ne fonctionne pas chez moi

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
Function LireCellule_ClasseurFerme( _
        Chemin As String, _
        Fichier As String, _
        Feuille As String, _
        Cellule As Variant) As Variant
 
    Application.Volatile
 
    Dim Source As Object, Rst As Object, ADOCommand As Object
    Dim Cible As String
 
    Feuille = Feuille & "$"
    Cible = Cellule.Address(0, 0, xlA1, 0) & ":" & _
        Cellule.Address(0, 0, xlA1, 0)
 
    Set Source = CreateObject("ADODB.Connection")
    Source.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=" & Chemin & "\" & Fichier & _
        ";Extended Properties=""Excel 8.0;HDR=No;"";"
 
    Set ADOCommand = CreateObject("ADODB.Command")
    With ADOCommand
        .ActiveConnection = Source
        .CommandText = "SELECT * FROM [" & Feuille & Cible & "]"
    End With
 
    Set Rst = CreateObject("ADODB.Recordset")
    '1 = adOpenKeyset, 3 = adLockOptimistic
    Rst.Open ADOCommand, , 1, 3
    Set Rst = Source.Execute("[" & Feuille & Cible & "]")
 
    LireCellule_ClasseurFerme = Rst(0).Value
 
    Rst.Close
    Source.Close
    Set Source = Nothing
    Set Rst = Nothing
    Set ADOCommand = Nothing
End Function

Vous pouvez ensuite insérer ce type de formule dans une cellule :

Code formule :

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

=LireCellule_ClasseurFerme(A1;A2;A3;G7)

A1 correspond au chemin :
C:\Documents and Settings\mimi\dossier

A2 correspond au nom du classeur :
ClasseurY.xls

A3 correspond au nom de la feuille :
Feuil1

G7 correspond à la cellule qui doit être lue dans le classeur fermé.

[barpasc]

le script vba fonctionne très bien.

Assurez vous d'avoir les bons arguments en paramètres dans la fonction

Assurez voir d'avoir l'option de calcul automatique

[Pierre Fauconnier]

Salut.

Deux problèmes avec la fonction que tu utilises:

• elle crée une connexion au fichier source à chaque fois qu'elle est calculée. Ca risque de ramer un peu;
• elle utilise un driver pour les xls (Excel 97-2003). Pour les fichiers plus récents, tu dois utiliser quelque chose du genre: Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\myFolder\myExcel2007file.xlsx;Extended Properties="Excel 12.0 Xml;HDR=YES";.

Travailler sur des classeurs fermés ou utiliser du RECHERCHEV interclasseurs n'est pas une bonne approche, selon moi. Il me semblerait préférable de rapatrier les données dans le classeur de travail avec Power Query et de travailler en local, voire de réaliser le RECHERCHEV en Power Query (voir les jointures Power Query)

[barpasc]

sinon, pour savoir ce qui ne fonctionne pas, dans l'ide vba de la fonction LireCellule_ClasseurFerme, créer un point d'arrêt à la dernière ligne "End Function" et sur la feuille éditer la cellule qui contient la formule avec F2 par exemple et valider avec Entrée. Dans l'ide vba vous pourrez accéder à toutes les valeurs dans les variables et arguments de la fonction et les propriétés des objets.

N'hésitez pas à informer la ligne qui bug. Il se peut que ce soit une erreur de version de l'application mais théoriquement un code ancien adodb fonctionne avec les nouvelles versions à ma connaissance.

[Pierre Fauconnier]

[...]
[...]théoriquement un code ancien adodb fonctionne avec les nouvelles versions à ma connaissance.

La compatibilité ne fonctionne pas dans ce sens-là, et le driver Excel 8.0 ne sait pas lire un fichier XLSX, à mon avis, puisque ce format n'existait pas au moment ou le driver a été créé . Je serais d'ailleurs surpris que le driver 12.0 lise les fichiers xls, mais je n'ai pas fait le test...

[barpasc]

Je n'ai pas une connaissance extensible des drivers, j'ai testé le code de deved95 sur xl2007, je n'ai pas testé sur d'autres versions et je ne sais pas. Sur xl2007, le script fonctionne.