Excel을 사용하여 폴더에서 파일 이름 목록 가져오기(VBA 포함 및 제외)

작은 컨설팅 회사에 입사한 첫날, 나는 XNUMX일짜리 단기 프로젝트를 배정받았다.작업은 간단합니다.

네트워크 드라이브에는 수백 개의 파일이 있는 많은 폴더가 있습니다.

다음 세 단계를 따라야 했습니다.

1. 파일을 선택하고 이름을 복사합니다.
2. Excel의 셀에 이름을 붙여넣고 Enter 키를 누릅니다.
3. 다음 파일로 이동하여 1단계와 2단계를 반복합니다.

간단하게 들리죠?

간단하고 시간이 많이 걸립니다.

XNUMX일 동안 한 일을 올바른 기술만 알면 몇 분 안에 끝낼 수 있습니다.

이 튜토리얼에서는 전체 프로세스를 매우 빠르고 쉽게(VBA 사용 여부와 상관없이) 만드는 다양한 방법을 보여 드리겠습니다.

이 자습서에 표시된 방법의 제한 사항:아래 표시된 기술을 사용하면 홈 폴더에 있는 파일 이름만 가져올 수 있습니다.기본 폴더의 하위 폴더에 있는 파일 이름을 얻지 못합니다.파워 쿼리를 사용하여 폴더 및 하위 폴더에서 파일 이름을 가져오는 방법은 다음과 같습니다.

FILES 함수를 사용하여 폴더에서 파일 이름 목록 가져오기

그것에 대해 들었다파일 기능?

그렇지 않은 경우 걱정하지 마십시오.

어린 시절의 Excel 스프레드시트에서 가져왔습니다(버전 4 공식).

이 수식은 워크시트 셀에서 작동하지 않지만 명명된 범위에서는 계속 작동합니다.이 사실을 사용하여 지정된 폴더에서 파일 이름 목록을 가져옵니다.

이제 바탕 화면에 " 테스트 폴더 " 그리고 해당 폴더에 있는 모든 파일의 파일 이름 목록을 가져오려고 합니다.

이 폴더의 파일 이름을 지정하는 단계는 다음과 같습니다.

1. A1 셀에 전체 폴더 주소와 별표(*)를 차례로 입력합니다.
   • 예를 들어 폴더가 C 드라이브에 있는 경우 주소는 다음과 같습니다.
     C:\Users\내 이름\데스크탑\테스트 폴더\*
   • 폴더 주소를 얻는 방법을 잘 모르는 경우 다음 방법을 사용하십시오.
     • • 파일 이름을 가져오려는 폴더에서 새 Excel 통합 문서를 만들거나 폴더에서 기존 통합 문서를 열고 아무 셀에서나 다음 수식을 사용합니다.이 수식은 끝에 별표(*)가 있는 폴더 주소를 제공합니다.이제 이 주소를 파일 이름을 원하는 통합 문서의 모든 셀(이 예에서는 A1)에 복사하여 붙여넣을 수 있습니다(값으로 붙여넣기).

         =REPLACE(CELL("파일 이름"),FIND("[",CELL("파일 이름")),LEN(CELL("파일 이름")),"*")

         [위의 수식을 사용하여 폴더 주소를 얻기 위해 폴더에 새 통합 문서를 만든 경우 해당 폴더의 파일 목록에 없도록 삭제해야 할 수 있습니다.]

2. "수식" 탭으로 이동하여 "이름 정의" 옵션을 클릭합니다.
3. 새 이름 대화 상자에서 다음 세부 정보를 사용합니다.
   • 이름: FileNameList(원하는 이름을 자유롭게 선택)
   • 범위: 통합 문서
   • 참조: =FILES(시트1!$A$1)
4. 이제 파일 목록을 얻기 위해 INDEX 함수에서 명명된 범위를 사용할 것입니다.A3 셀(또는 이름 목록을 시작하려는 셀)으로 이동하여 다음 수식을 입력합니다.

   =IFERROR(INDEX(파일이름목록,ROW()-2),"")

5. 아래로 드래그하면 폴더의 모든 파일 이름 목록이 표시됩니다.

특정 확장자를 가진 파일을 추출하고 싶으십니까? ?

특정 확장명을 가진 모든 파일을 가져오려면 해당 파일 확장명으로 별표를 변경하면 됩니다.예를 들어, 엑셀 파일만 원하는 경우 * 대신 *xls*를 사용할 수 있습니다.

따라서 사용해야 하는 폴더 주소는C:UsersSumitDesktopTest 폴더*xls*

마찬가지로 워드 문서 파일의 경우 *doc*를 사용합니다.

어떻게 작동합니까?

FILES 수식은 지정된 폴더에서 지정된 확장자를 가진 모든 파일의 이름을 검색합니다.

INDEX 공식에서 파일 이름을 배열로 제공하고 ROW 함수를 사용하여 첫 번째, 두 번째, 세 번째 등의 파일 이름을 반환합니다.

내가 사용한 참고행()-2, 세 번째 줄부터 시작합니다.따라서 행 번호가 4일 때 ROW()-2는 첫 번째 인스턴스의 경우 1이 되고 두 번째 인스턴스의 경우 2가 되는 식입니다.

VBA를 사용하여 폴더에서 모든 파일 이름 목록 가져오기

이제 위의 방법이 조금 복잡하다고 말씀드리고 싶습니다(많은 단계가 있습니다).

그러나 이 작업을 수동으로 수행하는 것보다 훨씬 낫습니다.

그러나 VBA 사용에 익숙하다면(또는 아래에 나열할 정확한 단계를 잘 수행할 수 있는 경우) 모든 파일의 이름을 쉽게 가져올 수 있는 사용자 지정 함수(UDF)를 만들 수 있습니다.

사용자 정의 함수(UDF) 사용의 이점是너할 수있다將기능개인용 매크로 통합 문서에 저장하고 이 단계를 반복하지 않고도 쉽게 재사용할 수 있습니다.추가 기능을 만들고 이 기능을 다른 사람과 공유할 수도 있습니다.

이제 Excel의 폴더에서 모든 파일 이름 목록을 가져오는 함수를 만드는 VBA 코드를 먼저 제공하겠습니다.

함수 GetFileNames(ByVal FolderPath As String) As 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") MyFolder = MyFSO로 설정합니다. GetFolder(FolderPath) Set MyFiles = MyFolder.Files ReDim Result(1 To MyFiles.Count) i = 1 MyFiles의 각 MyFile Result(i) = MyFile.Name i = i + 1 Next MyFile GetFileNames = 결과 종료 기능

위의 코드는 워크시트에서 사용할 수 있는 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) As Variant Dim 결과 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) Set MyFiles = MyFolder.Files ReDim Result(1 To MyFiles.Count) i = 1 MyFiles의 각 MyFile If InStr(1, MyFile.Name, FileExt) <> 0 Then Result(i) = MyFile.Name i = i + 1 End If Next MyFile ReDim 보존 결과(1 To i - 1) GetFileNamesbyExt = 결과 종료 기능

위의 코드는 워크시트에서 사용할 수 있는 함수를 생성합니다." GetFileNamesbyExt "(일반 함수와 동일).

이 함수는 폴더 위치와 확장 키워드의 두 가지 매개변수를 사용합니다.주어진 확장자와 일치하는 파일 이름의 배열을 반환합니다.확장자나 키워드를 지정하지 않으면 지정된 폴더의 모든 파일 이름을 반환합니다.

구문: =GetFileNamesbyExt("폴더 위치","확장자")

이 코드를 어디에 넣을까요?

VB 편집기에서 이 코드를 복사하려면 아래 단계를 따르십시오.

• 개발자 탭으로 이동합니다.
• Visual Basic 단추를 클릭하십시오.그러면 VB 편집기가 열립니다.
• VB 편집기에서 작업 중인 통합 문서의 개체를 마우스 오른쪽 버튼으로 클릭하고 삽입으로 이동한 다음 모듈을 클릭합니다.프로젝트 탐색기가 표시되지 않으면 키보드 단축키 Control + R을 사용합니다(컨트롤을 누른 상태에서 "R" 누름).
• 모듈 개체를 두 번 클릭하고 위의 코드를 복사하여 모듈 코드 창에 붙여넣습니다.

이 기능을 사용하는 방법?

워크시트에서 이 기능을 사용하는 단계는 다음과 같습니다.

• 아무 셀에나 파일 이름을 나열할 폴더의 폴더 주소를 입력합니다.A1 셀에 입력했습니다.
• 셀에 모든 파일 이름을 지정할 확장자(또는 키워드)를 입력합니다.나는 이것을 B1 셀에 입력했다.
• 목록을 원하는 셀에 다음 수식을 입력합니다(저는 A3 셀에 입력했습니다).

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

• 수식을 복사하여 아래 셀에 붙여넣으면 모든 파일 목록이 표시됩니다.

당신은 어때삶을 단순화하는 데 사용하는 모든 Excel 트릭.나는 당신에게서 배우고 싶습니다.의견 섹션에서 공유하십시오!