Problème de référence à des classeurs fermés

[Sylvain7]

Bonjour,

J'essaye de lire des données depuis des classeurs fermés afin de les regrouper dans un seul classeur. J'ai dans un premeir temps essayer les fonctions INDIRECT et CONCATENER afin d'avoir une référence variable mais INDIRECT ne marche que si les classeurs sont ouverts.
Je me suis donc tourné vers une fonction personnelle LireCellule_ClesseurFerme() trouvée sur le net :

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")
    Rst.Open ADOCommand, , adOpenKeyset, adLockOptimistic
    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

J'ai bien coché la référence Microsoft ActiveX Data Objetcs Library et vérifié mes arguments mais la fonction me renvoi l'erreur #VALEUR. Encore mieux, la fonction marche lorsque le classeur source est ouvert!
Pour info, je suis sur le réseau de la société, donc mon chemin est du type \\serveur\MonService\MonDossierPerso et j'utilise Excel 2007.
Quelqu'un aurait-il une explication pour m'aider à avancer?
Merci d'avance!

 

[Guest]

Re : Problème de référence à des classeurs fermés

bonjour Sylvain, bienvenue sur le forum.
bonjour à tous,

Une autre solution dans ce fil: https://www.excel-downloads.com/thr...r-dans-une-macro-les-valeurs-dun-autre.21964/

Sinon, mets un point d'arrêt (F9) à la ligne :
LireCellule_ClasseurFerme = Rst(0).Value

lance la fonction et quand l'éditeur s'arrête sur cette ligne, Affichage, fenêtre variables locales puis explore la variable Rst pour savoir si elle retourne quelque chose,

Si non explore la variable source pour savoir si elle est ouverte ou fermée (sa propriété State doit être = 1)

A+

 

[Sylvain7]

Re : Problème de référence à des classeurs fermés

Bonjour Hasco et merci pour ta réponse.

En ce qui concerne la macro de Walkenbach je l'avais déjà vu, mais j'avais cru comprendre qu'on ne pouvait faire de executeexcel4macro dans une fonction, hors je compte rester sur l'optique fonction, et quoi qu'il en soit je vais par la suite me servir de ma fonction sur une grande base de données, et je crois que Walkenbach n'est pas adaptée pour un grand nombre de cellule.

Par contre j'ai suivi ta procédure, et elle m'a permis de mettre un évidence un problème assez étrange, j'ai une erreure lorsque j'arrive à la ligne Source.Open qui m'empêche d'aller plus loin. Dans les variables locales pour source j'ai un joli "DefaultDatabase <Cette opération n'est pas autorisée si l'objet est fermé>" et le state à 0.

Donc ca coïncide avec le fait qu'elle marche lorsque le classeur source est ouvert...

 

[Guest]

Re : Problème de référence à des classeurs fermés

Re,

bien qu'en cherchant sur le forum tu trouveras pas mal de solution différentes pour ce genres de chose, j'ai réécrit ta fonction avec certaines modification.

1 - soit tu utilises créateObject (late binding) et tu ne coches pas la référence à Microsoft Active X... et alors ils faut remplacer tous les paramètre par leur valeur brute.

2 - soit tu utilises la référence (earlier binding) et tu enlèves les CreateObject comme ci-dessous.

3 - dans la fonction ci-dessous,:

A - Le paramètre Cellule n'est plus déclaré en variant (qui pourrait être n'importe quoi)
​

Passer au paramètre Cellule une adresse de cellule unique ex: "A3"
la variable Cible est construite à partir de Cellule et testée, si cible n'est pas une référence textuelle à une cellule alors la fonction retourneras #REF

B - La connexion est testée, si elle n'est pas ouverte la fonction retournera #Null
​

la chaine de connexion est modifiée pour travailler avec des fichiers de xl97 à xl2010
le curseur de connexion est changé (adUseClient)

C - Le recordset est testé, s'il n'est pas ouvert la fonction retournera #NA
​

D - dans les autres cas s'il y a une erreur la fonction retourne #Valeur
​

Function LireCellule_ClasseurFerme( _
        Chemin As String, _
        Fichier As String, _
        Feuille As String, _
        Cellule As String) As Variant
 
    Application.Volatile
 
 
    Dim Cible As String, c As Range
    Feuille = Feuille & "$"
 
    Cible = Cellule & ":" & Cellule
 
    'tester si Cible est bien une référence à cellule la testant dans la feuille active
    On Error Resume Next
    Set c = Range(Cible)
    'Si la cilbe n'est pas une addresse de cellule alors renvoyer l'erreur #Ref
    If c Is Nothing Then
        LireCellule_ClasseurFerme = CVErr(xlErrRef)
        Exit Function
    End If
 
 
    Dim Source As New ADODB.Connection
    Source.CursorLocation = adUseClient
    Source.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
        "Data Source=" & Chemin & "\" & Fichier & _
        ";Extended Properties=""Excel 12.0;HDR=No"";"
 
    If Source.State <> adStateOpen Then
        LireCellule_ClasseurFerme = CVErr(xlErrNull)
        Set Source = Nothing
        Exit Function
    End If
 
    Dim Rst As New ADODB.Recordset
    Rst.Open "SELECT * FROM [" & Feuille & Cible & "]", Source, adOpenStatic, adLockBatchOptimistic
 
    If Rst.State = adStateOpen Then
      LireCellule_ClasseurFerme = Rst(0).Value
      Rst.Close
    Else
      LireCellule_ClasseurFerme = CVErr(xlErrNA)
    End If
 
    Source.Close
    Set Source = Nothing
    Set Rst = Nothing
End Function

A+

 

[Sylvain7]

Re : Problème de référence à des classeurs fermés

Ca marche! C'est au top cette fonction!
Vraiment merci pour ton aide!
A +!

 

[Vincent63]

Re : Problème de référence à des classeurs fermés

Ca marche! C'est au top cette fonction!
Vraiment merci pour ton aide!
A +!

Bonjour,

Je suis interessé par votre fonction mais je n'arrive pas à l'utiliser:
J'ai copier le code dans VBA sous ma feuille.
Voici la formule que j'ai rentré pour l'utiliser:
=LireCellule_ClasseurFerme("J:\DIF\LMI_EMBALLAGE\2015\"; "2014 Saisie journaliere.xlsm"; "BILAN ANNUEL"; "$B$1")
mais çà m'affiche:
#NOM?

 

[Guest]

Re : Problème de référence à des classeurs fermés

Bonjour,

[Edit] La réponse ci-dessous ne changera rien voir réponse dans post#9 plus bas:

Essaie de remplacer l'espace par un underscore dans le paramètre Feuile:

=LireCellule_ClasseurFerme("J:\DIF\LMI_EMBALLAGE\2 015\"; "2014 Saisie journaliere.xlsm"; "BILAN_ANNUEL"; "$B$1")


A+

 

[st007]

Re : Problème de référence à des classeurs fermés

Bonjour,

tu as inséré ce code dans un module puis enregistrer en xls ou xlsm ...

 

[Guest]

Re : Problème de référence à des classeurs fermés

Re,

Au fait, ne pas oublier de cochez la référence "Microsoft ActiveX Data object N.NN" dans outils références de l'éditeur VBE (où N.NN est un numéro de version suivant installation).

Sinon la fonction réécrite sans avoir à cocher la référence ci-dessus indiquée.
Elle risque d'être moins performante s'il y a de nombreux appels.

 Function LireCellule_ClasseurFerme( _
         Chemin As String, _
         Fichier As String, _
         Feuille As String, _
         Cellule As String) As Variant
    
    Const adStateOpen = 1
    Const adUseClient = 3
    Const adOpenStatic = 3
    Const adLockBatchOptimistic = 4
     
     Application.Volatile
  
  
     Dim Cible As String, c As Range
     Feuille = Feuille & "$"
  
     Cible = Cellule & ":" & Cellule
  
     'tester si Cible est bien une référence à cellule la testant dans la feuille active
     On Error Resume Next
     Set c = Range(Cible)
     'Si la cilbe n'est pas une addresse de cellule alors renvoyer l'erreur #Ref
     If c Is Nothing Then
         LireCellule_ClasseurFerme = CVErr(xlErrRef)
         Exit Function
     End If
  
  
     Dim Source As Object: Set Source = CreateObject("adodb.connection")
     Source.CursorLocation = adUseClient
     Source.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
         "Data Source=" & Chemin & "\" & Fichier & _
         ";Extended Properties=""Excel 12.0;HDR=No"";"
  
     If Source.State <> adStateOpen Then
         LireCellule_ClasseurFerme = CVErr(xlErrNull)
         Set Source = Nothing
         Exit Function
     End If
  
     Dim Rst As Object: Set Rst = CreateObject("ADODB.Recordset")
     Rst.Open "SELECT * FROM [" & Feuille & Cible & "]", Source, adOpenStatic, adLockBatchOptimistic
  
     If Rst.State = adStateOpen Then
       LireCellule_ClasseurFerme = Rst(0).Value
       Rst.Close
     Else
       LireCellule_ClasseurFerme = CVErr(xlErrNA)
     End If
  
     Source.Close
     Set Source = Nothing
     Set Rst = Nothing
 End Function

A+

 

[Vincent63]

Re : Problème de référence à des classeurs fermés

Merci pour vos réponses.
- J'ai mis un underscore
- J'ai enregistrer au format xlsm
- J'ai repris le code qui ne nécessite pas de cocher l'option activex
- J'ai quand même cocher l'option "Microsoft ActiveX Data Objects 2.0 Library"
Mais çà ne change rien.

J'ai refait un exemple plus simple:

=LireCellule_ClasseurFerme("C:\Dossier\"; "Classeur.xlsx"; "Sheet1"; "A1")

Est-ce bien la bonne syntaxe point virgule, guillemet, anti-slash etc... ?

car le résultat est toujours le même : #NOM?

 

[Guest]

Re : Problème de référence à des classeurs fermés

Re,

Apparement oui, c'est la bonne syntaxe. Mais où as-tu mis cette macro? Module Général? Module de Feuille?
Si tu ne sais pas donne nous ton classeur anonymisé et sans donnée confidentielle.

A+

 

[st007]

Re : Problème de référence à des classeurs fermés

Bonjour hasco,

j'ai essayé aussi en plaçant les codes dans un module standard et #ref,

=LireCellule_ClasseurFerme("D:\";"LISTE.xls";"Feuil1";A1)

avec le fichier "LISTE.xls" à la racine du "D:\" comportant 2 onglets génériques "Feuil1" et "Feuil2"
en A1 du texte
en B1 un nombre

et toujours #ref

 

[st007]

Re : Problème de référence à des classeurs fermés

Avec captures d'écran

 

[st007]

Re : Problème de référence à des classeurs fermés

Avec cette syntaxe, çà fonctionne

=LireCellule_ClasseurFerme("D:\";"LISTE.xls";"Feuil1";"A1")

 

[Guest]

Re : Problème de référence à des classeurs fermés

Re,

Si tu utilises la version 'Late binding' avec createobject il ne faut pas cocher la référence à microsoft activex data object.

Avec les deux versions chez moi, =LireCellule_ClasseurFerme("C:\....\XLD";"h_Source.xlsx";"Feuil1";"A3")

fonctionne parfaitement

A+

[Edit] Je viens de voir la différence entre tes deux syntaxes. Effectivement le paramètre Cellule doit être de type String (chaine de caractère) donc entre guillemet.