Hanki luettelo tiedostonimien kansioista Excelissä (VBA: n kanssa ja ilman)

Ensimmäisenä päivänä työssäni pienessä konsulttiyrityksessä työskentelin lyhyessä projektissa kolme päivää.

Työ oli yksinkertaista.

Verkkoasemassa oli monia kansioita ja jokaisessa kansiossa oli satoja tiedostoja.

Minun oli noudatettava näitä kolmea vaihetta:

1. Valitse tiedosto ja kopioi sen nimi.
2. Liitä nimi Excel -soluun ja paina Enter.
3. Siirry seuraavaan tiedostoon ja toista vaiheet 1 ja 2.

Kuulostaa yksinkertaiselta?

Se oli - Yksinkertaista ja valtavaa ajanhukkaa.

Se, mikä kesti kolme päivää, olisi voitu tehdä muutamassa minuutissa, jos tietäisin oikeat tekniikat.

Tässä opetusohjelmassa näytän sinulle erilaisia ​​tapoja tehdä koko prosessista erittäin nopea ja erittäin helppo (VBA: n kanssa ja ilman sitä).

Tässä opetusohjelmassa esitettyjen menetelmien rajoitukset: Seuraavalla tekniikalla voit saada vain pääkansion tiedostojen nimet. Et saa pääkansion alikansioiden tiedostojen nimiä. Tässä on tapa saada tiedostojen nimet kansioista ja alikansioista Power Queryn avulla

FILES -toiminnon käyttäminen luettelon tiedostonimien saamiseksi kansiosta

Kuullut FILES -toiminto ennen?

Älä huoli, jos et ole.

Se on Excel -laskentataulukoiden (versio 4 -kaava) lapsuuden päiviltä.

Vaikka tämä kaava ei toimi laskentataulukon soluissa, se toimii edelleen nimetyillä alueilla. Käytämme tätä tosiasiaa saadaksemme luettelon tiedostonimistä määritetystä kansiosta.

Oletetaan nyt, että sinulla on kansio, jonka nimi on "Testikansio"Työpöydällä ja haluat saada luettelon kaikkien tämän kansion tiedostojen tiedostojen nimistä.

Tässä on vaiheet, jotka antavat sinulle tämän kansion tiedostojen nimet:

1. Kirjoita soluun A1 kansion täydellinen osoite ja tähti (*)
   • Jos esimerkiksi kansio on C -asemassa, osoite näyttää tältä
     C: \ Users \ Sumit \ Desktop \ Test Folder \*
   • Jos et ole varma, miten saat kansion osoitteen, käytä seuraavaa menetelmää:
     • • Luo kansioon, josta haluat hakea tiedostonimet, joko luo uusi Excel -työkirja tai avaa olemassa oleva työkirja kansiossa ja käytä alla olevaa kaavaa missä tahansa solussa. Tämä kaava antaa sinulle kansion osoitteen ja lisää siihen tähtimerkin (*). Nyt voit kopioida ja liittää (liittää arvoksi) tämän osoitteen mihin tahansa soluun (tässä esimerkissä A1) työkirjassa, johon haluat tiedostonimet.

         = VAIHDA (CELL ("tiedostonimi"), FIND ("[", CELL ("tiedostonimi")), LEN (CELL ("tiedostonimi")), "*")

         [Jos olet luonut kansioon uuden työkirjan käyttääksesi yllä olevaa kaavaa ja saadaksesi kansion osoitteen, sinun kannattaa ehkä poistaa se, jotta se ei näy kyseisen kansion tiedostoluettelossa]
2. Siirry Kaavat -välilehteen ja napsauta Määrittele nimi -vaihtoehtoa.
3. Käytä Uusi nimi -valintaikkunassa seuraavia tietoja
   • Nimi: FileNameList (voit vapaasti valita haluamasi nimen)
   • Soveltamisala: Työkirja
   • Viittaa seuraaviin: = FILES (Sheet1! $ A $ 1)
4. Tiedostojen saamiseksi käytämme nyt INDEX -funktion nimettyä aluetta. Siirry soluun A3 (tai mihin tahansa soluun, jossa haluat nimiluettelon alkavan) ja kirjoita seuraava kaava:

   = IFERROR (INDEX (TiedostonimiLista, RIVI ()-2), "")

5. Vedä tämä alas ja saat luettelon kaikista kansion tiedostojen nimistä

Haluatko purkaa tiedostoja tietyllä laajennuksella ??

Jos haluat saada kaikki tiedostot tietyllä laajennuksella, muuta vain tähti kyseisellä tiedostopääteellä. Jos haluat esimerkiksi vain Excel -tiedostoja, voit käyttää * xls * -merkkiä *: n sijasta

Joten kansion osoite, jota sinun on käytettävä, olisi C: \ Users \ Sumit \ Desktop \ Test Folder \*xls*

Samoin Word -asiakirjatiedostoille käytä *doc *

Miten tämä toimii?

FILES -kaava hakee kaikkien määritetyn laajennuksen tiedostojen nimet määritetystä kansiosta.

INDEX -kaavassa olemme antaneet tiedostonimet taulukkona ja palautamme 1., 2., 3. tiedostonimen ja niin edelleen käyttämällä ROW -funktiota.

Huomaa, että olen käyttänyt RIVI ()-2, kun aloitimme kolmannesta rivistä eteenpäin. Joten RIVI ()-2 olisi 1 ensimmäisessä tapauksessa, 2 toisessa tapauksessa, kun rivin numero on 4, ja niin edelleen ja niin edelleen.

Katso video - Hae luettelo tiedostonimistä Excelin kansiosta

VBA: n käyttäminen Hanki luettelo kaikista kansioiden tiedostojen nimistä

Nyt minun on sanottava, että yllä oleva menetelmä on hieman monimutkainen (useilla vaiheilla).

Se on kuitenkin paljon parempi kuin tehdä tämä manuaalisesti.

Mutta jos olet tyytyväinen VBA: n käyttöön (tai jos olet hyvä noudattamaan tarkkoja vaiheita, jotka aion luetella alla), voit luoda mukautetun toiminnon (UDF), joka voi helposti saada kaikkien tiedostojen nimet.

A: n käytön hyöty User Dparannettu FUnction (UDF) tarkoittaa, että voit tallentaa toiminnon henkilökohtaiseen makrotyökirjaasi ja käyttää sitä uudelleen helposti toistamatta vaiheita uudestaan ​​ja uudestaan. Voit myös luoda lisäosan ja jakaa tämän toiminnon muiden kanssa.

Anna minun ensin antaa sinulle VBA -koodi, joka luo toiminnon saadaksesi luettelon kaikista tiedostonimistä Excel -kansiosta.

Funktio GetFileNames (ByVal FolderPath as String) Variant Dim Dim tuloksena Variant Dim Dim kokonaislukuna Dim MyFile as Object Dim MyFSO Object Dim MyFolder Object Dim MyFiles as Object Set MyFSO = CreateObject ("Scripting.FileSystemObject") Aseta MyFolder = MyFSO. GetFolder (FolderPath) Aseta MyFiles = MyFolder.Files ReDim -tulos (1 MyFiles.Count) i = 1 Jokaiselle MyFile -tiedostolle Tulos (i) = MyFile.Name i = i + 1 Next MyFile GetFileNames = Result End Function

Yllä oleva koodi luo funktion GetFileNames, jota voidaan käyttää laskentataulukoissa (aivan kuten tavallisia toimintoja).

Minne tämä koodi laitetaan?

Kopioi tämä koodi VB -editorissa seuraavasti.

• Siirry Kehittäjä -välilehdelle.
• Napsauta Visual Basic -painiketta. Tämä avaa VB -editorin.
• Napsauta VB-editorissa hiiren kakkospainikkeella mitä tahansa työkirjan objektia, jossa työskentelet, siirry Lisää-kohtaan ja napsauta Moduuli. Jos et näe Project Exploreria, käytä pikanäppäintä Control + R (pidä ohjausnäppäintä painettuna ja paina R -näppäintä).
• Kaksoisnapsauta Module -objektia ja kopioi ja liitä yllä oleva koodi moduulin koodi -ikkunaan.

Kuinka käyttää tätä toimintoa?

Alla on ohjeet tämän toiminnon käyttämiseen laskentataulukossa:

• Kirjoita mihin tahansa soluun sen kansion osoite, josta haluat luetteloida tiedostonimet.
• Kirjoita seuraava kaava soluun, johon haluat luettelon (kirjoitan sen soluun A3):

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

• Kopioi ja liitä kaava alla oleviin soluihin saadaksesi luettelon kaikista tiedostoista.

Huomaa, että kirjoitin kansion sijainnin soluun ja käytin sitten kyseistä solua GetFileNames kaava. Voit myös kovakoodata kansion osoitteen alla olevassa kaavassa:

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

Yllä olevassa kaavassa olemme käyttäneet ROW ()-2 ja aloitimme kolmannesta rivistä eteenpäin. Tämä varmisti, että kun kopioin kaavan alla oleviin soluihin, se kasvaa yhdellä. Jos kirjoitat kaavan sarakkeen ensimmäiselle riville, voit käyttää yksinkertaisesti ROW ().

Kuinka tämä kaava toimii?

GetFileNames -kaava palauttaa taulukon, joka sisältää kaikkien kansion tiedostojen nimet.

INDEX -toimintoa käytetään luetteloimaan yksi tiedostonimi solua kohden alkaen ensimmäisestä.

IFERROR -funktiota käytetään palauttamaan tyhjä #REF! virhe, joka näkyy, kun kaava kopioidaan soluun, mutta luettelossa ei ole enää tiedostonimiä.

VBA: n käyttäminen Hanki luettelo kaikista tiedostonimistä, joilla on tietty laajennus

Yllä oleva kaava toimii hyvin, kun haluat saada luettelon kaikista tiedostonimistä Excel -kansiosta.

Mutta entä jos haluat saada vain videotiedostojen nimet tai vain Excel -tiedostot tai vain tietyn avainsanan sisältävät tiedostonimet.

Tässä tapauksessa voit käyttää hieman erilaista toimintoa.

Alla on koodi, jonka avulla saat kaikki tiedostonimet, joissa on tietty avainsana (tai tietty laajennus).

Toiminto GetFileNamesbyExt (ByVal FolderPath merkkijonona, FileExt merkkijonona) Varianttina Dim Tulos Variantti Dim i Kokonaisluku Dim MyFile Objektina Dim MyFSO objektina Dim MyFolder As Objekt Dim MyFiles Object Set MyFSO = CreateObject ("Scripting.FileSystemObject") MyFolder = MyFSO.GetFolder (FolderPath) Aseta MyFiles = MyFolder.Files ReDim -tulos (1 MyFiles.Count) i = 1 Jokaiselle MyFile In My Files -tiedostolle If InStr (1, MyFile.Name, FileExt) 0 Sitten tulos (i) = MyFile .Nimi i = i + 1 Lopeta, jos seuraava MyFile ReDim säilyttää tuloksen (1 - i - 1) GetFileNamesbyExt = Tuloksen lopetusfunktio

Yllä oleva koodi luo toiminnon "GetFileNamesbyExt"Jota voidaan käyttää laskentataulukoissa (kuten tavallisia toimintoja).

Tämä toiminto sisältää kaksi argumenttia - kansion sijainnin ja laajennuksen avainsanan. Se palauttaa joukon tiedostonimiä, jotka vastaavat annettua laajennusta. Jos laajennusta tai avainsanaa ei ole määritetty, se palauttaa kaikki määritetyn kansion tiedostonimet.

Syntaksi: = GetFileNamesbyExt ("Kansion sijainti", "Laajennus")

Minne tämä koodi laitetaan?

Kopioi tämä koodi VB -editorissa seuraavasti.

• Siirry Kehittäjä -välilehdelle.
• Napsauta Visual Basic -painiketta. Tämä avaa VB -editorin.
• Napsauta VB-editorissa hiiren kakkospainikkeella mitä tahansa työkirjan objektia, jossa työskentelet, siirry Lisää-kohtaan ja napsauta Moduuli. Jos et näe Project Exploreria, käytä pikanäppäintä Control + R (pidä ohjausnäppäintä painettuna ja paina R -näppäintä).
• Kaksoisnapsauta Module -objektia ja kopioi ja liitä yllä oleva koodi moduulin koodi -ikkunaan.

Kuinka käyttää tätä toimintoa?

Alla on ohjeet tämän toiminnon käyttämiseen laskentataulukossa:

• Kirjoita mihin tahansa soluun sen kansion osoite, josta haluat luetteloida tiedostonimet. Olen kirjoittanut tämän soluun A1.
• Kirjoita soluun laajennus (tai avainsana), jolle haluat kaikki tiedostonimet. Olen kirjoittanut tämän soluun B1.
• Kirjoita seuraava kaava soluun, johon haluat luettelon (kirjoitan sen soluun A3):

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

• Kopioi ja liitä kaava alla oleviin soluihin saadaksesi luettelon kaikista tiedostoista.

Entä sinä? Kaikki Excel -temput, joita käytät helpottamaan elämää. Haluaisin oppia sinulta. Jaa se kommenttikentässä!