[Excel] Réference externe classeurs fermés Résolu

Question

Bonjour,  

J'essaye de lire des données sur des classeurs fermés afin de regrouper les infos sur un seul classeur.  

Mon but est d'avoir une référence variable, j'ai donc d'abord essayé avec les fonction INDIRECT et CONCATENER avant de découvrir que INDIRECT est limité aux classeurs ouverts.  

J'ai donc trouvé une fonction VBA LireCellule_ClasseurFerme() sur le net pour pallier à ce problème, mais elle me renvoi l'erreur #VALEUR, et je me suis même aperçu que la fonction marchait si le classeur était ouvert (ce qui m'a doucement fait rire!), quelqu'un aurait-il une explication qui pourrait m'aider à avancer? 

Voilà le code de la fonction : 

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 = , 3 = 
    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

Je précise que j'ai bien pris soin de cocher la référence Microsoft ActiveX Data objects Library dans les outils Visual Basic. 

Merci pour votre aide! 

Configuration: Windows XP / Firefox 3.6.12

michel_m · Answer

Bonjour

Le ou les classeurs source doivent répondre à des conditions bien précises
notamment les cellules cibles doivent ^tre du type liste de données; cad données du m^me type dans une m^me colonne: texte,nombre, date
et les formats ne sont pas copiés

Il y a aussi qqchose de bizarre dans cette syntaxe
LireCellule_ClasseurFerme = Rst(0).Value 


D'autre part si tu as peu de données de cellules à copier, tu peux utiliser la macro dite de walkenbach
un exemple basique à adapter (chemin, nom du fichier, cellules...)
Sub lire_ferme()

Dim chemin As String

chemin = ThisWorkbook.Path
Range("A3") = ExecuteExcel4Macro("'" & chemin & "\[source.xls]Feuil1'!R1C1")
Range("B4") = ExecuteExcel4Macro("'" & chemin & "\[source.xls]Feuil1'!R2C2")
Range("C8") = ExecuteExcel4Macro("'" & chemin & "\[source.xls]Feuil1'!R5C3")
Range("E9") = ExecuteExcel4Macro("'" & chemin & "\[source.xls]Feuil1'!R7C4")
End Sub


si tu utilises ADo, met en pièce jointe un extrait de ton classeur source

pour joindre une pièce
mettre le classeur sans données confidentielles en pièce jointe
https://www.cjoint.com/ 
et faire un clic droit sur le lien proposé puis "copier l'adresse du lien" et coller dans le message de réponse

Sylvain · Answer

D'abord merci pour ta réponse michel, 

Quand tu parles de type, est ce que tu entends "format"? Si c'est le cas les données de mon tableau source sont toutes du même type dans chaque colonne. Mais après tout ta remarque me semble être une bonne piste puisque j'avais déjà eu un problème de format dans ce tableau source, en fait ce tableau est lui même exporté depuis le progiciel de la boîte. 

Effectivement j'avais déjà vu la macro de walkenbach mais je compte utiliser ma fonction pour exploiter par la suite toute une base de donnée (pour un autre classeur excel, celui là me servant plutôt de test), d'autre part j'aimerais si possible rester dans l'optique "fonction" afin que les utilisateurs du classeurs puisse avoir un suivi des valeurs des cellules. 

Pour être clair, car je sais qu'avant d'essayer de coder n'importe quoi faut déjà bien définir ce que l'on veut, j'aimerais une fonction du type indirect.ext, c'est à dire la fonction indirect mais qui fonctionne sur des classeurs fermés. Indirect.ext est une fonction de l'add-on MoreFunc, mais je ne peux pas le télécharger (interdit par la boîte), et impossible de retrouver le code VBA puisqu'il est tout simplement coder sous C! 

Voilà un extrait de mon classeur source : 

http://cjoint.com/?AJAnhkY3STv

Patrice33740 · Answer

On peut lire dans un classeur fermé avec une simple formule : 

Sub LireFichierFermé() 
Dim NomFichier As String 
Dim Chemin As String 
Dim Formule As String 

  ActiveSheet.Range("A1").ClearContents 
   
  Chemin = "D:\Temp\" 
  NomFichier = "Classeur_à_lire.xls" 
  Formule = "='" & Chemin & "[" & NomFichier & "]Feuil1" & "'!" & "A1" 
  ActiveSheet.Range("A1").Formula = Formule 
  ActiveSheet.Range("A1").Value = ActiveSheet.Range("A1").Value 

End Sub 

Cordialement 
Patrice

michel_m · Answer

un peu compliqué XLD.... 

Const racine As String = "D:\documents\excel\essai\adodemo\" 'a adapter 
Const onglet As String = "TestBD" 
Const plage As String = "A1:P1000" 

Sub compiler() 
Dim ss_dossier, Cptr As Byte, Chemin As String 
Dim Source As Object, Requete As Object, texte_SQL As String 
Dim derlig As Long 

ss_dossier = Array("ccmA", "ccmB") 'a adapter 

For Cptr = 0 To UBound(ss_dossier) 
     'recherche des classeurs dans les sous dossiers 
     Chemin = racine & ss_dossier(Cptr) 
     ChDir Chemin 
     fichier = Dir(Chemin & "\rapport_nomclient" & "*.xlsx") 
      
     'connection au classeur 
     Set Source = CreateObject("ADODB.Connection") 
     With Source 
        .Provider = "Microsoft.Jet.OLEDB.4.0" 
        .ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" _ 
            & fichier & ";Extended Properties=""Excel 12.0;HDR=YES;""" 
        .Open 
     End With 
      
     ' éxécute l'extraction des données 
     Set Requete = CreateObject("ADODB.Recordset") 
     texte_SQL = "SELECT * FROM [" & onglet & "$" & plage & "] " 
     Set Requete = Source.Execute(texte_SQL) 
      
     'restitution 
    If Not Requete.EOF Then 
          derlig = Columns(2).Find("*", , , , , xlPrevious).Row + 1 
          Sheets(1).Cells(derlig, 2).CopyFromRecordset Requete 
    End If 
Next 
Sheets(1).Activate 
End Sub 


https://www.cjoint.com/?3JAtTXYFpVj 

Mais à partir de maintenant, je m'abstiendrai de me casser le cul pour aider quelqu'un en allant vérifier s'il n'a pas posé la question sur un autre forum sans prévenir...triste pour ce qui est de l'entraide
 Michel

Sylvain · Answer

Effectivement ta solution est simple et très efficace Patrice, j'ai failli m'en vouloir de ne pas y avoir pensé, mais ce n'est pas ce que je recherche... à moins d'en faire une fonction ou je peux saisir mes arguments. 
J'aime l'idée de créer mon propre répertoire de fonction, car je suis plus à l'aise sur excel que sur VBA et en plus mes utilisateurs pourront tout de suite voir d'où viennent les valeurs des cellules, car quand je ne serais plus là (je suis en stage), je veux qu'ils puissent quand même s'en sortir s'ils changent les dossiers de place (donc les adresses) et la configuration des classeurs (donc les cellules), sinon mon boulot n'aura servi à rien!
Je suis en train de regarder ta solution Michel, même si ça n'est pas non plus une fonction, mais ça va surement m'aider pour le traitement de ma base, j'essaierais de voir ce que ça donne demain!

[Excel] Réference externe classeurs fermés

5 réponses

Votre réponse

Discussions similaires

Newsletters