Få listen over filnavn fra en mappe i Excel (med og uten VBA)

På min første dag i jobben i et lite konsulentfirma ble jeg bemannet på et kort prosjekt i tre dager.

Arbeidet var enkelt.

Det var mange mapper på nettverksstasjonen, og hver mappe hadde hundrevis av filer i den.

Jeg måtte følge disse tre trinnene:

1. Velg filen og kopier navnet.
2. Lim inn det navnet i en celle i Excel og trykk Enter.
3. Gå til neste fil og gjenta trinn 1 og 2.

Høres enkelt ut?

Det var - Enkelt og en enorm sløsing med tid.

Det som tok meg tre dager kunne ha blitt gjort på få minutter hvis jeg kjente de riktige teknikkene.

I denne opplæringen vil jeg vise deg forskjellige måter å gjøre hele prosessen superrask og superenkel (med og uten VBA).

Begrensninger for metodene vist i denne opplæringen: Med teknikkene vist nedenfor vil du bare kunne få navnene på filene i hovedmappen. Du får ikke navnene på filene i undermappene i hovedmappen. Her er en måte å få navn på filer fra mapper og undermapper ved hjelp av Power Query

Bruke FILES -funksjon for å få en liste over filnavn fra en mappe

Hørt om FILER -funksjon før?

Ikke bekymre deg hvis du ikke har det.

Det er fra barndommens dager med Excel -regneark (en versjon 4 -formel).

Selv om denne formelen ikke fungerer i regnearkcellene, fungerer den fortsatt i navngitte områder. Vi vil bruke dette faktum for å få listen over filnavn fra en spesifisert mappe.

Anta at du har en mappe med navnet - ‘Testmappe'På skrivebordet, og du vil få en liste over filnavn for alle filene i denne mappen.

Her er trinnene som gir deg filnavnene fra denne mappen:

1. I celle A1 skriver du inn mappens fullstendige adresse etterfulgt av et stjernetegn (*)
   • For eksempel, hvis mappen din i C -stasjonen, ser adressen ut
     C: \ Users \ Sumit \ Desktop \ Testmappe \*
   • Hvis du ikke er sikker på hvordan du får mappeadressen, bruker du følgende metode:
     • • I mappen du vil hente filnavnene fra, må du enten opprette en ny Excel -arbeidsbok eller åpne en eksisterende arbeidsbok i mappen og bruke formelen nedenfor i en hvilken som helst celle. Denne formelen gir deg mappeadressen og legger til et stjernetegn (*) på slutten. Nå kan du kopiere og lime inn (lime inn som verdi) denne adressen i en hvilken som helst celle (A1 i dette eksemplet) i arbeidsboken der du vil ha filnavnene.

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

         [Hvis du har opprettet en ny arbeidsbok i mappen for å bruke formelen ovenfor og få mappeadressen, kan det være lurt å slette den slik at den ikke finnes i listen over filer i den mappen]
2. Gå til "Formler" -fanen og klikk på "Definer navn" -alternativet.
3. Bruk følgende detaljer i dialogboksen Nytt navn
   • Navn: FileNameList (velg gjerne hvilket navn du liker)
   • Omfang: Arbeidsbok
   • Henviser til: = FILER (Sheet1! $ A $ 1)
4. For å få listen over filer, bruker vi det navngitte området i en INDEX -funksjon. Gå til celle A3 (eller hvilken som helst celle der du vil at listen med navn skal starte) og skriv inn følgende formel:

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

5. Dra dette ned, og det vil gi deg en liste over alle filnavnene i mappen

Vil du trekke ut filer med en spesifikk utvidelse ??

Hvis du vil få alle filene med en bestemt utvidelse, må du bare endre stjernen med den filtypen. For eksempel, hvis du bare vil ha excel -filer, kan du bruke * xls * i stedet for *

Så mappeadressen du må bruke vil være C: \ Users \ Sumit \ Desktop \ Testmappe \*xls*

På samme måte, for word -dokumentfiler, bruk *doc *

Hvordan virker dette?

FILES -formelen henter navnene på alle filene i den angitte utvidelsen i den angitte mappen.

I INDEX -formelen har vi gitt filnavnene som array, og vi returnerer 1., 2., 3. filnavn og så videre ved å bruke ROW -funksjonen.

Legg merke til at jeg har brukt RAD ()-2, som vi startet fra tredje rad og utover. Så RAD ()-2 ville være 1 for første forekomst, 2 for andre forekomst når radnummeret er 4, og så videre og så videre.

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

Bruke VBA Få en liste over alle filnavnene fra en mappe

Nå må jeg si at metoden ovenfor er litt kompleks (med et antall trinn).

Det er imidlertid mye bedre enn å gjøre dette manuelt.

Men hvis du er komfortabel med å bruke VBA (eller hvis du er flink til å følge de nøyaktige trinnene som jeg skal liste nedenfor), kan du lage en tilpasset funksjon (UDF) som enkelt kan gi deg navnene på alle filene.

Fordelen med å bruke a User Defined Function (UDF) er at du kan lagre funksjonen i en personlig makro arbeidsbok og bruke den enkelt uten å gjenta trinnene igjen og igjen. Du kan også opprette et tillegg og dele denne funksjonen med andre.

La meg først gi deg VBA -koden som vil lage en funksjon for å få listen over alle filnavnene fra en mappe i Excel.

Funksjon GetFileNames (ByVal FolderPath As 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 Sett MyFSO = CreateObject ("Scripting.FileSystemObject") Sett MyFolder = MyFSO. GetFolder (FolderPath) Angi MyFiles = MyFolder.Files ReDim -resultat (1 til MyFiles.Count) i = 1 For hver MyFile I MyFiles -resultat (i) = MyFile.Name i = i + 1 Neste MyFile GetFileNames = Resultat Sluttfunksjon

Koden ovenfor vil opprette en funksjon GetFileNames som kan brukes i regnearkene (akkurat som vanlige funksjoner).

Hvor skal jeg legge denne koden?

Følg trinnene nedenfor for å kopiere denne koden i VB Editor.

• Gå til kategorien Utvikler.
• Klikk på Visual Basic -knappen. Dette åpner VB Editor.
• I VB Editor, høyreklikk på et av objektene i arbeidsboken du jobber med, gå til Sett inn og klikk på Modul. Hvis du ikke ser Project Explorer, bruker du hurtigtasten Ctrl + R (hold kontrolltasten og trykk "R" -tasten).
• Dobbeltklikk på modulobjektet og kopier og lim inn koden ovenfor i modulkodevinduet.

Hvordan bruke denne funksjonen?

Nedenfor er trinnene for å bruke denne funksjonen i et regneark:

• I hvilken som helst celle skriver du inn mappeadressen til mappen du vil vise filnavnene fra.
• I cellen du vil ha listen, skriver du inn følgende formel (jeg skriver den inn i celle A3):

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

• Kopier og lim inn formelen i cellene nedenfor for å få en liste over alle filene.

Vær oppmerksom på at jeg skrev inn mappens plassering i en celle og deretter brukte den cellen i GetFileNames formel. Du kan også hardkode mappeadressen i formelen som vist nedenfor:

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

I formelen ovenfor har vi brukt RAD ()-2 og vi startet fra tredje rad og fremover. Dette sørget for at når jeg kopierer formelen i cellene nedenfor, økes den med 1. Hvis du skriver inn formelen i den første raden i en kolonne, kan du bare bruke RAD ().

Hvordan fungerer denne formelen?

Formelen GetFileNames returnerer en matrise som inneholder navnene på alle filene i mappen.

INDEX -funksjonen brukes til å liste ett filnavn per celle, fra den første.

IFERROR -funksjonen brukes til å returnere blank i stedet for #REF! feil som vises når en formel kopieres i en celle, men det er ikke flere filnavn å vise.

Bruke VBA Få en liste over alle filnavnene med en bestemt utvidelse

Formelen ovenfor fungerer bra når du vil få en liste over alle filnavnene fra en mappe i Excel.

Men hva om du bare vil ha navnene på videofilene, eller bare Excel -filene, eller bare filnavnene som inneholder et bestemt søkeord.

I så fall kan du bruke en litt annen funksjon.

Nedenfor er koden som lar deg få alle filnavnene med et bestemt søkeord i det (eller en bestemt utvidelse).

Funksjon GetFileNamesbyExt (ByVal FolderPath Som String, FileExt Som String) Som Variant Dim Resultat Som Variant Dim i Som Heltall Dim MyFile Som Object Dim MyFSO Som Object Dim MyFolder Som Object Dim MyFiles Som Objekt Sett MyFSO = CreateObject ("Scripting.FileSystemObject") Sett MyFolder = MyFSO.GetFolder (FolderPath) Sett MyFiles = MyFolder.Files ReDim Resultat (1 til MyFiles.Count) i = 1 For hver MyFile I MyFiles If InStr (1, MyFile.Name, FileExt) 0 Deretter Resultat (i) = MyFile .Name i = i + 1 End If Next MyFile ReDim Bevar resultat (1 til i - 1) GetFileNamesbyExt = Resultatsluttfunksjon

Koden ovenfor vil opprette en funksjon 'GetFileNamesbyExt'Som kan brukes i regnearkene (akkurat som vanlige funksjoner).

Denne funksjonen tar to argumenter - mapplasseringen og utvidelsesnøkkelordet. Den returnerer en rekke filnavn som samsvarer med den angitte utvidelsen. Hvis det ikke er angitt noen utvidelse eller et nøkkelord, returnerer det alle filnavnene i den angitte mappen.

Syntaks: = GetFileNamesbyExt ("mapplokasjon", "utvidelse")

Hvor skal jeg legge denne koden?

Følg trinnene nedenfor for å kopiere denne koden i VB Editor.

• Gå til kategorien Utvikler.
• Klikk på Visual Basic -knappen. Dette åpner VB Editor.
• I VB Editor, høyreklikk på et av objektene i arbeidsboken du jobber med, gå til Sett inn og klikk på Modul. Hvis du ikke ser Project Explorer, bruker du hurtigtasten Ctrl + R (hold kontrolltasten og trykk "R" -tasten).
• Dobbeltklikk på modulobjektet og kopier og lim inn koden ovenfor i modulkodevinduet.

Hvordan bruke denne funksjonen?

Nedenfor er trinnene for å bruke denne funksjonen i et regneark:

• I hvilken som helst celle skriver du inn mappeadressen til mappen du vil vise filnavnene fra. Jeg har lagt inn dette i celle A1.
• I en celle skriver du inn utvidelsen (eller søkeordet), som du vil ha alle filnavnene for. Jeg har lagt inn dette i celle B1.
• I cellen du vil ha listen, skriver du inn følgende formel (jeg skriver den inn i celle A3):

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

• Kopier og lim inn formelen i cellene nedenfor for å få en liste over alle filene.

Hva med deg? Eventuelle Excel -triks som du bruker for å gjøre livet enkelt. Jeg vil gjerne lære av deg. Del det i kommentarfeltet!