احصل على قائمة بأسماء الملفات من مجلد باستخدام Excel (مع وبدون VBA)

في أول يوم لي في شركة استشارية صغيرة ، تم تكليفي بمشروع قصير الأجل لمدة ثلاثة أيام.الوظيفة بسيطة.

يوجد العديد من المجلدات على محرك أقراص الشبكة ، يحتوي كل منها على مئات الملفات.

كان علي اتباع هذه الخطوات الثلاث:

1. حدد الملف وانسخ اسمه.
2. الصق الاسم في خلية في Excel واضغط على Enter.
3. انتقل إلى الملف التالي وكرر الخطوتين 1 و 2.

تبدو بسيطة ، أليس كذلك؟

إنها بسيطة وتستغرق وقتًا طويلاً جدًا.

ما فعلته في ثلاثة أيام يمكن إنجازه في دقائق إذا كنت أعرف التقنية الصحيحة.

في هذا البرنامج التعليمي ، سأوضح لك طرقًا مختلفة لجعل العملية برمتها فائقة السرعة وسهلة للغاية (باستخدام VBA وبدونه).

حدود الطرق الموضحة في هذا البرنامج التعليمي:باستخدام التقنية الموضحة أدناه ، ستتمكن فقط من الحصول على أسماء الملفات الموجودة في المجلد الرئيسي الخاص بك.لا تحصل على أسماء الملفات في المجلدات الفرعية في المجلد الرئيسي.فيما يلي طريقة للحصول على أسماء الملفات من المجلدات والمجلدات الفرعية باستخدام Power Query

استخدم وظيفة FILES للحصول على قائمة بأسماء الملفات من مجلد

سمعت عن ذلكوظيفة FILES؟

إذا لم تفعل ، فلا تقلق.

إنه من جدول بيانات Excel منذ الطفولة (صيغ الإصدار 4).

على الرغم من أن هذه الصيغة لا تعمل مع خلايا ورقة العمل ، إلا أنها لا تزال تعمل مع النطاقات المسماة.سنستخدم هذه الحقيقة للحصول على قائمة بأسماء الملفات من المجلد المحدد.

الآن ، افترض أن لديك ملفًا على سطح المكتب يسمى " مجلد الاختبار "وتريد الحصول على قائمة بأسماء الملفات لجميع الملفات في هذا المجلد.

فيما يلي الخطوات التي ستمنحك أسماء الملفات في هذا المجلد:

1. في الخلية A1 ، أدخل عنوان المجلد الكامل متبوعًا بعلامة النجمة (*)
   • على سبيل المثال ، إذا كان المجلد الخاص بك موجودًا في محرك الأقراص C ، فسيكون العنوان شيئًا مثل
     C: \ Users \ YOUR NAME \ Desktop \ Test Folder \ *
   • إذا لم تكن متأكدًا من كيفية الحصول على عنوان المجلد ، فاستخدم الطريقة التالية:
     • • في المجلد الذي تريد الحصول على اسم الملف منه ، أنشئ مصنف Excel جديدًا أو افتح مصنفًا موجودًا في المجلد واستخدم الصيغة التالية في أي خلية.ستمنحك هذه الصيغة عنوان المجلد بعلامة النجمة (*) في النهاية.يمكنك الآن نسخ (لصق كقيمة) هذا العنوان في أي خلية في المصنف (A1 في هذا المثال) حيث تريد اسم الملف.

         = REPLACE (CELL ("filename") ، FIND ("[" ، CELL ("filename")) ، LEN (CELL ("filename")) ، "*")

         [إذا قمت بإنشاء مصنف جديد في مجلد لاستخدام الصيغة أعلاه والحصول على عنوان المجلد ، فقد تحتاج إلى حذفه حتى لا يكون في قائمة الملفات في هذا المجلد]

2. انتقل إلى علامة التبويب "الصيغ" وانقر على خيار "تحديد الأسماء".
3. في مربع الحوار New Name ، استخدم التفاصيل التالية
   • الاسم: FileNameList (لا تتردد في اختيار أي اسم تريد)
   • النطاق: مصنف
   • يشير إلى: = FILES (Sheet1! $ A $ 1)
4. الآن للحصول على قائمة بالملفات ، سنستخدم نطاقًا مسمىًا في دالة INDEX.انتقل إلى الخلية A3 (أو أي خلية تريد أن تبدأ قائمة الأسماء بها) وأدخل الصيغة التالية:

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

5. اسحبه لأسفل وسيمنحك قائمة بجميع أسماء الملفات في المجلد

هل تريد استخراج الملفات بامتداد محدد؟ ؟

إذا كنت ترغب في الحصول على جميع الملفات بامتداد معين ، فما عليك سوى تغيير العلامة النجمية بامتداد الملف هذا.على سبيل المثال ، إذا كنت تريد ملف Excel فقط ، فيمكنك استخدام * xls * بدلاً من *

لذا فإن عنوان المجلد الذي تحتاج إلى استخدامه هوج: UsersSumitDesktopTest Folder * xls *

وبالمثل ، بالنسبة لملفات مستندات Word ، استخدم * doc *

كيف يعمل هذا؟

تسترد صيغة FILES أسماء جميع الملفات ذات الامتداد المحدد في المجلد المحدد.

في صيغة INDEX ، نعطي أسماء الملفات كمصفوفة ونستخدم الدالة ROW لإرجاع أسماء الملفات الأولى والثانية والثالثة وما إلى ذلك.

لاحظ أنني اعتدتROW () - 2، منذ أن بدأنا في السطر الثالث.لذلك عندما يكون رقم الصف 4 ، سيكون ROW () - 2 هو 1 للمثال الأول ، و 2 للثاني ، وهكذا.

احصل على قائمة بجميع أسماء الملفات من مجلد باستخدام VBA

الآن ، يجب أن أقول إن الطريقة المذكورة أعلاه معقدة بعض الشيء (هناك العديد من الخطوات).

ومع ذلك ، فهو أفضل بكثير من القيام بذلك يدويًا.

ومع ذلك ، إذا كنت مرتاحًا لاستخدام VBA (أو إذا كنت جيدًا في اتباع الخطوات الدقيقة التي سأدرجها أدناه) ، فيمكنك إنشاء وظيفة مخصصة (UDF) يمكنها بسهولة الحصول على أسماء جميع الملفات.

فوائد استخدام وظائف معرّفة من قبل المستخدم (UDFs)是أنتعلبة將وظيفةاحفظ في مصنفات الماكرو الشخصية وأعد استخدامها بسهولة دون الحاجة إلى تكرار هذه الخطوات مرارًا وتكرارًا.يمكنك أيضًا إنشاء وظائف إضافية ومشاركة هذه الوظيفة مع الآخرين.

الآن اسمحوا لي أولاً أن أقدم لك رمز VBA الذي سينشئ وظيفة للحصول على قائمة بجميع أسماء الملفات من مجلد في Excel.

الوظيفة GetFileNames (ByVal FolderPath as String) كنتيجة خافتة متغيرة مثل خافت متغير أنا كملفي خافت صحيح ككائن خافت MyFSO ككائن خافت MyFolder ككائن خافت MyFiles ككائن مجموعة MyFSO = CreateObject ("Scripting.FileSystemObject") عيّن MyFolder = MyFSO. GetFolder (FolderPath) Set MyFiles = MyFolder.Files ReDim Result (1 To MyFiles.Count) i = 1 لكل MyFile في MyFiles Result (i) = MyFile.Name i = i + 1 التالي MyFile GetFileNames = النتيجة النهائية وظيفة

سيقوم الكود أعلاه بإنشاء دالة GetFileNames يمكن استخدامها في ورقة العمل (تمامًا مثل الوظيفة العادية).

أين أضع هذا الرمز؟

اتبع الخطوات أدناه لنسخ هذا الرمز في محرر VB.

كيف تستخدم هذه الوظيفة؟

فيما يلي خطوات استخدام هذه الميزة في ورقة العمل:

• في أي خلية ، أدخل عنوان المجلد للمجلد الذي تريد إدراج أسماء الملفات منه.
• في الخلية حيث تريد القائمة ، أدخل الصيغة التالية (لقد أدخلتها في الخلية A3):

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

• انسخ والصق الصيغة في الخلية أدناه للحصول على قائمة بجميع الملفات.

لاحظ أنني دخلت إلى موقع المجلد في خلية واحدة ثم فيGetFileNamesيتم استخدام هذه الخلية في صيغة.يمكنك أيضًا ترميز عنوان المجلد في الصيغة مثل هذا:

= IFERROR (INDEX (GetFileNames ("C: \ Users \ YOUR NAME \ Desktop \ Test Folder")، ROW () - 2)، "")

في الصيغة أعلاه ، استخدمنا ROW () - 2 ونبدأ بالصف الثالث.هذا يضمن أنه عندما أنسخ الصيغة في الخلية أدناه ، فإنها ستزداد بمقدار 1.إذا قمت بإدخال الصيغة في الصف الأول من العمود ، يمكنك ببساطة استخدام ROW ().

كيف تعمل هذه الصيغة؟

ترجع الصيغة GetFileNames صفيفًا يحتوي على أسماء كافة الملفات الموجودة في المجلد.

تُستخدم الدالة INDEX لسرد اسم ملف واحد لكل خلية ، بدءًا من الأول.

تُستخدم الدالة IFERROR لإرجاع فارغة بدلاً من #REF!يظهر خطأ عند نسخ صيغة في خلية ولكن لا يوجد المزيد من أسماء الملفات للقائمة.

احصل على قائمة بجميع أسماء الملفات بامتداد محدد باستخدام VBA

الصيغة أعلاه مفيدة عندما تريد الحصول على قائمة بجميع أسماء الملفات من مجلد في Excel.

ولكن ماذا لو كنت تريد فقط الحصول على اسم ملف فيديو ، أو مجرد ملف Excel ، أو مجرد ملف يحتوي على كلمة أساسية معينة.

في هذه الحالة ، يمكنك استخدام وظيفة مختلفة قليلاً.

سيسمح لك الكود أدناه بالحصول على جميع أسماء الملفات التي تحتوي على كلمة رئيسية معينة (أو امتداد معين) فيها.

الوظيفة GetFileNamesbyExt (ByVal FolderPath As String ، FileExt As String) كنتيجة خافتة متغير مثل Dim متغير مثل ملف MyFile خافت عدد صحيح ككائن خافت MyFSO ككائن خافت MyFolder ككائن خافت ملفات MyFiles ككائن مجموعة MyFSO = CreateObject ("Scripting.FileSystemObject") مجموعة MyFolder = MyFSO.GetFolder (FolderPath) تعيين MyFiles = MyFolder.Files ReDim النتيجة (1 إلى MyFiles.Count) i = 1 لكل ملف MyFiles في MyFiles إذا كان InStr (1 ، MyFile.Name ، FileExt) <> 0 ثم النتيجة (i) = MyFile.Name i = i + 1 End إذا كانت النتيجة التالية للمحافظة على MyFile ReDim (من 1 إلى i - 1) GetFileNamesbyExt = وظيفة إنهاء النتيجة

سيُنشئ الرمز أعلاه وظيفة يمكن استخدامها في ورقة العمل " GetFileNamesbyExt "(تمامًا مثل الوظائف العادية).

تأخذ هذه الوظيفة معلمتين - موقع المجلد وكلمة التمديد الأساسية.تقوم بإرجاع مجموعة من أسماء الملفات تطابق الامتداد المحدد.إذا لم يتم تحديد امتداد أو كلمة أساسية ، فستُرجع جميع أسماء الملفات في المجلد المحدد.

بناء الجملة: = GetFileNamesbyExt ("موقع المجلد" ، "الامتداد")

أين أضع هذا الرمز؟

اتبع الخطوات أدناه لنسخ هذا الرمز في محرر VB.

• انتقل إلى علامة التبويب المطور.
• انقر فوق الزر Visual Basic.سيؤدي هذا إلى فتح محرر VB.
• في محرر VB ، انقر بزر الماوس الأيمن فوق أي كائن في المصنف الذي تعمل عليه ، وانتقل إلى "إدراج" وانقر فوق "وحدة نمطية".إذا كنت لا ترى Project Explorer ، فاستخدم اختصار لوحة المفاتيح Control + R (مع الاستمرار في التحكم واضغط على "R").
• انقر نقرًا مزدوجًا فوق كائن الوحدة النمطية وانسخ الكود أعلاه والصقه في نافذة رمز الوحدة النمطية.

كيف تستخدم هذه الوظيفة؟

فيما يلي خطوات استخدام هذه الميزة في ورقة العمل:

• في أي خلية ، أدخل عنوان المجلد للمجلد الذي تريد إدراج أسماء الملفات منه.لقد أدخلت هذا في الخلية A1.
• في الخلية ، أدخل الامتداد (أو الكلمة الأساسية) التي تريد أن تكون جميع أسماء الملفات عليها.لقد أدخلت هذا في الخلية B1.
• في الخلية حيث تريد القائمة ، أدخل الصيغة التالية (لقد أدخلتها في الخلية A3):

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

• انسخ والصق الصيغة في الخلية أدناه للحصول على قائمة بجميع الملفات.

ماذا عنكأي حيل Excel تستخدمها لتبسيط حياتك.أود أن أتعلم منك.شاركه في قسم التعليقات!