Få listen over filnavne fra en mappe i Excel (med og uden VBA)

På min første dag i mit job i et lille konsulentfirma blev jeg bemandet på et kort projekt i tre dage.

Arbejdet var enkelt.

Der var mange mapper på netværksdrevet, og hver mappe havde hundredvis af filer i det.

Jeg var nødt til at følge disse tre trin:

1. Vælg filen, og kopier dens navn.
2. Indsæt dette navn i en celle i Excel, og tryk på Enter.
3. Gå til den næste fil, og gentag trin 1 og 2.

Lyder det simpelt?

Det var - Enkelt og et enormt spild af tid.

Det, der tog mig tre dage, kunne have været gjort på få minutter, hvis jeg kendte de rigtige teknikker.

I denne vejledning vil jeg vise dig forskellige måder at gøre hele denne proces superhurtig og super let (med og uden VBA).

Begrænsninger af metoderne vist i denne vejledning: Med de viste teknikker nedenfor vil du kun kunne få navnene på filerne i hovedmappen. Du får ikke navnene på filerne i undermapperne i hovedmappen. Her er en måde at få navne på filer fra mapper og undermapper ved hjælp af Power Query

Brug FILES -funktion til at få en liste over filnavne fra en mappe

Hørt om FILES funktion Før?

Bare rolig, hvis du ikke har.

Det er fra barndommens dage i Excel -regneark (en version 4 -formel).

Selvom denne formel ikke fungerer i regnearkets celler, fungerer den stadig i navngivne områder. Vi vil bruge denne kendsgerning til at få listen over filnavne fra en bestemt mappe.

Antag nu, at du har en mappe med navnet - 'Testmappe'På skrivebordet, og du vil have en liste over filnavne til alle filerne i denne mappe.

Her er trinene, der giver dig filnavne fra denne mappe:

1. I celle A1 skal du indtaste mappens fulde adresse efterfulgt af et stjernetegn (*)
   • For eksempel, hvis din mappe i C -drevet, så ville adressen se ud
     C: \ Brugere \ Sumit \ Desktop \ Testmappe \*
   • Hvis du ikke er sikker på, hvordan du får mappeadressen, skal du bruge følgende metode:
     • • I den mappe, hvorfra du vil hente filnavnene, skal du enten oprette en ny Excel -projektmappe eller åbne en eksisterende projektmappe i mappen og bruge nedenstående formel i en hvilken som helst celle. Denne formel giver dig mappeadressen og tilføjer et stjernetegn (*) i slutningen. Nu kan du kopiere og indsætte (indsæt som værdi) denne adresse i en hvilken som helst celle (A1 i dette eksempel) i projektmappen, hvor du vil have filnavnene.

         = ERSTAT (CELL ("filnavn"), FIND ("[", CELL ("filnavn")), LEN (CELL ("filnavn")), "*")

         [Hvis du har oprettet en ny projektmappe i mappen for at bruge ovenstående formel og få mappeadressen, kan du slette den, så den ikke findes på listen over filer i den pågældende mappe]
2. Gå til fanen 'Formler', og klik på 'Definer navn'.
3. Brug følgende oplysninger i dialogboksen Nyt navn
   • Navn: FileNameList (vælg gerne hvilket navn du vil)
   • Anvendelsesområde: Arbejdsbog
   • Henviser til: = FILES (Sheet1! $ A $ 1)
4. For at få listen over filer bruger vi det navngivne område inden for en INDEX -funktion. Gå til celle A3 (eller enhver celle, hvor du vil have listen med navne til at starte), og indtast følgende formel:

   = IFERROR (INDEX (FileNameList, ROW ()-2), "")

5. Træk dette ned, og det giver dig en liste over alle filnavne i mappen

Vil du udtrække filer med en specifik udvidelse ??

Hvis du vil have alle filerne med en bestemt udvidelse, skal du bare ændre stjernen med den filtypenavn. Hvis du f.eks. Kun vil have excel -filer, kan du bruge * xls * i stedet for *

Så den mappeadresse, du skal bruge, ville være C: \ Brugere \ Sumit \ Desktop \ Testmappe \*xls*

På samme måde skal du bruge *doc *til word -dokumentfiler

Hvordan virker det?

FILES formel henter navnene på alle filerne i den angivne udvidelse i den angivne mappe.

I INDEX -formlen har vi givet filnavnene som array, og vi returnerer 1., 2., 3. filnavne og så videre ved hjælp af ROW -funktionen.

Bemærk at jeg har brugt RÆKKE ()-2, da vi startede fra tredje række og frem. Så RÆK ()-2 ville være 1 for den første forekomst, 2 for den anden forekomst, når rækkenummeret er 4, og så videre og så videre.

Se video - Få liste over filnavne fra en mappe i Excel

Brug af VBA Få en liste over alle filnavne fra en mappe

Nu må jeg sige, at ovenstående metode er lidt kompleks (med et antal trin).

Det er dog meget bedre end at gøre dette manuelt.

Men hvis du er fortrolig med at bruge VBA (eller hvis du er god til at følge de nøjagtige trin, som jeg vil liste nedenfor), kan du oprette en brugerdefineret funktion (UDF), der let kan få dig navnene på alle filerne.

Fordelen ved at bruge en User Defined Function (UDF) er, at du kan gemme funktionen i en personlig makro -projektmappe og nemt genbruge den uden at gentage trinene igen og igen. Du kan også oprette et tilføjelsesprogram og dele denne funktion med andre.

Lad mig nu først give dig VBA -koden, der vil oprette en funktion for at få listen over alle filnavne fra en mappe i Excel.

Funktion GetFileNames (ByVal FolderPath Som String) Som Variant Dim Resultat Som Variant Dim i As Integer Dim MyFile As Object Dim MyFSO Som Object Dim MyFolder Som Object Dim MyFiles Som Object Set MyFSO = CreateObject ("Scripting.FileSystemObject") Indstil MyFolder = MyFSO. GetFolder (FolderPath) Indstil MyFiles = MyFolder.Files ReDim -resultat (1 til MyFiles.Count) i = 1 For hver MyFile In MyFiles -resultat (i) = MyFile.Name i = i + 1 Næste MyFile GetFileNames = Resultat Slutfunktion

Ovenstående kode vil oprette en funktion GetFileNames, der kan bruges i regnearkene (ligesom almindelige funktioner).

Hvor skal denne kode placeres?

Følg nedenstående trin for at kopiere denne kode i VB Editor.

• Gå til fanen Udvikler.
• Klik på knappen Visual Basic. Dette åbner VB Editor.
• I VB Editor skal du højreklikke på ethvert af objekterne i den projektmappe, du arbejder i, gå til Indsæt og klik på Modul. Hvis du ikke kan se Project Explorer, skal du bruge tastaturgenvejen Control + R (hold kontroltasten nede og tryk på 'R' -tasten).
• Dobbeltklik på modulobjektet, og kopier og indsæt ovenstående kode i modulkodevinduet.

Hvordan bruges denne funktion?

Nedenfor er trinene til brug af denne funktion i et regneark:

• I en hvilken som helst celle skal du indtaste mappeadressen for den mappe, hvorfra du vil liste filnavnene.
• I den celle, hvor du vil have listen, skal du indtaste følgende formel (jeg indtaster den i celle A3):

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

• Kopier og indsæt formlen i cellerne herunder for at få en liste over alle filerne.

Bemærk, at jeg indtastede mappens placering i en celle og derefter brugte den celle i GetFileNames formel. Du kan også hard kode mappeadressen i formlen som vist herunder:

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

I ovenstående formel har vi brugt ROW ()-2, og vi startede fra tredje række og frem. Dette sørgede for, at når jeg kopierer formlen i cellerne herunder, vil den blive forøget med 1. Hvis du indtaster formlen i den første række i en kolonne, kan du simpelthen bruge ROW ().

Hvordan fungerer denne formel?

Formlen GetFileNames returnerer en matrix, der indeholder navnene på alle filerne i mappen.

INDEX -funktionen bruges til at angive et filnavn pr. Celle fra den første.

IFERROR -funktionen bruges til at returnere blank i stedet for #REF! fejl, der vises, når en formel kopieres i en celle, men der ikke er flere filnavne at liste.

Brug af VBA Få en liste over alle filnavne med en specifik udvidelse

Ovenstående formel fungerer godt, når du vil få en liste over alle filnavne fra en mappe i Excel.

Men hvad nu hvis du kun vil have navnene på videofilerne, eller kun Excel -filerne, eller kun de filnavne, der indeholder et bestemt søgeord.

I så fald kan du bruge en lidt anden funktion.

Nedenfor er koden, der giver dig mulighed for at få alle filnavne med et specifikt søgeord i det (eller en bestemt udvidelse).

Funktion GetFileNamesbyExt (ByVal FolderPath Som String, FileExt Som String) Som Variant Dim Resultat Som Variant Dim i Som Heltal Dim MyFile Som Object Dim MyFSO Som Object Dim MyFolder Som Object Dim MyFiles Som Objektsæt MyFSO = CreateObject ("Scripting.FileSystemObject") Set MyFolder = MyFSO.GetFolder (FolderPath) Indstil MyFiles = MyFolder.Files ReDim Resultat (1 til MyFiles.Count) i = 1 For hver MyFile I MyFiles If InStr (1, MyFile.Name, FileExt) 0 Så Resultat (i) = MyFile .Name i = i + 1 End If Next MyFile ReDim Bevar resultat (1 til i - 1) GetFileNamesbyExt = Resultat afslutningsfunktion

Ovenstående kode vil oprette en funktion 'GetFileNamesbyExt'Der kan bruges i regnearkene (ligesom almindelige funktioner).

Denne funktion tager to argumenter - mappens placering og udvidelsessøgeordet. Det returnerer en række filnavne, der matcher den givne udvidelse. Hvis der ikke er angivet nogen udvidelse eller et nøgleord, returnerer det alle filnavne i den angivne mappe.

Syntaks: = GetFileNamesbyExt ("Mappeplacering", "Udvidelse")

Hvor skal denne kode placeres?

Følg nedenstående trin for at kopiere denne kode i VB Editor.

• Gå til fanen Udvikler.
• Klik på knappen Visual Basic. Dette åbner VB Editor.
• I VB Editor skal du højreklikke på ethvert af objekterne i den projektmappe, du arbejder i, gå til Indsæt og klik på Modul. Hvis du ikke kan se Project Explorer, skal du bruge tastaturgenvejen Control + R (hold kontroltasten nede og tryk på 'R' -tasten).
• Dobbeltklik på modulobjektet, og kopier og indsæt ovenstående kode i modulkodevinduet.

Hvordan bruges denne funktion?

Nedenfor er trinene til brug af denne funktion i et regneark:

• I en hvilken som helst celle skal du indtaste mappeadressen for den mappe, hvorfra du vil liste filnavnene. Jeg har indtastet dette i celle A1.
• I en celle skal du indtaste udvidelsen (eller søgeordet), som du vil have alle filnavne for. Jeg har indtastet dette i celle B1.
• I den celle, hvor du vil have listen, skal du indtaste følgende formel (jeg indtaster den i celle A3):

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

• Kopier og indsæt formlen i cellerne herunder for at få en liste over alle filerne.

Hvad med dig? Alle Excel -tricks, som du bruger til at gøre livet let. Jeg ville elske at lære af dig. Del det i kommentarfeltet!