รับรายชื่อไฟล์จากโฟลเดอร์โดยใช้ Excel (มีและไม่มี VBA)

ในวันแรกของฉันที่บริษัทที่ปรึกษาเล็กๆ ฉันได้รับมอบหมายโครงการระยะสั้นสามวันงานเป็นเรื่องง่าย

มีหลายโฟลเดอร์ในไดรฟ์เครือข่าย แต่ละโฟลเดอร์มีไฟล์หลายร้อยไฟล์

ฉันต้องปฏิบัติตามสามขั้นตอนเหล่านี้:

1. เลือกไฟล์และคัดลอกชื่อ
2. วางชื่อลงในเซลล์ใน Excel แล้วกด Enter
3. ย้ายไปยังไฟล์ถัดไปและทำซ้ำขั้นตอนที่ 1 และ 2

ฟังดูง่ายใช่มั้ย?

มันง่ายและใช้เวลานานมาก

สิ่งที่ฉันทำในสามวันสามารถทำได้ในไม่กี่นาทีหากฉันรู้เทคนิคที่ถูกต้อง

ในบทช่วยสอนนี้ ฉันจะแสดงวิธีต่างๆ ในการทำให้กระบวนการทั้งหมดรวดเร็วและง่ายสุดๆ (ทั้งที่มีและไม่มี VBA)

ข้อจำกัดของวิธีการที่แสดงในบทช่วยสอนนี้:โดยใช้เทคนิคที่แสดงด้านล่าง คุณจะสามารถรับชื่อไฟล์ในโฮมโฟลเดอร์ของคุณเท่านั้นคุณไม่ได้รับชื่อไฟล์ในโฟลเดอร์ย่อยในโฟลเดอร์หลักนี่คือวิธีรับชื่อไฟล์จากโฟลเดอร์และโฟลเดอร์ย่อยโดยใช้ Power Query

ใช้ฟังก์ชัน FILES เพื่อรับรายชื่อไฟล์จากโฟลเดอร์

ได้ยินเกี่ยวกับมันฟังก์ชัน FILESมันคืออะไร

ถ้าคุณไม่ไม่ต้องกังวล

มาจากสเปรดชีต Excel ตั้งแต่วัยเด็ก (สูตรเวอร์ชัน 4)

แม้ว่าสูตรนี้ใช้ไม่ได้กับเซลล์เวิร์กชีต แต่ยังคงใช้ได้กับช่วงที่มีชื่อเราจะใช้ข้อเท็จจริงนี้เพื่อรับรายชื่อไฟล์จากโฟลเดอร์ที่ระบุ

ตอนนี้ สมมติว่าคุณมีไฟล์บนเดสก์ท็อปชื่อ " โฟลเดอร์ทดสอบ ” และคุณต้องการรับรายชื่อไฟล์สำหรับไฟล์ทั้งหมดในโฟลเดอร์นั้น

นี่คือขั้นตอนที่จะให้ชื่อไฟล์ในโฟลเดอร์นี้แก่คุณ:

1. ในเซลล์ A1 ให้ป้อนที่อยู่โฟลเดอร์แบบเต็มตามด้วยเครื่องหมายดอกจัน (*)
   • ตัวอย่างเช่น ถ้าโฟลเดอร์ของคุณอยู่ในไดรฟ์ C ที่อยู่จะเป็นเช่น
     C:\Users\ชื่อของคุณ\เดสก์ท็อป\โฟลเดอร์ทดสอบ\*
   • หากคุณไม่แน่ใจว่าจะรับที่อยู่โฟลเดอร์ได้อย่างไร ให้ใช้วิธีต่อไปนี้:
     • • ในโฟลเดอร์ที่คุณต้องการรับชื่อไฟล์ ให้สร้างเวิร์กบุ๊ก Excel ใหม่ หรือเปิดเวิร์กบุ๊กที่มีอยู่ในโฟลเดอร์ และใช้สูตรต่อไปนี้ในเซลล์ใดก็ได้สูตรนี้จะให้ที่อยู่โฟลเดอร์ที่มีเครื่องหมายดอกจัน (*) ต่อท้ายขณะนี้ คุณสามารถคัดลอกและวาง (วางเป็นค่า) ที่อยู่นี้ลงในเซลล์ใดก็ได้ในเวิร์กบุ๊ก (A1 ในตัวอย่างนี้) ที่คุณต้องการให้ชื่อไฟล์

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

         [หากคุณสร้างเวิร์กบุ๊กใหม่ในโฟลเดอร์เพื่อใช้สูตรข้างต้นและรับที่อยู่ของโฟลเดอร์ คุณอาจต้องลบเวิร์กบุ๊กนั้นเพื่อไม่ให้อยู่ในรายการไฟล์ในโฟลเดอร์นั้น]

2. ไปที่แท็บ "สูตร" และคลิกตัวเลือก "กำหนดชื่อ"
3. ในกล่องโต้ตอบชื่อใหม่ ให้ใช้รายละเอียดต่อไปนี้
   • ชื่อ: FileNameList (เลือกชื่อใดก็ได้ตามต้องการ)
   • ขอบเขต: สมุดงาน
   • หมายถึง: =FILES(Sheet1!$A$1)
4. ในการรับรายการไฟล์ เราจะใช้ช่วงที่มีชื่อในฟังก์ชัน INDEXไปที่เซลล์ A3 (หรือเซลล์ใดก็ตามที่คุณต้องการให้รายชื่อเริ่มต้นด้วย) และป้อนสูตรต่อไปนี้:

   =IFERROR(INDEX(รายชื่อไฟล์,ROW()-2),"")

5. ลากลงมาแล้วจะมีรายชื่อไฟล์ทั้งหมดในโฟลเดอร์

ต้องการแยกไฟล์ที่มีนามสกุลเฉพาะหรือไม่? ?

หากคุณต้องการได้ไฟล์ทั้งหมดที่มีนามสกุลเฉพาะ ให้เปลี่ยนเครื่องหมายดอกจันด้วยนามสกุลไฟล์นั้นตัวอย่างเช่น หากคุณต้องการเฉพาะไฟล์ excel คุณสามารถใช้ *xls* แทน *

ดังนั้นที่อยู่โฟลเดอร์ที่คุณต้องใช้คือC:UsersSumitDesktopTest โฟลเดอร์*xls*

ในทำนองเดียวกัน สำหรับไฟล์เอกสาร word ให้ใช้ *doc*

มันทำงานอย่างไร?

สูตร FILES ดึงชื่อไฟล์ทั้งหมดที่มีนามสกุลที่ระบุในโฟลเดอร์ที่ระบุ

ในสูตร INDEX เราให้ชื่อไฟล์เป็นอาร์เรย์และใช้ฟังก์ชัน ROW เพื่อคืนค่าชื่อไฟล์ที่หนึ่ง ที่สอง ที่สาม ฯลฯ

สังเกตว่าฉันใช้แถว()-2เนื่องจากเราเริ่มในบรรทัดที่สามดังนั้นเมื่อหมายเลขแถวคือ 4 ROW()-2 จะเป็น 1 สำหรับอินสแตนซ์แรก 2 สำหรับอินสแตนซ์ที่สอง และอื่นๆ

รับรายชื่อไฟล์ทั้งหมดจากโฟลเดอร์โดยใช้VBA

ตอนนี้ต้องบอกว่าวิธีการข้างต้นค่อนข้างซับซ้อน (มีหลายขั้นตอน)

อย่างไรก็ตาม มันจะดีกว่าการทำด้วยตนเองมาก

อย่างไรก็ตาม หากคุณสะดวกที่จะใช้ VBA (หรือหากคุณปฏิบัติตามขั้นตอนที่ฉันจะแสดงด้านล่างนี้ได้ดี) คุณสามารถสร้างฟังก์ชันที่กำหนดเอง (UDF) ที่สามารถรับชื่อไฟล์ทั้งหมดได้อย่างง่ายดาย

ประโยชน์ของการใช้ฟังก์ชันที่กำหนดโดยผู้ใช้ (UDF)是คุณสามารถจะการทำงานบันทึกในสมุดงานแมโครส่วนบุคคลและนำมาใช้ใหม่ได้อย่างง่ายดายโดยไม่ต้องทำตามขั้นตอนเหล่านี้ซ้ำแล้วซ้ำอีกคุณยังสามารถสร้างส่วนเสริมและแชร์ฟังก์ชันนี้กับผู้อื่นได้

อันดับแรก ให้ฉันให้รหัส VBA แก่คุณเพื่อสร้างฟังก์ชันเพื่อรับรายชื่อไฟล์ทั้งหมดจากโฟลเดอร์ใน Excel

ฟังก์ชัน GetFileNames (ByVal FolderPath As String) เป็น Variant Dim Result เป็น Variant Dim i เป็น Integer Dim MyFile เป็น Object Dim MyFSO เป็น Object Dim MyFolder เป็น Object Dim MyFiles เป็น Object Set MyFSO = CreateObject ("Scripting.FileSystemObject") ตั้งค่า MyFolder = MyFSO รับโฟลเดอร์

โค้ดด้านบนจะสร้างฟังก์ชัน GetFileNames ที่สามารถใช้ได้ในเวิร์กชีต (เหมือนกับฟังก์ชันปกติ)

จะใส่รหัสนี้ที่ไหน?

ทำตามขั้นตอนด้านล่างเพื่อคัดลอกโค้ดนี้ในตัวแก้ไข VB

วิธีการใช้ฟังก์ชั่นนี้?

ต่อไปนี้เป็นขั้นตอนในการใช้คุณลักษณะนี้ในเวิร์กชีต:

• ในเซลล์ใดๆ ให้ป้อนที่อยู่โฟลเดอร์ของโฟลเดอร์ที่คุณต้องการแสดงรายการชื่อไฟล์
• ในเซลล์ที่คุณต้องการแสดงรายการ ให้ป้อนสูตรต่อไปนี้ (ฉันป้อนในเซลล์ A3):

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

• คัดลอกและวางสูตรลงในเซลล์ด้านล่างเพื่อดูรายการไฟล์ทั้งหมด

โปรดทราบว่าฉันป้อนตำแหน่งโฟลเดอร์ในเซลล์เดียวแล้วในรับชื่อไฟล์เซลล์นี้ใช้ในสูตรคุณยังสามารถฮาร์ดโค้ดที่อยู่โฟลเดอร์ในสูตรดังนี้:

=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) เป็น Variant Dim Result As Variant Dim i As Integer 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 = MyFolder.Files ReDim Result (1 ถึง MyFiles.Count) i = 1 สำหรับแต่ละ MyFile ใน MyFiles ถ้า InStr (1, MyFile.Name, FileExt) <> 0 แล้วผลลัพธ์ (i) = MyFile.Name i = i + 1 End ถ้าถัดไป MyFile ReDim รักษาผลลัพธ์ (1 ถึง i - 1) GetFileNamesbyExt = ฟังก์ชันสิ้นสุดผลลัพธ์

โค้ดข้างต้นจะสร้างฟังก์ชันที่สามารถใช้ได้ในแผ่นงาน" รับ FileNamesbyExt " (เหมือนกับฟังก์ชันปกติ)

ฟังก์ชันนี้ใช้พารามิเตอร์สองตัว - ตำแหน่งของโฟลเดอร์และคีย์เวิร์ดของส่วนขยายส่งคืนอาร์เรย์ของชื่อไฟล์ที่ตรงกับนามสกุลที่กำหนดหากไม่ได้ระบุนามสกุลหรือคีย์เวิร์ดไว้ ระบบจะส่งคืนชื่อไฟล์ทั้งหมดในโฟลเดอร์ที่ระบุ

ไวยากรณ์: =GetFileNamesbyExt("ตำแหน่งโฟลเดอร์","ส่วนขยาย")

จะใส่รหัสนี้ที่ไหน?

ทำตามขั้นตอนด้านล่างเพื่อคัดลอกโค้ดนี้ในตัวแก้ไข VB

• ไปที่แท็บนักพัฒนา
• คลิกปุ่ม Visual Basicซึ่งจะเป็นการเปิดตัวแก้ไข VB
• ในตัวแก้ไข VB ให้คลิกขวาที่วัตถุใดๆ ในเวิร์กบุ๊กที่คุณกำลังทำงานอยู่ ไปที่ แทรก แล้วคลิก โมดูลหากคุณไม่เห็น Project Explorer ให้ใช้แป้นพิมพ์ลัด Control + R (ควบคุมค้างไว้แล้วกด "R")
• ดับเบิลคลิกที่อ็อบเจ็กต์โมดูล แล้วคัดลอกและวางโค้ดด้านบนลงในหน้าต่างโค้ดโมดูล

วิธีการใช้ฟังก์ชั่นนี้?

ต่อไปนี้เป็นขั้นตอนในการใช้คุณลักษณะนี้ในเวิร์กชีต:

• ในเซลล์ใดๆ ให้ป้อนที่อยู่โฟลเดอร์ของโฟลเดอร์ที่คุณต้องการแสดงรายการชื่อไฟล์ฉันป้อนสิ่งนี้ในเซลล์ A1
• ในเซลล์ ให้ป้อนนามสกุล (หรือคีย์เวิร์ด) ที่คุณต้องการให้ชื่อไฟล์ทั้งหมดเป็นฉันป้อนสิ่งนี้ในเซลล์ B1
• ในเซลล์ที่คุณต้องการแสดงรายการ ให้ป้อนสูตรต่อไปนี้ (ฉันป้อนในเซลล์ A3):

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

• คัดลอกและวางสูตรลงในเซลล์ด้านล่างเพื่อดูรายการไฟล์ทั้งหมด

แล้วคุณล่ะ?เทคนิค Excel ใด ๆ ที่คุณใช้เพื่อทำให้ชีวิตของคุณง่ายขึ้นฉันชอบที่จะเรียนรู้จากคุณแบ่งปันในส่วนความคิดเห็น!