VBA - Excel : Lire dans un classeur fermé

Sous VBA, les différentes techniques de lecture de données dans un fichier Excel fermé sont assez rebutantes par leur complexité. Cependant, il existe une petite astuce permettant de contourner ceci, sans utiliser de connexion OLE DB, mais uniquement pour la lecture.

Introduction

Nous avons besoin, dans notre classeur « Recap », d'avoir les informations situées dans la plage de cellules « A1 :F10 » de la feuille « Feuil1 » d'un classeur « source », lui-même situé dans le répertoire « C:\Pijaku\Ne_fait_rien_de_ses_journées\CCM\ ».

Donc pour commencer, ouvrons un classeur, inscrivons « Pijaku » en Feuil1 de la cellule A1 à la cellule F10 et enregistrons le sous « source.xls » dans le répertoire : C:\Pijaku\Ne_fait_rien_de_ses_journées\CCM\.
Fermons ce classeur.
Ouvrons un second classeur Excel et enregistrons le sous « Recap.xls » dans le bureau (par exemple).

La méthode, par Excel :

Dans votre classeur Recap.xls :

• Insérer un nom défini :
  • Menu : Insertion
  • Choix : Nom
  • Cliquer sur : Définir

Noms dans le classeur => saisir : plage
Fait référence à => saisir : ='C:\Pijaku\Ne_fait_rien_de_ses_journées\CCM\[source.xls]Feuil1'!$A$1:$F$10

Cliquer sur Ajouter.

• Dans votre classeur, en A1 saisir la formule : =plage

puis valider.
Etirer cette formule sur toute la plage de A1 à F10...

Le code VBA correspondant

Sub ImporterDonneesSansOuvrir()     
Dim Chemin As String, Fichier As String     
Chemin = "C:\Pijaku\Ne_fait_rien_de_ses_journées\CCM\"     
Fichier = "source.xls"     
ThisWorkbook.Names.Add "plage", _     
            RefersTo:="='" & Chemin & "[" & Fichier & "]Feuil1'!$A$1:$F$10"     
    With Sheets("Feuil2")     
        .[A1:F10] = "=plage"     
        .[A1:F10].Copy     
        Sheets("Feuil1").Range("A1").PasteSpecial xlPasteValues     
        .[A1:F10].Clear     
    End With     
End Sub

Précautions d'emploi

Surtout, vérifiez bien le chemin d'accès au répertoire, l'orthographe, la casse sont très important. Aussi à vérifier le « \ » entre le chemin du répertoire et le nom du classeur.

Exemple d'utilisation

Ce code boucle sur tous les classeurs Excel d'un répertoire et en extrait le contenu de la cellule A1 Feuil1, en l'occurence pour l'exemple, une date.

Option Explicit   

Sub ImporterDates()   
Dim objShell As Object, objFolder As Object   
Dim Chemin As String, fichier As String   

    Set objShell = CreateObject("Shell.Application")   
    Set objFolder = objShell.BrowseForFolder(&H0&, "Choisir un répertoire", &H1&)   

If objFolder Is Nothing Then   
    MsgBox "Abandon opérateur", vbCritical, "Annulation"   
Else   
    Columns(1).NumberFormat = "m/d/yyyy"   
    Chemin = objFolder.ParentFolder.ParseName(objFolder.Title).Path & "\"   
    [B1] = Chemin   
    fichier = Dir(Chemin & "*.xls")   
    Do While Len(fichier) > 0   
        If fichier <> ThisWorkbook.Name Then   
            ThisWorkbook.Names.Add "Plage", _   
            RefersTo:="='" & Chemin & "[" & fichier & "]Feuil1'!$A$1"   
            With Sheets("Feuil2")   
                .[A1] = "=Plage"   
                .[A1].Copy   
                Sheets("Feuil1").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues   
                Sheets("Feuil1").Range("A" & Rows.Count).End(xlUp).Offset(0, 1) = fichier   
            End With   
        End If   
        fichier = Dir()   
    Loop   
End If   
End Sub   

Téléchargement

Vous pouvez télécharger le classeur source exemple ainsi que le classeur « recap » utilisés en première partie de cette astuce.
Egalement disponible en téléchargement un zip de l'exemple d'extration en boucle.

Si toutefois ceux-ci n'étaient plus disponibles sur cjoint, merci de me le faire savoir en m'envoyant un MP ici, cliquez sur « Lui écrire un message » Ce document intitulé « VBA - Excel : Lire dans un classeur fermé » issu de CommentCaMarche (www.commentcamarche.net) est mis à disposition sous les termes de la licence Creative Commons. Vous pouvez copier, modifier des copies de cette page, dans les conditions fixées par la licence, tant que cette note apparaît clairement.