Excel kullanarak bir klasörden dosya adlarının listesini alın (VBA ile ve VBA olmadan)

Küçük bir danışmanlık firmasındaki ilk günümde bana üç günlük kısa vadeli bir proje verildi.İş basit.

Bir ağ sürücüsünde her biri yüzlerce dosya içeren birçok klasör vardır.

Bu üç adımı izlemem gerekiyordu:

1. Dosyayı seçin ve adını kopyalayın.
2. Adı Excel'deki bir hücreye yapıştırın ve Enter tuşuna basın.
3. Sonraki dosyaya geçin ve 1. ve 2. adımları tekrarlayın.

Kulağa basit geliyor, değil mi?

Bu basit ve çok zaman alıcıdır.

Doğru tekniği bilirsem üç günde yaptığım şey dakikalar içinde yapılabilir.

Bu eğitimde, tüm süreci süper hızlı ve süper kolay hale getirmenin farklı yollarını göstereceğim (VBA ile ve VBA olmadan).

Bu öğreticide gösterilen yöntemlerin sınırlamaları:Aşağıda gösterilen tekniği kullanarak, yalnızca ana klasörünüzdeki dosyaların adlarını alabileceksiniz.Ana klasördeki alt klasörlerdeki dosyaların adlarını alamazsınız.Power Query kullanarak klasörlerden ve alt klasörlerden dosya adları almanın bir yolu

Bir klasörden dosya adlarının listesini almak için DOSYALAR işlevini kullanın

hakkında duymuştumDOSYALAR işlevi?

Eğer yapmazsan, endişelenme.

Çocukluğundan bir Excel elektronik tablosundan (sürüm 4 formülleri).

Bu formül, çalışma sayfası hücreleriyle çalışmasa da, yine de adlandırılmış aralıklarla çalışır.Belirtilen klasörden dosya adlarının bir listesini almak için bu gerçeği kullanacağız.

Şimdi, masaüstünüzde "adlı bir dosyanız olduğunu varsayalım. Test Klasörü ” ve o klasördeki tüm dosyalar için dosya adlarının bir listesini almak istiyorsunuz.

Bu klasördeki dosya adlarını size verecek adımlar şunlardır:

1. A1 hücresine, tam klasör adresini ve ardından bir yıldız işareti (*) girin
   • Örneğin, klasörünüz C sürücüsündeyse, adres şöyle bir şey olurdu:
     C:\Kullanıcılar\ADINIZ\Masaüstü\Test Klasörü\*
   • Klasör adresini nasıl alacağınızdan emin değilseniz, aşağıdaki yöntemi kullanın:
     • • Dosya adını almak istediğiniz klasörde yeni bir Excel çalışma kitabı oluşturun veya klasörde var olan bir çalışma kitabını açın ve herhangi bir hücrede aşağıdaki formülü kullanın.Bu formül size sonunda bir yıldız işareti (*) ile klasör adresini verecektir.Artık bu adresi, çalışma kitabında (bu örnekte A1) dosya adını istediğiniz herhangi bir hücreye kopyalayıp yapıştırabilirsiniz (değer olarak yapıştırabilirsiniz).

         =DEĞİŞTİR(HÜCRE("dosyaadı")),BUL("[",HÜCRE("dosyaadı")),UZUNLUK(HÜCRE("dosyaadı")),"*")

         [Yukarıdaki formülü kullanmak ve klasör adresini almak için bir klasörde yeni bir çalışma kitabı oluşturduysanız, o klasördeki dosyalar listesinde olmaması için silmeniz gerekebilir]

2. "Formüller" sekmesine gidin ve "Adları Tanımla" seçeneğini tıklayın.
3. Yeni Ad iletişim kutusunda aşağıdaki ayrıntıları kullanın
   • Ad: DosyaAdıListesi (istediğiniz herhangi bir adı seçmekten çekinmeyin)
   • Kapsam: Çalışma kitabı
   • Şu anlama gelir: =DOSYALAR(Sayfa1!$A$1)
4. Şimdi dosyaların bir listesini almak için INDEX işlevinde adlandırılmış bir aralık kullanacağız.A3 hücresine (veya ad listesinin başlamasını istediğiniz hücreye) gidin ve aşağıdaki formülü girin:

   =EĞERHATA(INDEX(DosyaAdıListesi,SATIR()-2),"")

5. Aşağı sürükleyin ve size klasördeki tüm dosya adlarının bir listesini verecektir.

Belirli bir uzantıya sahip dosyaları ayıklamak ister misiniz? ?

Belirli bir uzantıya sahip tüm dosyaları almak istiyorsanız, yıldız işaretini o dosya uzantısıyla değiştirmeniz yeterlidir.Örneğin sadece excel dosyasını istiyorsanız * yerine *xls* kullanabilirsiniz.

Yani kullanmanız gereken klasör adresiC:UsersSumitDesktopTest Klasörü*xls*

Aynı şekilde, word belgesi dosyaları için *doc* kullanın.

Bu nasıl çalışıyor?

FILES formülü, belirtilen klasördeki belirtilen uzantıya sahip tüm dosyaların adlarını alır.

INDEX formülünde dosya isimlerini dizi olarak veriyoruz ve SIRA fonksiyonunu kullanarak birinci, ikinci, üçüncü vb. dosya isimlerini döndürüyoruz.

Not kullandığımSATIR()-2, üçüncü satırdan başladığımızdan beri.Yani satır numarası 4 olduğunda, ROW()-2 ilk örnek için 1, ikinci örnek için 2 vb. olacaktır.

VBA kullanarak bir klasördeki tüm dosya adlarının listesini alın

Şimdi, yukarıdaki yöntemin biraz karmaşık olduğunu söylemeliyim (birçok adım var).

Ancak, bunu manuel olarak yapmaktan çok daha iyidir.

Ancak, VBA'yı kullanmakta rahatsanız (veya aşağıda listeleyeceğim adımları tam olarak takip etmekte iyiyseniz), tüm dosyaların adlarını kolayca alabilen özel bir işlev (UDF) oluşturabilirsiniz.

Kullanıcı Tanımlı İşlevleri (UDF'ler) kullanmanın faydaları是senYapabilmek將işlevKişisel bir makro çalışma kitabına kaydedin ve bu adımları tekrar tekrar tekrarlamak zorunda kalmadan kolayca yeniden kullanın.Ayrıca eklentiler oluşturabilir ve bu işlevi başkalarıyla paylaşabilirsiniz.

Şimdi önce size Excel'deki bir klasörden tüm dosya adlarının bir listesini almak için bir işlev oluşturacak VBA kodunu vereyim.

İşlev GetFileNames(ByVal FolderPath As String) Varyant Olarak Dim Sonucu Varyant Dim i As Tamsayı Dim MyFile As Object Dim MyFSO As Object Dim MyFolder Dim MyFiles As Object Set MyFSO = CreateObject("Scripting.FileSystemObject") MyFolder = MyFSO olarak ayarlayın. GetFolder(FolderPath) Set MyFiles = MyFolder.Files ReDim Result(1 To MyFiles.Count) i = 1 MyFiles'daki Her MyFile için Result(i) = MyFile.Name i = i + 1 Sonraki MyFile GetFileNames = Sonuç Bitiş Fonksiyonu

Yukarıdaki kod, çalışma sayfasında kullanılabilecek bir GetFileNames işlevi yaratacaktır (tıpkı normal bir işlev gibi).

Bu kodu nereye koyacağız?

Bu kodu VB düzenleyicide kopyalamak için aşağıdaki adımları izleyin.

Bu işlev nasıl kullanılır?

Bu özelliği bir çalışma sayfasında kullanma adımları şunlardır:

• Herhangi bir hücreye, dosya adlarını listelemek istediğiniz klasörün klasör adresini girin.
• Listeyi istediğiniz hücreye aşağıdaki formülü girin (A3 hücresine girdim):

  =EĞERHATA(INDEX(GetFileNames($A$1),SATIR()-2),"")

• Tüm dosyaların bir listesini almak için formülü kopyalayıp aşağıdaki hücreye yapıştırın.

Klasör konumunu bir hücreye girdiğimi ve ardındanDosya Adlarını GetirBu hücre bir formülde kullanılır.Klasör adresini aşağıdaki gibi formüle de yazabilirsiniz:

=EĞERHATA(INDEX(GetFileNames("C:\Kullanıcılar\Adınız\Masaüstü\Test Klasörü")),SATIR()-2),"")

Yukarıdaki formülde ROW()-2 kullandık ve üçüncü satırdan başlıyoruz.Bu, aşağıdaki hücreye formülü kopyaladığımda 1 artacağını garanti ediyor.Formülü sütunun ilk satırına girerseniz, ROW()'u kullanabilirsiniz.

Bu formül nasıl çalışır?

GetFileNames formülü, klasördeki tüm dosyaların adlarını içeren bir dizi döndürür.

INDEX işlevi, ilkinden başlayarak hücre başına bir dosya adı listelemek için kullanılır.

EĞERHATA işlevi, #REF! yerine boş döndürmek için kullanılır!Bir hücreye formül kopyalandığında, ancak listelenecek başka dosya adı olmadığında hata görüntüleniyor.

VBA kullanarak belirli bir uzantıya sahip tüm dosya adlarının bir listesini alın

Yukarıdaki formül, Excel'deki bir klasörden tüm dosya adlarının bir listesini almak istediğinizde kullanışlıdır.

Ama ya sadece bir video dosyasının adını, ya da sadece bir Excel dosyasını ya da sadece belirli bir anahtar kelimeyi içeren bir dosyayı almak istiyorsanız.

Bu durumda, biraz farklı bir işlev kullanabilirsiniz.

Aşağıdaki kod, içinde belirli bir anahtar kelime (veya belirli bir uzantı) bulunan tüm dosya adlarını almanıza olanak tanır.

İşlev GetFileNamesbyExt(ByVal FolderPath As Dize, FileExt As Dize) Varyant Olarak Dim Sonucu Varyant Olarak Dim i Tamsayı Olarak Dim MyFile As Object Dim MyFSO As Obje Olarak 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 MyFiles'daki Her MyFile İçin If InStr(1, MyFile.Name, FileExt) <> 0 Sonra Sonuç(i) = MyFile.Name i = i + 1 End If Next MyFile ReDim Sonucu Koru (1'den i - 1) GetFileNamesbyExt = Sonuç Bitiş Fonksiyonu

Yukarıdaki kod, çalışma sayfasında kullanılabilecek bir işlev yaratacaktır" GetFileNamesbyExt " (normal işlevler gibi).

Bu işlev iki parametre alır - klasör konumu ve uzantı anahtar sözcüğü.Verilen uzantıyla eşleşen bir dizi dosya adı döndürür.Uzantı veya anahtar sözcük belirtilmezse, belirtilen klasördeki tüm dosya adlarını döndürür.

Sözdizimi: =GetFileNamesbyExt("Klasör Konumu","Uzantı")

Bu kodu nereye koyacağız?

Bu kodu VB düzenleyicide kopyalamak için aşağıdaki adımları izleyin.

• Geliştirici sekmesine gidin.
• Visual Basic düğmesine tıklayın.Bu, VB düzenleyicisini açacaktır.
• VB editöründe, üzerinde çalıştığınız çalışma kitabında herhangi bir nesneye sağ tıklayın, Ekle'ye gidin ve Modül'e tıklayın.Proje Gezgini'ni görmüyorsanız, Control + R klavye kısayolunu kullanın (kontrolü basılı tutun ve "R" tuşuna basın).
• Modül nesnesine çift tıklayın ve yukarıdaki kodu kopyalayıp modül kodu penceresine yapıştırın.

Bu işlev nasıl kullanılır?

Bu özelliği bir çalışma sayfasında kullanma adımları şunlardır:

• Herhangi bir hücreye, dosya adlarını listelemek istediğiniz klasörün klasör adresini girin.Bunu A1 hücresine girdim.
• Hücreye, tüm dosya adlarının olmasını istediğiniz uzantıyı (veya anahtar kelimeyi) girin.Bunu B1 hücresine girdim.
• Listeyi istediğiniz hücreye aşağıdaki formülü girin (A3 hücresine girdim):

  =EĞERHATA(INDEX(GetFileNamesbyExt($A$1,$B$1),SATIR()-2),"")

• Tüm dosyaların bir listesini almak için formülü kopyalayıp aşağıdaki hücreye yapıştırın.

Ya senHayatınızı kolaylaştırmak için kullandığınız tüm Excel hileleri.senden öğrenmeyi çok isterimYorumlar bölümünde paylaşın!