Dapatkan senarai nama fail daripada folder menggunakan Excel (dengan dan tanpa VBA)

Pada hari pertama saya di firma perunding kecil, saya telah diberikan projek jangka pendek tiga hari.Kerjanya mudah.

Terdapat banyak folder pada pemacu rangkaian, setiap satu dengan beratus-ratus fail.

Saya terpaksa mengikuti tiga langkah ini:

1. Pilih fail dan salin namanya.
2. Tampalkan nama ke dalam sel dalam Excel dan tekan Enter.
3. Beralih ke fail seterusnya dan ulangi langkah 1 dan 2.

Kedengaran mudah, bukan?

Ianya mudah dan sangat memakan masa.

Apa yang saya lakukan dalam tiga hari boleh dilakukan dalam beberapa minit jika saya tahu teknik yang betul.

Dalam tutorial ini, saya akan menunjukkan kepada anda cara yang berbeza untuk menjadikan keseluruhan proses sangat pantas dan sangat mudah (dengan dan tanpa VBA).

Had kaedah yang ditunjukkan dalam tutorial ini:Menggunakan teknik yang ditunjukkan di bawah, anda hanya boleh mendapatkan nama fail dalam folder rumah anda.Anda tidak mendapat nama fail dalam subfolder dalam folder utama.Berikut ialah cara untuk mendapatkan nama fail daripada folder dan subfolder menggunakan Power Query

Gunakan fungsi FILES untuk mendapatkan senarai nama fail daripada folder

mendengar tentangnyafungsi FILES?

Jika anda tidak, jangan risau.

Ia daripada hamparan Excel dari zaman kanak-kanak (formula versi 4).

Walaupun formula ini tidak berfungsi dengan sel lembaran kerja, ia masih berfungsi dengan julat bernama.Kami akan menggunakan fakta ini untuk mendapatkan senarai nama fail daripada folder yang ditentukan.

Sekarang, katakan anda mempunyai fail pada desktop anda yang dipanggil " Folder Ujian ” dan anda ingin mendapatkan senarai nama fail untuk semua fail dalam folder itu.

Berikut ialah langkah-langkah yang akan memberi anda nama fail dalam folder ini:

1. Dalam sel A1, masukkan alamat folder penuh diikuti dengan asterisk (*)
   • Sebagai contoh, jika folder anda berada dalam pemacu C, alamatnya adalah seperti
     C:\Users\NAMA ANDA\Desktop\Test Folder\*
   • Jika anda tidak pasti cara mendapatkan alamat folder, gunakan kaedah berikut:
     • • Dalam folder yang anda ingin dapatkan nama fail, buat buku kerja Excel baharu atau buka buku kerja sedia ada dalam folder dan gunakan formula berikut dalam mana-mana sel.Formula ini akan memberi anda alamat folder dengan asterisk (*) pada penghujungnya.Anda kini boleh salin-tampal (tampal sebagai nilai) alamat ini ke dalam mana-mana sel dalam buku kerja (A1 dalam contoh ini) di mana anda mahu nama fail.

         =REPLACE(CELL("nama fail"),FIND("[",CELL("nama fail")),LEN(CELL("nama fail"),"*")

         [Jika anda mencipta buku kerja baharu dalam folder untuk menggunakan formula di atas dan mendapatkan alamat folder, anda mungkin perlu memadamkannya supaya ia tiada dalam senarai fail dalam folder itu]

2. Pergi ke tab "Formula" dan klik pilihan "Tentukan Nama".
3. Dalam dialog Nama Baharu, gunakan butiran berikut
   • Nama: FileNameList (sila pilih mana-mana nama yang anda suka)
   • Skop: Buku kerja
   • Merujuk kepada: =FILES(Helaian1!$A$1)
4. Sekarang untuk mendapatkan senarai fail, kami akan menggunakan julat bernama dalam fungsi INDEX.Pergi ke sel A3 (atau sel apa sahaja yang anda mahu senarai nama dimulakan) dan masukkan formula berikut:

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

5. Seret ke bawah dan ia akan memberikan anda senarai semua nama fail dalam folder

Ingin mengekstrak fail dengan sambungan tertentu? ?

Jika anda ingin mendapatkan semua fail dengan sambungan tertentu, cuma tukar asterisk dengan sambungan fail tersebut.Sebagai contoh, jika anda hanya mahukan fail excel, anda boleh menggunakan *xls* dan bukannya *

Jadi alamat folder yang anda perlu gunakan ialahC:UsersSumitDesktopTest Folder*xls*

Begitu juga, untuk fail dokumen perkataan, gunakan *doc*

Bagaimana ianya berfungsi?

Formula FILES mendapatkan semula nama semua fail dengan sambungan yang ditentukan dalam folder yang ditentukan.

Dalam formula INDEX, kami memberikan nama fail sebagai tatasusunan dan menggunakan fungsi ROW untuk mengembalikan nama fail pertama, kedua, ketiga, dsb.

Perhatikan bahawa saya telah menggunakanROW()-2, kerana kita bermula pada baris ketiga.Jadi apabila nombor baris ialah 4, ROW()-2 akan menjadi 1 untuk contoh pertama, 2 untuk yang kedua, dan seterusnya.

Dapatkan senarai semua nama fail daripada folder menggunakan VBA

Sekarang, saya mesti mengatakan bahawa kaedah di atas agak rumit (terdapat banyak langkah).

Walau bagaimanapun, ia adalah lebih baik daripada melakukan ini secara manual.

Walau bagaimanapun, jika anda selesa menggunakan VBA (atau jika anda mahir mengikut langkah tepat yang saya akan senaraikan di bawah), anda boleh mencipta fungsi tersuai (UDF) yang boleh mendapatkan nama semua fail dengan mudah.

Faedah menggunakan Fungsi Ditentukan Pengguna (UDF)是AndaBoleh將fungsiSimpan dalam buku kerja makro peribadi dan gunakan semula dengan mudah tanpa perlu mengulangi langkah ini lagi dan lagi.Anda juga boleh membuat alat tambah dan berkongsi fungsi ini dengan orang lain.

Sekarang izinkan saya memberi anda kod VBA yang akan mencipta fungsi untuk mendapatkan senarai semua nama fail daripada folder dalam Excel.

Fungsi GetFileNames(ByVal FolderPath Sebagai Rentetan) Sebagai Varian Malapkan Hasil Sebagai Varian Malap i Sebagai Integer Malapkan Fail Saya Sebagai Objek Malapkan MyFSO Sebagai Objek Malapkan MyFolder Sebagai Objek Malapkan MyFiles Sebagai Objek Set MyFSO = CreateObject("Scripting.FileFSOSystemObject") Tetapkan MyFolder GetFolder(FolderPath) Tetapkan MyFiles = MyFolder.Files ReDim Result(1 To MyFiles.Count) i = 1 Untuk Setiap MyFile Dalam MyFiles Result(i) = MyFile.Name i = i + 1 MyFile Seterusnya GetFileNames = Result End Function

Kod di atas akan mencipta fungsi GetFileNames yang boleh digunakan dalam lembaran kerja (sama seperti fungsi biasa).

Di mana untuk meletakkan kod ini?

Ikuti langkah di bawah untuk menyalin kod ini dalam editor VB.

Bagaimana untuk menggunakan fungsi ini?

Berikut ialah langkah-langkah untuk menggunakan ciri ini dalam lembaran kerja:

• Dalam mana-mana sel, masukkan alamat folder folder yang anda ingin senaraikan nama fail.
• Dalam sel di mana anda mahu senarai, masukkan formula berikut (saya masukkannya dalam sel A3):

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

• Salin dan tampal formula ke dalam sel di bawah untuk mendapatkan senarai semua fail.

Ambil perhatian bahawa saya memasukkan lokasi folder dalam satu sel dan kemudian masukGetFileNamesSel ini digunakan dalam formula.Anda juga boleh hardcode alamat folder dalam formula seperti ini:

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

Dalam formula di atas, kami menggunakan ROW()-2 dan kami mulakan dengan baris ketiga.Ini memastikan bahawa apabila saya menyalin formula dalam sel di bawah ia akan meningkat sebanyak 1.Jika anda memasukkan formula dalam baris pertama lajur, anda boleh menggunakan ROW().

Bagaimanakah formula ini berfungsi?

Formula GetFileNames mengembalikan tatasusunan yang mengandungi nama semua fail dalam folder.

Fungsi INDEX digunakan untuk menyenaraikan satu nama fail setiap sel, bermula dengan yang pertama.

Fungsi IFERROR digunakan untuk mengembalikan kosong dan bukannya #REF!Ralat dipaparkan apabila formula disalin dalam sel tetapi tiada lagi nama fail untuk disenaraikan.

Dapatkan senarai semua nama fail dengan sambungan khusus menggunakan VBA

Formula di atas berguna apabila anda ingin mendapatkan senarai semua nama fail daripada folder dalam Excel.

Tetapi bagaimana jika anda hanya ingin mendapatkan nama fail video, atau hanya fail Excel, atau hanya fail yang mengandungi kata kunci tertentu.

Dalam kes ini, anda boleh menggunakan fungsi yang sedikit berbeza.

Kod di bawah akan membolehkan anda mendapatkan semua nama fail yang mempunyai kata kunci tertentu (atau sambungan tertentu) di dalamnya.

Fungsi GetFileNamesbyExt(ByVal FolderPath Sebagai Rentetan, FileExt Sebagai Rentetan) Sebagai Hasil Varian Malap Sebagai Varian Malap i Sebagai Integer Malapkan Fail Saya Sebagai Objek Malapkan MyFSO Sebagai Objek Malapkan MyFolder Sebagai Objek Malapkan MyFiles Sebagai Objek Set MyFSO = CreateObject("Scripting.FileStysys") MyFolder = MyFSO.GetFolder(FolderPath) Tetapkan MyFiles = MyFolder.Files ReDim Result(1 To MyFiles.Count) i = 1 Untuk Setiap MyFile Dalam MyFiles Jika InStr(1, MyFile.Name, FileExt) <> 0 Kemudian Result(i) = MyFile.Name i = i + 1 End If Next MyFile ReDim Preserve Result(1 To i - 1) GetFileNamesbyExt = Result End Function

Kod di atas akan mencipta fungsi yang boleh digunakan dalam lembaran kerja" GetFileNamesbyExt " (sama seperti fungsi biasa).

Fungsi ini mengambil dua parameter - lokasi folder dan kata kunci sambungan.Ia mengembalikan pelbagai nama fail yang sepadan dengan sambungan yang diberikan.Jika tiada sambungan atau kata kunci dinyatakan, ia akan mengembalikan semua nama fail dalam folder yang ditentukan.

Sintaks: =GetFileNamesbyExt("Lokasi Folder","Sambungan")

Di mana untuk meletakkan kod ini?

Ikuti langkah di bawah untuk menyalin kod ini dalam editor VB.

• Pergi ke tab pembangun.
• Klik butang Visual Basic.Ini akan membuka editor VB.
• Dalam editor VB, klik kanan pada mana-mana objek dalam buku kerja yang anda sedang kerjakan, pergi ke Sisipkan dan klik Modul.Jika anda tidak melihat Project Explorer, gunakan pintasan papan kekunci Control + R (tahan kawalan dan tekan "R").
• Klik dua kali objek modul dan salin dan tampal kod di atas ke dalam tetingkap kod modul.

Bagaimana untuk menggunakan fungsi ini?

Berikut ialah langkah-langkah untuk menggunakan ciri ini dalam lembaran kerja:

• Dalam mana-mana sel, masukkan alamat folder folder yang anda ingin senaraikan nama fail.Saya memasukkan ini dalam sel A1.
• Dalam sel, masukkan sambungan (atau kata kunci) yang anda mahu semua nama fail ada.Saya memasukkan ini dalam sel B1.
• Dalam sel di mana anda mahu senarai, masukkan formula berikut (saya masukkannya dalam sel A3):

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

• Salin dan tampal formula ke dalam sel di bawah untuk mendapatkan senarai semua fail.

Bagaimana dengan awakSebarang helah Excel yang anda gunakan untuk memudahkan hidup anda.Saya ingin belajar daripada anda.Kongsikan di ruangan komen!