폴더내 하부폴더 포함 모든 엑셀파일 문서 내용에서 검색어 한꺼번에 찾는 법!

DATA

폴더내 하부폴더 포함 모든 엑셀파일 문서 내용에서 검색어 한꺼번에 찾는 법!

노만e 2025. 9. 23. 22:33

오늘 일일 업무일지 수년치를 열어, 원하는 검색어를 찾다가, 내가 도대체 왜 이러고 있나 한숨이...났습니다.

저희는 4개팀, 3개조로 1년 365일 쉬지 않고 일합니다. 일일 업무일지는 4개팀이 주간, 야간 작성하니,

하루에만 8개의 일지가 생성 됩니다. 그걸 365일X2년이면.. 5840개!!! 허걱~~

📁 수십 개, 수백 개의 엑셀 파일을 하나씩 열어가며 데이터를 찾고 계신가요?
이제는 폴더 전체 엑셀 파일을 자동으로 검색해주는 VBA 매크로로 단번에 해결하세요!

✅ 이런 분들에게 강력 추천합니다

✔️ 수많은 견적서/매출자료/보고서에서 특정 값을 찾아야 하는 분
✔️ 부서별/연도별로 정리된 엑셀 파일을 검색해야 하는 실무자
✔️ 수기로 작업하는 비효율적인 검색 방식에서 벗어나고 싶은 분
✔️ 폴더 내 모든 엑셀 파일을 자동 검색하고, 결과를 정리하고 싶은 분

🔍 주요 기능 (엑셀 VBA 자동 검색기)

기능설명

📁 폴더 선택	원하는 폴더를 지정하면 하위 폴더까지 검색
🔑 키워드 다중 검색	최대 5개의 키워드를 동시에 검색 가능 (B3~B7 입력)
📊 검색 결과 정리	[검색결과] 시트에 자동 기록
🧩 맥락 확인	앞/뒤 셀 내용, 시트명, 셀주소까지 제공
🔐 현재 파일은 제외	실행 중인 매크로 파일은 자동으로 제외 처리

🧪 사용 방법

① VBA 코드 삽입

엑셀에서 Alt + F11 키를 눌러 VBA 편집기 실행
삽입 > 모듈 클릭
아래 전체 코드를 붙여넣기
저장 (.xlsm 형식으로 저장 권장)

✅ 전체 코드는 이 글 하단에 제공됩니다!

② 키워드 입력

현재 엑셀 파일의 아무 시트나 선택 후
B3:B7 셀에 찾고 싶은 키워드를 입력하세요
예) 고객명, 제품명, 날짜 등

③ 매크로 실행

Alt + F8 → SearchInExcelFiles 선택 → 실행
검색할 폴더를 선택하면 자동으로 모든 엑셀을 열어 검색 진행
결과는 [검색결과] 시트에 자동 정리됩니다!

📌 검색 결과 예시

파일명시트명찾은값이전셀이후셀셀주소

sales_2023	매출	홍길동	서울점	1,000,000	C12
invoice_005	거래내역	2023-08-21	제품명	결제완료	B7

💬 실무에서 이렇게 활용했어요

“한 달치 고객 요청사항이 어디 있었는지 몰라서 검색했는데 5초만에 찾았습니다.”
“수백 개 엑셀을 수작업으로 보던 시절은 끝났어요.”
“모든 키워드가 한눈에 정리돼서 보고서 만들기도 쉬워졌어요.”

📥 지금 바로 사용해보세요!

🎁 아래 코드 복사해서 엑셀 VBA에 붙여넣기만 하면 끝!
엑셀을 자주 다루는 분이라면 100% 실무에 도움됩니다.

🧾 전체 VBA 코드 (복사해서 붙여넣기)

Sub SearchInExcelFiles()
    Dim folderPath As String
    Dim currentWs As Worksheet
    Dim resultWs As Worksheet
    Dim searchValues As Variant
    Dim resultRow As Long
    Dim fileCount As Long
    Dim searchCount As Long

    On Error GoTo ErrorHandler

    ' 현재 시트 설정
    Set currentWs = ActiveSheet

    ' 폴더 선택 대화상자
    With Application.FileDialog(msoFileDialogFolderPicker)
        .Title = "검색할 폴더를 선택하세요"
        .AllowMultiSelect = False
        If .Show = -1 Then
            folderPath = .SelectedItems(1)
        Else
            MsgBox "폴더가 선택되지 않았습니다."
            Exit Sub
        End If
    End With

    ' A2:A6 값을 배열로 저장
    On Error Resume Next
    searchValues = currentWs.Range("B3:B7").Value
    On Error GoTo ErrorHandler

    ' 검색값이 있는지 확인
    searchCount = 0
    For i = 1 To UBound(searchValues, 1)
        If Trim(CStr(searchValues(i, 1))) <> "" Then
            searchCount = searchCount + 1
        End If
    Next i

    If searchCount = 0 Then
        MsgBox "A2:A6에 검색할 값이 없습니다."
        Exit Sub
    End If

    ' [검색결과] 시트 생성 또는 가져오기
    Set resultWs = CreateOrGetResultSheet()

    ' 결과 시트 헤더 설정
    With resultWs
        .Cells(1, 1).Value = "파일명"
        .Cells(1, 2).Value = "시트명"
        .Cells(1, 3).Value = "찾은값"
        .Cells(1, 4).Value = "이전셀"
        .Cells(1, 5).Value = "이후셀"
        .Cells(1, 6).Value = "셀위치"

        ' 헤더 서식 설정
        .Range("A1:F1").Font.Bold = True
        .Range("A1:F1").Interior.Color = RGB(200, 200, 200)

        ' 기존 데이터 삭제
        .Range("A2:F10000").Clear
    End With

    ' 결과 출력 시작 행
    resultRow = 2
    fileCount = 0

    ' 화면 업데이트 중지 (성능 향상)
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual

    ' 상태 표시
    Application.StatusBar = "검색 중..."

    ' 폴더 내 모든 엑셀 파일 검색
    Call SearchFolder(folderPath, searchValues, resultWs, resultRow, fileCount)

    ' 결과 시트 활성화 및 열 너비 자동 조정
    resultWs.Activate
    resultWs.Columns("A:F").AutoFit

    GoTo CleanUp

ErrorHandler:
    MsgBox "오류가 발생했습니다: " & Err.Description & " (오류 번호: " & Err.Number & ")"

CleanUp:
    ' 설정 복원
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic
    Application.StatusBar = False

    If Err.Number = 0 Then
        Dim foundCount As Long
        foundCount = resultRow - 2
        MsgBox "검색이 완료되었습니다!" & vbCrLf & _
               "처리된 파일 수: " & fileCount & vbCrLf & _
               "검색된 결과 수: " & foundCount & vbCrLf & _
               "[검색결과] 시트를 확인해주세요."
    End If
End Sub

Function CreateOrGetResultSheet() As Worksheet
    Dim ws As Worksheet
    Dim sheetExists As Boolean

    ' [검색결과] 시트가 이미 있는지 확인
    sheetExists = False
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name = "검색결과" Then
            Set CreateOrGetResultSheet = ws
            sheetExists = True
            Exit For
        End If
    Next ws

    ' 시트가 없으면 새로 생성
    If Not sheetExists Then
        Set CreateOrGetResultSheet = ThisWorkbook.Worksheets.Add
        CreateOrGetResultSheet.Name = "검색결과"
    End If
End Function

Sub SearchFolder(folderPath As String, searchValues As Variant, resultWs As Worksheet, ByRef resultRow As Long, ByRef fileCount As Long)
    Dim fso As Object
    Dim folder As Object
    Dim file As Object
    Dim subFolder As Object

    ' FileSystemObject 생성
    Set fso = CreateObject("Scripting.FileSystemObject")

    On Error Resume Next
    Set folder = fso.GetFolder(folderPath)
    On Error GoTo 0

    If folder Is Nothing Then
        Exit Sub
    End If

    ' 현재 폴더의 엑셀 파일 검색
    For Each file In folder.Files
        If IsExcelFile(file.Name) Then
            fileCount = fileCount + 1
            Application.StatusBar = "검색 중... 파일: " & file.Name
            Call SearchInFile(file.Path, searchValues, resultWs, resultRow)
        End If
    Next file

    ' 하위 폴더 재귀 검색
    For Each subFolder In folder.SubFolders
        On Error Resume Next
        Call SearchFolder(subFolder.Path, searchValues, resultWs, resultRow, fileCount)
        On Error GoTo 0
    Next subFolder
End Sub

Function IsExcelFile(fileName As String) As Boolean
    Dim ext As String
    ext = LCase(Right(fileName, 4))
    IsExcelFile = (ext = ".xls" Or Right(LCase(fileName), 5) = ".xlsx" Or Right(LCase(fileName), 5) = ".xlsm")
End Function

Sub SearchInFile(filePath As String, searchValues As Variant, resultWs As Worksheet, ByRef resultRow As Long)
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim searchRange As Range
    Dim foundCell As Range
    Dim firstAddress As String
    Dim i As Integer
    Dim searchValue As String
    Dim fileOpened As Boolean
    Dim fileName As String
    Dim fso As Object

    ' 변수 초기화
    Set wb = Nothing
    fileOpened = False
    Set fso = CreateObject("Scripting.FileSystemObject")

    On Error GoTo ErrorHandler

    ' 현재 파일인지 확인 (현재 파일은 건너뜀)
    If filePath = ThisWorkbook.FullName Then
        Exit Sub
    End If

    ' 파일명 추출 (확장자 제외)
    fileName = fso.GetBaseName(filePath)

    ' 엑셀 파일 열기 시도
    Set wb = Workbooks.Open(filePath, ReadOnly:=True, UpdateLinks:=0, Password:="")
    fileOpened = True

    ' 각 검색 값에 대해
    For i = 1 To UBound(searchValues, 1)
        searchValue = Trim(CStr(searchValues(i, 1)))

        ' 빈 값은 건너뜀
        If searchValue = "" Then
            GoTo NextSearchValue
        End If

        ' 워크북의 모든 시트에서 검색
        For Each ws In wb.Worksheets
            On Error GoTo NextSheet

            ' 시트가 보호되어 있거나 숨겨져 있어도 검색 시도
            If ws.Visible = xlSheetVisible Or ws.Visible = xlSheetHidden Then
                Set searchRange = ws.UsedRange

                If Not searchRange Is Nothing Then
                    ' 다양한 검색 방법으로 시도

                    ' 1. 정확히 일치하는 값 검색
                    Set foundCell = searchRange.Find(What:=searchValue, _
                                                    LookIn:=xlValues, _
                                                    LookAt:=xlWhole, _
                                                    SearchOrder:=xlByRows, _
                                                    SearchDirection:=xlNext, _
                                                    MatchCase:=False)

                    ' 2. 정확히 일치하지 않으면 부분 일치 검색
                    If foundCell Is Nothing Then
                        Set foundCell = searchRange.Find(What:=searchValue, _
                                                        LookIn:=xlValues, _
                                                        LookAt:=xlPart, _
                                                        SearchOrder:=xlByRows, _
                                                        SearchDirection:=xlNext, _
                                                        MatchCase:=False)
                    End If

                    ' 3. 수식에서 검색
                    If foundCell Is Nothing Then
                        Set foundCell = searchRange.Find(What:=searchValue, _
                                                        LookIn:=xlFormulas, _
                                                        LookAt:=xlWhole, _
                                                        SearchOrder:=xlByRows, _
                                                        SearchDirection:=xlNext, _
                                                        MatchCase:=False)
                    End If

                    ' 찾은 값이 있으면 모든 일치하는 셀을 찾기
                    If Not foundCell Is Nothing Then
                        firstAddress = foundCell.Address

                        Do
                            ' 검색결과 시트에 결과 기록
                            resultWs.Cells(resultRow, 1).Value = fileName           ' A열: 파일명
                            resultWs.Cells(resultRow, 2).Value = ws.Name            ' B열: 시트명
                            resultWs.Cells(resultRow, 3).Value = foundCell.Value    ' C열: 찾은값
                            resultWs.Cells(resultRow, 6).Value = foundCell.Address  ' F열: 셀위치

                            ' 찾은 값의 이전셀 (D열에 기록)
                            If foundCell.Column > 1 Then
                                On Error Resume Next
                                resultWs.Cells(resultRow, 4).Value = foundCell.Offset(0, -1).Value
                                On Error GoTo NextCell
                            End If

                            ' 찾은 값의 이후셀 (E열에 기록)
                            If foundCell.Column < ws.Columns.Count Then
                                On Error Resume Next
                                resultWs.Cells(resultRow, 5).Value = foundCell.Offset(0, 1).Value
                                On Error GoTo NextCell
                            End If

NextCell:
                            resultRow = resultRow + 1

                            ' 다음 일치하는 셀 찾기
                            On Error Resume Next
                            Set foundCell = searchRange.FindNext(foundCell)
                            On Error GoTo NextSheet

                            ' 처음 찾은 셀로 돌아왔거나 없으면 루프 종료
                            If foundCell Is Nothing Then Exit Do
                            If foundCell.Address = firstAddress Then Exit Do

                        Loop
                    End If
                End If
            End If

NextSheet:
            On Error GoTo ErrorHandler
        Next ws

NextSearchValue:
    Next i

    ' 정상적으로 파일 닫기
    If fileOpened And Not wb Is Nothing Then
        wb.Close SaveChanges:=False
        Set wb = Nothing
    End If
    Exit Sub

ErrorHandler:
    ' 오류 발생 시 안전하게 파일 닫기
    On Error Resume Next
    If fileOpened And Not wb Is Nothing Then
        wb.Close SaveChanges:=False
        Set wb = Nothing
    End If
    On Error GoTo 0
End Sub

✅ 마무리: 클릭 한 번으로 엑셀 검색 끝내기

이제는 반복적인 검색 작업에서 벗어나세요.
엑셀 자동 검색 매크로, 실무에서 지금 바로 활용해보세요.

👇 궁금한 점은 댓글로 질문 주세요!
좋아요 ❤️ + 이웃추가 👥는 큰 힘이 됩니다!

폴더내 모든 엑셀파일에서 검색값 찾기.xlsm

0.03MB

예제파일.zip

0.07MB

저작자표시 비영리 동일조건 (새창열림)

현재글폴더내 하부폴더 포함 모든 엑셀파일 문서 내용에서 검색어 한꺼번에 찾는 법!

VBA 스나이퍼

엑셀 VBA 기초지식을 배우고, 유사 모델을 찾아, 당신의 작품을 완성하세요.

Today :
Yesterday :

일	월	화	수	목	금	토
			1	2	3	4
5	6	7	8	9	10	11
12	13	14	15	16	17	18
19	20	21	22	23	24	25
26	27	28	29	30

VBA 스나이퍼