Excel'deki bir Klasörden Dosya Adlarının Listesini Alın (VBA ile ve VBA olmadan)

Küçük bir danışmanlık firmasındaki işimde ilk günümde, üç günlük kısa bir projede görevlendirildim.

İş basitti.

Ağ sürücüsünde birçok klasör vardı ve her klasörün içinde yüzlerce dosya vardı.

Bu üç adımı izlemem gerekiyordu:

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

Kulağa basit geliyor değil mi?

Öyleydi - Basit ve büyük bir zaman kaybı.

Doğru teknikleri bilseydim, üç günümü alan şey birkaç dakika içinde yapılabilirdi.

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

Bu öğreticide gösterilen yöntemlerin sınırlamaları: Aşağıda gösterilen teknikler ile sadece ana klasör içindeki dosyaların isimlerini alabileceksiniz. Ana klasör içindeki alt klasörlerdeki dosyaların adlarını alamazsınız. Power Query kullanarak klasörlerden ve alt klasörlerden dosya adlarını almanın bir yolu

Bir Klasörden Dosya Adlarının Listesini Almak için DOSYALAR İşlevini Kullanma

Duydum DOSYALAR işlevi önce?

Yapmadıysan endişelenme.

Excel elektronik tablolarının çocukluk günlerindendir (sürüm 4 formülü).

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

Şimdi, adında bir klasörünüz olduğunu varsayalım - 'Test Klasörü' ve bu klasördeki tüm dosyalar için dosya adlarının bir listesini almak istiyorsunuz.

İşte size bu klasördeki dosya adlarını verecek adımlar:

1. A1 hücresine, klasör tam 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 görünür:
     C:\Kullanıcılar\Sumit\Desktop\Test Klasörü\*
   • Klasör adresini nasıl alacağınızdan emin değilseniz, aşağıdaki yöntemi kullanın:
     • • Dosya adlarını almak istediğiniz klasörde ya yeni bir Excel Çalışma Kitabı oluşturun ya da 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 klasör adresini verir ve sonuna bir yıldız işareti (*) ekler. Artık bu adresi, dosya adlarını istediğiniz çalışma kitabındaki herhangi bir hücreye (bu örnekte A1) 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 klasörde yeni bir çalışma kitabı oluşturduysanız, o klasördeki dosyalar listesinde yer almaması için silmek isteyebilirsiniz]
2. "Formüller" sekmesine gidin ve "Ad Tanımla" seçeneğine tıklayın.
3. Yeni Ad iletişim kutusunda aşağıdaki ayrıntıları kullanın
   • İsim: FileNameList (istediğiniz ismi seçmekten çekinmeyin)
   • Kapsam: Çalışma kitabı
   • Şu anlama gelir: =DOSYALAR(Sayfa1!$A$1)
4. Şimdi dosyaların listesini almak için, bir INDEX işlevi içinde adlandırılmış aralığı kullanacağız. A3 hücresine (veya ad listesinin başlamasını istediğiniz herhangi bir hücreye) gidin ve aşağıdaki formülü girin:

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

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

Belirli Bir Uzantıya Sahip Dosyaları Çıkarmak İster 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, yalnızca excel dosyaları istiyorsanız, * yerine *xls* kullanabilirsiniz.

Yani kullanmanız gereken klasör adresi C:\Kullanıcılar\Sumit\Desktop\Test Klasörü\*xls*

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

Bu nasıl çalışıyor?

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

INDEX formülünde dosya isimlerini dizi olarak verdik ve ROW fonksiyonunu kullanarak 1., 2., 3. dosya isimlerini vb. döndürüyoruz.

Not kullandığım SATIR()-2, üçüncü sıradan başladığımız gibi. Dolayısıyla, ROW()-2, ilk örnek için 1, satır numarası 4 olduğunda ikinci örnek için 2 ve bu böyle devam eder.

Videoyu İzle - Excel'deki Bir Klasörden Dosya Adlarının Listesini Alın

VBA'yı Kullanma Bir Klasörden Tüm Dosya Adlarının Listesini Alın

Şimdi, yukarıdaki yöntemin biraz karmaşık olduğunu söylemeliyim (birkaç adımla).

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), size tüm dosyaların adlarını kolayca alabilen özel bir işlev (UDF) oluşturabilirsiniz.

Bir kullanmanın yararı senser NSrafine Function (UDF), işlevi kişisel bir makro çalışma kitabına kaydedebilmeniz ve adımları tekrar tekrar tekrarlamadan kolayca yeniden kullanabilmenizdir. Ayrıca bir eklenti oluşturabilir ve bu işlevi başkalarıyla paylaşabilirsiniz.

Şimdi önce size Excel'deki bir klasördeki tüm dosya adlarının 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 sayfalarında kullanılabilecek bir GetFileNames işlevi yaratacaktır (tıpkı normal işlevler gibi).

Bu kodu nereye koyacağız?

Bu kodu VB Editöründe kopyalamak için aşağıdaki adımları izleyin.

• Geliştirici sekmesine gidin.
• Visual Basic düğmesine tıklayın. Bu, VB Editörünü açacaktır.
• VB Editöründe, üzerinde çalıştığınız çalışma kitabının herhangi bir nesnesine 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 tuşunu 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 İşlev Nasıl Kullanılır?

Bu işlevi bir çalışma sayfasında kullanma adımları aşağıdadır:

• Herhangi bir hücrede, 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 giriyorum):

  =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ücrelere yapıştırın.

Klasör konumunu bir hücreye girdiğimi ve ardından o hücreyi GetFileNames formül. Ayrıca, formüldeki klasör adresini aşağıda gösterildiği gibi sabit kodlayabilirsiniz:

=EĞERHATA(INDEX(GetFileNames("C:\Kullanıcılar\Sumit\Desktop\Test Klasörü"),SATIR()-2),"")

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

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

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

INDEX işlevi, ilkinden başlayarak hücre başına bir dosya adını 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 gösterilen ancak listelenecek başka dosya adı olmayan hata.

VBA'yı Kullanma Belirli Bir Uzantıya Sahip Tüm Dosya Adlarının Listesini Alın

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

Ancak, yalnızca video dosyalarının veya yalnızca Excel dosyalarının veya yalnızca belirli bir anahtar kelimeyi içeren dosya adlarının adlarını almak istiyorsanız ne olur?

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

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

İşlev GetFileNamesbyExt(ByVal FolderPath As String, FileExt As String) Varyant Olarak Dim Sonucu Varyant Olarak Dim i Tamsayı Olarak Dim MyFile As Object Dim MyFSO As Object Dim MyFolder As Object Dim MyFiles As Object Set MyFSO = CreateObject("Scripting.FileSystemObject") Set MyFolder = MyFSO.GetFolder(FolderPath) MyFiles Seti = MyFolder.Files ReDim Result(1 To MyFiles.Count) i = 1 MyFiles'daki Her MyFile için 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 bir işlev yaratacaktır 'GetFileNamesbyExt' çalışma sayfalarında kullanılabilir (tıpkı normal işlevler gibi).

Bu işlev iki bağımsız değişken 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 Editöründe kopyalamak için aşağıdaki adımları izleyin.

• Geliştirici sekmesine gidin.
• Visual Basic düğmesine tıklayın. Bu, VB Editörünü açacaktır.
• VB Editöründe, üzerinde çalıştığınız çalışma kitabının herhangi bir nesnesine 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 tuşunu 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 İşlev Nasıl Kullanılır?

Bu işlevi bir çalışma sayfasında kullanma adımları aşağıdadır:

• Herhangi bir hücrede, dosya adlarını listelemek istediğiniz klasörün klasör adresini girin. Bunu A1 hücresine girdim.
• Bir hücreye, tüm dosya adları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 giriyorum):

  =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ücrelere yapıştırın.

Peki ya sen? Hayatı kolaylaştırmak için kullandığınız tüm Excel hileleri. senden öğrenmeyi çok isterim Yorum bölümünde paylaşın!