Een lijst met bestandsnamen uit een map krijgen met Excel (met en zonder VBA)

Op mijn eerste dag bij een klein adviesbureau kreeg ik een driedaags kortlopend project toegewezen.Het werk is eenvoudig.

Er zijn veel mappen op een netwerkstation, elk met honderden bestanden.

Ik moest deze drie stappen volgen:

1. Selecteer het bestand en kopieer de naam.
2. Plak de naam in een cel in Excel en druk op Enter.
3. Ga naar het volgende bestand en herhaal stap 1 en 2.

Klinkt eenvoudig, toch?

Het is eenvoudig en zeer tijdrovend.

Wat ik in drie dagen heb gedaan, kan in minuten worden gedaan als ik de juiste techniek ken.

In deze tutorial laat ik je verschillende manieren zien om het hele proces supersnel en supergemakkelijk te maken (met en zonder VBA).

Beperkingen van de methoden die in deze zelfstudie worden getoond:Met behulp van de onderstaande techniek kunt u alleen de namen van de bestanden in uw thuismap krijgen.U krijgt niet de namen van bestanden in submappen in de hoofdmap.Hier is een manier om bestandsnamen uit mappen en submappen op te halen met Power Query

Gebruik de FILES-functie om een ​​lijst met bestandsnamen uit een map te krijgen

ervan gehoordFILES-functie?

Als je dat niet doet, maak je geen zorgen.

Het komt uit een Excel-spreadsheet uit de kindertijd (versie 4-formules).

Hoewel deze formule niet werkt met werkbladcellen, werkt deze nog steeds met benoemde bereiken.We zullen dit feit gebruiken om een ​​lijst met bestandsnamen uit de opgegeven map te krijgen.

Stel nu dat u een bestand op uw bureaublad heeft met de naam " Testmap ” en u wilt een lijst met bestandsnamen krijgen voor alle bestanden in die map.

Dit zijn de stappen die u de bestandsnamen in deze map geven:

1. Voer in cel A1 het volledige mapadres in gevolgd door een asterisk (*)
   • Als uw map zich bijvoorbeeld in de C-schijf bevond, zou het adres zoiets zijn als:
     C:\Gebruikers\UW NAAM\Bureaublad\Testmap\*
   • Als u niet zeker weet hoe u het mapadres kunt krijgen, gebruikt u de volgende methode:
     • • Maak in de map waaruit u de bestandsnaam wilt halen een nieuwe Excel-werkmap of open een bestaande werkmap in de map en gebruik de volgende formule in een willekeurige cel.Deze formule geeft u het mapadres met een asterisk (*) aan het einde.U kunt dit adres nu kopiëren en plakken (plakken als waarde) in elke cel in de werkmap (A1 in dit voorbeeld) waar u de bestandsnaam wilt hebben.

         =REPLACE(CELL("bestandsnaam"),FIND("[",CELL("bestandsnaam")),LEN(CELL("bestandsnaam")),"*")

         [Als u een nieuwe werkmap in een map hebt gemaakt om de bovenstaande formule te gebruiken en het mapadres op te halen, moet u deze mogelijk verwijderen zodat deze niet in de lijst met bestanden in die map staat]

2. Ga naar het tabblad "Formules" en klik op de optie "Namen definiëren".
3. Gebruik in het dialoogvenster Nieuwe naam de volgende details:
   • Naam: FileNameList (voel je vrij om elke gewenste naam te kiezen)
   • Toepassingsgebied: werkboek
   • Verwijst naar: =BESTANDEN(Blad1!$A$1)
4. Om nu een lijst met bestanden te krijgen, gebruiken we een benoemd bereik in de INDEX-functie.Ga naar cel A3 (of welke cel u ook wilt dat de lijst met namen begint) en voer de volgende formule in:

   =IFERROR(INDEX(Bestandsnaamlijst,RIJ()-2,"")

5. Sleep het naar beneden en je krijgt een lijst met alle bestandsnamen in de map

Wilt u bestanden uitpakken met een specifieke extensie? ?

Als u alle bestanden met een specifieke extensie wilt krijgen, wijzigt u gewoon de asterisk met die bestandsextensie.Als u bijvoorbeeld alleen het Excel-bestand wilt, kunt u *xls* gebruiken in plaats van *

Dus het mapadres dat u moet gebruiken isC:GebruikersSumitDesktopTestmap*xls*

Evenzo, voor Word-documentbestanden, gebruik *doc*

Hoe werkt dit?

De FILES-formule haalt de namen op van alle bestanden met de opgegeven extensie in de opgegeven map.

In de INDEX-formule geven we de bestandsnamen als een array en gebruiken we de ROW-functie om de eerste, tweede, derde, enz. bestandsnamen terug te geven.

Merk op dat ik gebruikteRIJ()-2, aangezien we op de derde regel beginnen.Dus als het rijnummer 4 is, is ROW()-2 1 voor de eerste instantie, 2 voor de tweede, enzovoort.

Krijg een lijst met alle bestandsnamen uit een map met VBA

Nu moet ik zeggen dat de bovenstaande methode een beetje ingewikkeld is (er zijn veel stappen).

Het is echter veel beter dan dit handmatig te doen.

Als u echter vertrouwd bent met het gebruik van VBA (of als u goed bent in het volgen van de exacte stappen die ik hieronder zal opsommen), kunt u een aangepaste functie (UDF) maken die gemakkelijk de namen van alle bestanden kan krijgen.

Voordelen van het gebruik van door de gebruiker gedefinieerde functies (UDF's)是jekan將functieSla op in persoonlijke macrowerkmappen en gebruik ze eenvoudig opnieuw zonder deze stappen steeds opnieuw te hoeven herhalen.U kunt ook add-ons maken en deze functionaliteit met anderen delen.

Laat me je nu eerst de VBA-code geven die een functie zal maken om een ​​lijst met alle bestandsnamen uit een map in Excel te krijgen.

Functie GetFileNames(ByVal FolderPath As String) As Variant Dim Resultaat 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") Stel MyFolder = MyFSO in. GetFolder(FolderPath) Set MyFiles = MyFolder.Files ReDim Resultaat(1 To MyFiles.Count) i = 1 For Each MyFile In MyFiles Result(i) = MyFile.Name i = i + 1 Next MyFile GetFileNames = Resultaat Eindfunctie

De bovenstaande code maakt een functie GetFileNames aan die in het werkblad kan worden gebruikt (net als een gewone functie).

Waar plaats je deze code?

Volg de onderstaande stappen om deze code in de VB-editor te kopiëren.

Hoe deze functie te gebruiken?

Dit zijn de stappen om deze functie in een werkblad te gebruiken:

• Voer in een willekeurige cel het mapadres in van de map waarvan u de bestandsnamen wilt weergeven.
• Voer in de cel waar u de lijst wilt hebben de volgende formule in (ik heb deze in cel A3) ingevoerd:

  =IFERROR(INDEX(GetFileNames($A$1),ROW()-2,"")

• Kopieer en plak de formule in de cel hieronder om een ​​lijst met alle bestanden te krijgen.

Merk op dat ik de maplocatie in één cel heb ingevoerd en vervolgens inBestandsnamen ophalenDeze cel wordt gebruikt in een formule.U kunt het mapadres in de formule ook als volgt hardcoderen:

=IFERROR(INDEX(GetFileNames("C:\Users\YOUR NAME\Desktop\Test Folder"),ROW()-2,"")

In de bovenstaande formule hebben we ROW()-2 gebruikt en we beginnen met de derde rij.Dit zorgt ervoor dat wanneer ik de formule in de cel eronder kopieer, deze met 1 wordt verhoogd.Als u de formule in de eerste rij van de kolom invoert, kunt u eenvoudig ROW() gebruiken.

Hoe werkt deze formule?

De formule GetFileNames retourneert een matrix met de namen van alle bestanden in de map.

De INDEX-functie wordt gebruikt om één bestandsnaam per cel weer te geven, te beginnen met de eerste.

De IFERROR-functie wordt gebruikt om blanco te retourneren in plaats van #REF!Fout die wordt weergegeven wanneer een formule in een cel wordt gekopieerd, maar er zijn geen bestandsnamen meer om weer te geven.

Krijg een lijst van alle bestandsnamen met een specifieke extensie met VBA

De bovenstaande formule is handig als u een lijst met alle bestandsnamen uit een map in Excel wilt krijgen.

Maar wat als u alleen de naam van een videobestand wilt krijgen, of alleen een Excel-bestand, of alleen een bestand dat een bepaald trefwoord bevat.

In dit geval kunt u een iets andere functie gebruiken.

Met de onderstaande code kunt u alle bestandsnamen krijgen die een specifiek trefwoord (of een specifieke extensie) bevatten.

Functie GetFileNamesbyExt(ByVal FolderPath As String, FileExt As String) As Variant Dim Resultaat 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 Dan Resultaat(i) = MijnBestand.Naam i = i + 1 Einde Als Volgende MijnBestand ReDim Resultaat behouden (1 To i - 1) GetFileNamesbyExt = Resultaat Einde Functie

De bovenstaande code maakt een functie die in het werkblad kan worden gebruikt" GetFileNamesbyExt " (net als reguliere functies).

Deze functie heeft twee parameters: de maplocatie en het extensiesleutelwoord.Het retourneert een reeks bestandsnamen die overeenkomen met de opgegeven extensie.Als er geen extensie of trefwoord is opgegeven, worden alle bestandsnamen in de opgegeven map geretourneerd.

Syntaxis: =GetFileNamesbyExt("Maplocatie","Extensie")

Waar plaats je deze code?

Volg de onderstaande stappen om deze code in de VB-editor te kopiëren.

• Ga naar het tabblad Ontwikkelaars.
• Klik op de Visual Basic-knop.Dit opent de VB-editor.
• Klik in de VB-editor met de rechtermuisknop op een willekeurig object in de werkmap waaraan u werkt, ga naar Invoegen en klik op Module.Als je Project Explorer niet ziet, gebruik dan de sneltoets Control + R (houd de Ctrl-toets ingedrukt en druk op "R").
• Dubbelklik op het moduleobject en kopieer en plak de bovenstaande code in het modulecodevenster.

Hoe deze functie te gebruiken?

Dit zijn de stappen om deze functie in een werkblad te gebruiken:

• Voer in een willekeurige cel het mapadres in van de map waarvan u de bestandsnamen wilt weergeven.Dit heb ik ingevuld in cel A1.
• Voer in de cel de extensie (of trefwoord) in die u wilt dat alle bestandsnamen zijn.Dit heb ik ingevuld in cel B1.
• Voer in de cel waar u de lijst wilt hebben de volgende formule in (ik heb deze in cel A3) ingevoerd:

  =IFERROR(INDEX(GetFileNamesbyExt($A$1,$B$1),ROW()-2,"")

• Kopieer en plak de formule in de cel hieronder om een ​​lijst met alle bestanden te krijgen.

Hoe zit het met jouAlle Excel-trucs die u gebruikt om uw leven te vereenvoudigen.Ik leer graag van je.Deel het in de comments!