[Excel] Réference externe classeurs fermés

Résolu

Sylvain -
schoret Messages postés 5 Date d'inscription Statut Membre Dernière intervention - 28 juil. 2013 à 03:59

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!

A voir également:

Fonction indirect excel autre classeur
Fonction si et excel - Guide
Liste déroulante excel - Guide
Word et excel gratuit - Guide
Excel remplir automatiquement une cellule en fonction d'une autre ✓ - Forum Excel
Fonction moyenne excel - Guide

5 réponses

Réponse 1 / 5

Patrice33740 Messages postés 8561 Date d'inscription Statut Membre Dernière intervention 1 780

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

schoret Messages postés 5 Date d'inscription Statut Membre Dernière intervention

hey super gros merci pour se petit bout de code

cela fait exactements le travail que je voulais

ça fais 2 jours que je cherchais une solution pour lire les fichier fermé.

merci

Réponse 2 / 5

michel_m Messages postés 16602 Date d'inscription Statut Contributeur Dernière intervention 3 314

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

Réponse 3 / 5

Sylvain

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

michel_m Messages postés 16602 Date d'inscription Statut Contributeur Dernière intervention 3 314

Morefun est super mais difficilement portable, hélas!

Au vu de ton fichier, c'est OK (liste de données<==> base de données)

questions: tes fichiers sources sont ils dans le m^me répertoire? est ce toujours la m^me feuille(nom) et la m^me structure de liste de données ? êtes vous tous sous XL2007?

a mon avis, il sera préférable de passer par une macro paramétrée que par une fonction (renvoi d'un tableau)

Je repasse dans 2 ou 3 heures...

Sylvain

Pour l'instant je ne fais le test que sur un classeur (avant de m'amuser à explorer tout un répertoire et pour valider ma fonction avant de bloquer sur des problèmes d'adresse).
Par contre effectivement après j'ai l'intention de lire une grande quantité de classeur, mais il sont tous sous le même type de nom ("Rapport_NomClient"), le même nom de feuille, et le même format, car créé par la même personne (mais malheureusement obligé de faire des classeurs différents), la seule différence est qu'il sont sous des sous dossiers différents mais de même structure, donc il me suffira de changer une partie du chemin pour avoir accès à chacun.
Le classeur source comme celui qui l'apelle sont XL2007 (je les ai créé tous les deux).
Je pense que le problème vient d'ailleurs, en effectuant ma fonction pas à pas, elle bloque sur la ligne Source.Open
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 est à 0.

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

michel_m Messages postés 16602 Date d'inscription Statut Contributeur Dernière intervention 3 314

OK, je te propose quelque chose demain dans la matinée (j'espère)

Sylvain

Pas la peine c'est bon j'ai réussi à trouver quelquechose grâce à l'aide d'Hasco sur le fofo ExcelDownloads, je met le code pour ceux qui aurait besoin de la fonction indirect.ext !
Merci quand même pour ton aide et à la réactivité du fofo!

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

Réponse 4 / 5

michel_m Messages postés 16602 Date d'inscription Statut Contributeur Dernière intervention 3 314

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

Réponse 5 / 5

Sylvain

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!

michel_m Messages postés 16602 Date d'inscription Statut Contributeur Dernière intervention 3 314

Ma réponse a été supprimée par un modérateur comme d'habitude anonyme...

Il est hors de question que je continue d'essayer d'aider "Monsieur" Sylvain; je m'étais engagé à lui répondre ce matin mais un événement tardif fait que je n'aurais guère de temps de libre. J'ai donc écrit une procédure rapidement avant la soirée pour tenir ma parole
si ce "Monsieur" avait averti qu'il avait posé la même question sur d'autre forums (ce qui est son droit) j'y serais allé voir et cela m'aurait évité d'écrire un projet qui reste à améliorer.

Au passage,La fonction proposée est d'une utilité plus que réduite sans texte sql dans les param^tres, cela va de soi mais....

Sylvain

Désolé pour mon erreur, je comprend ta réaction et je préciserais dorénavent si j'ai posé ma question ailleurs (pas pensé désolé), je te prie de m'excuser pour le temps que tu m'avais généreusement accordé. A +

Discussions similaires

Excel copier/coller sans liaison

Importer données d'un autre classeur (fermé) sans macro

Equivalent de INDIRECT pour un fichier fermé SANS MACRO

relier des fichiers Excel pour synchroniser automatiquement

Mise à jour de liaisons excel

"Ce classeur comporte des liaisons avec un autre classeur"

Votre réponse

Discussions similaires