Obtenir la liste des noms de fichiers d’un dossier dans Excel (avec et sans VBA)

Lors de mon premier jour dans mon travail dans un petit cabinet de conseil, j’ai été affecté à un court projet pendant trois jours.

Le travail était simple.

Il y avait de nombreux dossiers sur le lecteur réseau et chaque dossier contenait des centaines de fichiers.

J’ai dû suivre ces trois étapes :

  1. Sélectionnez le fichier et copiez son nom.
  2. Collez ce nom dans une cellule d’Excel et appuyez sur Entrée.
  3. Passez au fichier suivant et répétez les étapes 1 et 2.

Cela semble simple, n’est-ce pas ?

C’était – Simple et une énorme perte de temps.

Ce qui m’a pris trois jours aurait pu être fait en quelques minutes si je connaissais les bonnes techniques.

Dans ce tutoriel, je vais vous montrer différentes manières de rendre tout ce processus super rapide et super facile (avec et sans VBA).

Limitations des méthodes présentées dans ce tutoriel : Avec les techniques indiquées ci-dessous, vous ne pourrez obtenir que les noms des fichiers dans le dossier principal. Vous n’obtiendrez pas les noms des fichiers dans les sous-dossiers du dossier principal. Voici un moyen d’obtenir les noms de fichiers à partir de dossiers et sous-dossiers à l’aide de Power Query

Utilisation de la fonction FILES pour obtenir une liste de noms de fichiers à partir d’un dossier

Entendu parler de Fonction FICHIERS avant de?

Ne vous inquiétez pas si vous ne l’avez pas fait.

C’est depuis l’enfance des feuilles de calcul Excel (une formule de la version 4).

Bien que cette formule ne fonctionne pas dans les cellules de la feuille de calcul, elle fonctionne toujours dans les plages nommées. Nous utiliserons ce fait pour obtenir la liste des noms de fichiers d’un dossier spécifié.

Lis  Utilisation de la mise en forme conditionnelle dans Excel (Le guide ultime + exemples)

Maintenant, supposons que vous ayez un dossier avec le nom – ‘Dossier de test‘ sur le bureau et vous souhaitez obtenir une liste des noms de fichiers pour tous les fichiers de ce dossier.

Voici les étapes qui vous donneront les noms de fichiers de ce dossier :

  1. Dans la cellule A1, entrez l’adresse complète du dossier suivie d’un astérisque
    • Par exemple, si votre dossier dans le lecteur C, l’adresse ressemblerait àC:UsersSumitDesktopTest Folder*
    • Adresse du dossier dans une cellule
        • Si vous ne savez pas comment obtenir l’adresse du dossier, utilisez la méthode suivante :
          Dans le dossier à partir duquel vous souhaitez obtenir les noms de fichiers, créez un nouveau classeur Excel ou ouvrez un classeur existant dans le dossier et utilisez la formule ci-dessous dans n'importe quelle cellule.  Cette formule vous donnera l'adresse du dossier et ajoute un signe astérisque[",CELL("filename")),LEN(CELL("filename")),"*")

          [If you have created a new workbook in the folder to use the above formula and get the folder address, you may want to delete it so that it doesn’t feature in the list of files in that folder]

  2. à la fin. Vous pouvez maintenant copier-coller (coller en tant que valeur) cette adresse dans n’importe quelle cellule (A1 dans cet exemple) du classeur dans lequel vous voulez les noms de fichiers.=REMPLACER(CELLULE(
  3. Allez dans l’onglet ‘Formules’ et cliquez sur l’option ‘Définir le nom’.
    • Noms de fichiers d’un dossier dans Excel – Définir le nom
    • Dans la boîte de dialogue Nouveau nom, utilisez les détails suivants
    • Nom : FileNameList (n’hésitez pas à choisir le nom que vous voulez) Portée : Cahier d’exercicesFait référence à:
  4. =FILES(Feuille1!$A$1)
    =IFERROR(INDEX(FileNameList,ROW()-2),"")
  5. Noms de fichiers à partir d’un dossier dans Excel – Définir le nom fait référence à
Lis  Comment insérer une case à cocher dans Excel (pour créer des listes et des graphiques interactifs)

Maintenant, pour obtenir la liste des fichiers, nous allons utiliser la plage nommée dans une fonction INDEX.  Accédez à la cellule A3 (ou à n'importe quelle cellule dans laquelle vous souhaitez que la liste des noms commence) et entrez la formule suivante :

Faites glisser ceci vers le bas et cela vous donnera une liste de tous les noms de fichiers dans le dossier

Obtenir les noms de fichiers d’un dossier à l’aide de la fonction FICHIERS Excel

Vous voulez extraire des fichiers avec une extension spécifique ?? Si vous souhaitez obtenir tous les fichiers avec une extension particulière, modifiez simplement l’astérisque avec cette extension de fichier. Par exemple, si vous voulez uniquement des fichiers Excel, vous pouvez utiliser *xls* au lieu de *

Donc, l’adresse du dossier que vous devez utiliser serait

C:UsersSumitDesktopTest Folder*xls*

De même, pour les fichiers de documents Word, utilisez *doc*

Comment cela marche-t-il?

La formule FILES récupère les noms de tous les fichiers de l’extension spécifiée dans le dossier spécifié. Dans la formule INDEX, nous avons donné les noms de fichiers sous forme de tableau et nous retournons les 1er, 2e, 3e noms de fichier et ainsi de suite en utilisant la fonction ROW.Notez que j’ai utilisé

LIGNE()-2

, car nous avons commencé à partir de la troisième rangée. Ainsi, ROW()-2 serait 1 pour la première instance, 2 pour la deuxième instance lorsque le numéro de ligne est 4, et ainsi de suite.

Regarder la vidéo – Obtenir la liste des noms de fichiers à partir d’un dossier dans Excel

Utilisation de VBA Obtenir une liste de tous les noms de fichiers d’un dossier

Maintenant, je dois dire que la méthode ci-dessus est un peu complexe (avec un certain nombre d’étapes).

Cependant, c’est beaucoup mieux que de le faire manuellement. Mais si vous êtes à l’aise avec l’utilisation de VBA (ou si vous savez bien suivre les étapes exactes que je vais énumérer ci-dessous), vous pouvez créer une fonction personnalisée (UDF) qui peut facilement vous obtenir les noms de tous les fichiers.L’avantage d’utiliser un User affiné

Lis  Convertir le temps en nombre décimal dans Excel (heures, minutes, secondes)

F

Function GetFileNames(ByVal FolderPath As String) As Variant
Dim Result As Variant
Dim i As Integer
Dim MyFile As Object
Dim MyFSO As Object
Dim MyFolder As Object
Dim MyFiles As Object
Set MyFSO = CreateObject("Scripting.FileSystemObject")
Set MyFolder = MyFSO.GetFolder(FolderPath)
Set MyFiles = MyFolder.Files
ReDim Result(1 To MyFiles.Count)
i = 1
For Each MyFile In MyFiles
Result(i) = MyFile.Name
i = i + 1
Next MyFile
GetFileNames = Result
End Function

unction (UDF) est que vous pouvez enregistrer la fonction dans un classeur de macros personnel et la réutiliser facilement sans répéter les étapes encore et encore. Vous pouvez également créer un complément et partager cette fonction avec d’autres.

Maintenant, permettez-moi d’abord de vous donner le code VBA qui créera une fonction pour obtenir la liste de tous les noms de fichiers d’un dossier dans Excel.

Le code ci-dessus créera une fonction GetFileNames qui peut être utilisée dans les feuilles de calcul (tout comme les fonctions normales).

  • Où mettre ce code ?Suivez les étapes ci-dessous pour copier ce code dans l'éditeur VB.
  • Allez dans l’onglet Développeur.Onglet Développeur dans le ruban
  • Cliquez sur le bouton Visual Basic. Cela ouvrira l’éditeur VB.Bouton Visual Basic dans le ruban
  • Dans l’éditeur VB, cliquez avec le bouton droit sur l’un des objets du classeur dans lequel vous travaillez, accédez à Insérer et cliquez sur Module. Si vous ne voyez pas l’explorateur de projet, utilisez le raccourci clavier Ctrl + R (maintenez la touche Ctrl enfoncée et appuyez sur la touche « R »).insérer le module dans l'éditeur VB

Double-cliquez sur l’objet Module et copiez et collez le code ci-dessus dans la fenêtre de code du module.

Copiez le code dans le module pour obtenir le nom de la liste de fichiers à partir d’un dossier

Comment utiliser cette fonction ?

Voici les étapes à suivre pour utiliser cette fonction dans une feuille de calcul : Obtenir la liste des noms de fichiers à partir du dossier à l’aide de la fonction VBA Notez que j’ai entré l’emplacement du dossier dans une cellule, puis utilisé cette cellule dans le

=IFERROR(INDEX(GetFileNames("C:UsersSumitDesktopTest Folder"),ROW()-2),"")

Obtenir les noms de fichiers

Lis  Fonction VBA LCASE - Convertir le texte en minuscules dans Excel

formule. Vous pouvez également coder en dur l’adresse du dossier dans la formule comme indiqué ci-dessous :

Dans la formule ci-dessus, nous avons utilisé ROW()-2 et nous avons commencé à partir de la troisième ligne. Cela garantit que lorsque je copie la formule dans les cellules ci-dessous, elle sera incrémentée de 1. Si vous entrez la formule dans la première ligne d’une colonne, vous pouvez simplement utiliser ROW ().

Comment fonctionne cette formule ?

La formule GetFileNames renvoie un tableau qui contient les noms de tous les fichiers du dossier.

La fonction INDEX permet de lister un nom de fichier par cellule, en commençant par le premier.

La fonction SIERREUR est utilisée pour renvoyer un blanc au lieu de #REF ! erreur qui s’affiche lorsqu’une formule est copiée dans une cellule mais qu’il n’y a plus de noms de fichiers à lister.

Utilisation de VBA Obtenir une liste de tous les noms de fichiers avec une extension spécifique

La formule ci-dessus fonctionne très bien lorsque vous souhaitez obtenir une liste de tous les noms de fichiers d’un dossier dans Excel.

Mais que se passe-t-il si vous souhaitez obtenir uniquement les noms des fichiers vidéo, ou uniquement les fichiers Excel, ou uniquement les noms de fichiers contenant un mot-clé spécifique.

Function GetFileNamesbyExt(ByVal FolderPath As String, FileExt As String) As Variant
Dim Result As Variant
Dim i As Integer
Dim MyFile As Object
Dim MyFSO As Object
Dim MyFolder As Object
Dim MyFiles As Object
Set MyFSO = CreateObject("Scripting.FileSystemObject")
Set MyFolder = MyFSO.GetFolder(FolderPath)
Set MyFiles = MyFolder.Files
ReDim Result(1 To MyFiles.Count)
i = 1
For Each MyFile In MyFiles
If InStr(1, MyFile.Name, FileExt) <> 0 Then
Result(i) = MyFile.Name
i = i + 1
End If
Next MyFile
ReDim Preserve Result(1 To i - 1)
GetFileNamesbyExt = Result
End Function

Dans ce cas, vous pouvez utiliser une fonction légèrement différente.Vous trouverez ci-dessous le code qui vous permettra d’obtenir tous les noms de fichiers contenant un mot-clé spécifique (ou d’une extension spécifique).Le code ci-dessus créera une fonction ‘

Lis  Comment calculer le taux de croissance annuel moyen (TACAM) dans Excel

GetFileNamesbyExt

‘ qui peut être utilisé dans les feuilles de calcul (tout comme les fonctions normales).

Cette fonction prend deux arguments : l’emplacement du dossier et le mot-clé d’extension. Il renvoie un tableau de noms de fichiers qui correspondent à l’extension donnée. Si aucune extension ou mot-clé n’est spécifié, il renverra tous les noms de fichiers dans le dossier spécifié.

Syntaxe : = GetFileNamesbyExt(« Emplacement du dossier », « Extension »)

  • Où mettre ce code ?
  • Suivez les étapes ci-dessous pour copier ce code dans l’éditeur VB.
  • Allez dans l’onglet Développeur.
  • Cliquez sur le bouton Visual Basic. Cela ouvrira l’éditeur VB.

Dans l’éditeur VB, cliquez avec le bouton droit sur l’un des objets du classeur dans lequel vous travaillez, accédez à Insérer et cliquez sur Module. Si vous ne voyez pas l’explorateur de projet, utilisez le raccourci clavier Ctrl + R (maintenez la touche Ctrl enfoncée et appuyez sur la touche « R »).

Double-cliquez sur l’objet Module et copiez et collez le code ci-dessus dans la fenêtre de code du module.

  • Comment utiliser cette fonction ?
  • Voici les étapes à suivre pour utiliser cette fonction dans une feuille de calcul :
  • Dans n’importe quelle cellule, entrez l’adresse du dossier à partir duquel vous souhaitez répertorier les noms de fichiers. J’ai entré ceci dans la cellule A1.
    =IFERROR(INDEX(GetFileNamesbyExt($A$1,$B$1),ROW()-2),"")
  • Dans une cellule, saisissez l’extension (ou le mot-clé), pour laquelle vous souhaitez tous les noms de fichiers. J’ai entré ceci dans la cellule B1.

Dans la cellule où vous voulez la liste, entrez la formule suivante (je l'entre dans la cellule A3) :

Copiez et collez la formule dans les cellules ci-dessous pour obtenir une liste de tous les fichiers. Obtenir des noms de fichiers à partir d’un dossier dans Excel par mot-clé d’extension

Et toi?

Lis  Mettez en surbrillance CHAQUE autre LIGNE dans Excel (à l'aide de la mise en forme conditionnelle)

Toutes les astuces Excel que vous utilisez pour vous faciliter la vie. J’aimerais apprendre de vous. Partagez-le dans la section des commentaires!

Vous aimerez peut-être aussi les didacticiels Excel suivants :

Pour protéger votre fils, votre mari ou votre espion, vous pouvez télécharger cette application

X