Excel VBA로 정형화 된 엑셀 파일 데이터 취합하기

오늘 오후 8시경 오랜만에 예전 근무했던 회사 동기에게 전화를 걸었다.
평소 매우 밝은 친군데 10년만에 처음 들어보는 지친 목소리였다.

갑작스런 고객사 요청으로 수만개의 엑셀 파일의 데이터를 취합하고 있었다.
지금 분위기로는 자정 이전 퇴근은 불가능한 상황.
~~사실상 밤샐 위기랜다.~~

친구의 부서 특성상 시험기에서 벹어낸 정형화된 데이터 파일일듯해서 자세한 상황을 물어보니,

1. 시험기에서 벹어낸 규칙적인 이름의 파일이다.
2. 파일 별 특정 시트(5번째 시트, 심지어 시트명도 항상 동일하다.)의 값을 복사해야 한다.
3. 시트 내 동일한 위치(E3:I14)의 값을 복사해야 한다.

와 같았다.

'이거 파이썬 몇줄이면 바로 해결 가능하겠는데!?'

바로 개발자였던(~~수학과 석사로 IT회사에서 근무했던, 지금은 금융업에 종사중이다.~~) 친구에게 전화를 걸었더니
엑셀 VBA로도 충분 하댄다.
엑셀 전문가 석선생님(현직 수학교사, 현재는 파이썬도 공부하고 계신다.)께 전화 했더니,
구두로 해결방안을 간단히 설명해 주셨다.

30분동안 혼자 낑낑대며 완성한 코딩.

Sub CopyDataToMasterFile()
    
    Dim folderPath As String ' 데이터를 가져올 폴더 경로
    Dim masterFilePath As String ' 결과 파일 경로
    Dim targetWorksheet As Worksheet ' 결과 파일에서 데이터를 붙여넣을 시트
    Dim targetRange As Range ' 결과 파일에서 데이터를 붙여넣을 범위
    Dim filePath As String ' 데이터를 가져올 파일명
    Dim sourceWorkbook As Workbook ' 데이터를 가져올 엑셀 파일 객체
    Dim sourceWorksheet As Worksheet ' 데이터를 가져올 시트 객체
    Dim sourceRange As Range ' 가져올 데이터 범위
    Dim targetRow As Long ' 결과 파일에서 데이터를 붙여넣을 시작 행
    
    ' 폴더 선택 창 열기
    With Application.FileDialog(msoFileDialogFolderPicker)
        .Show
        folderPath = .SelectedItems(1)
    End With
    
    ' 결과 파일 경로 설정
    masterFilePath = Environ("USERPROFILE") & "\Desktop\취합.xlsx"
    
    ' 파일이 존재하지 않으면 생성
    If Dir(masterFilePath) = "" Then
        Workbooks.Add.SaveAs masterFilePath
    End If

    ' 결과 파일 열어서 시트와 범위 설정
    Set targetWorksheet = Workbooks.Open(masterFilePath).Worksheets(1)
    Set targetRange = targetWorksheet.Range("E1:I1000")
    
    ' 붙여넣을 시작 행 설정
    targetRow = targetWorksheet.Cells(Rows.Count, 1).End(xlUp).Row + 1
    
    ' 폴더 내 모든 파일에 대해서 반복
    filePath = Dir(folderPath & "\*.xls*")
    Do While filePath <> ""
        
        ' 데이터를 가져올 엑셀 파일 열기
        Set sourceWorkbook = Workbooks.Open(folderPath & "\" & filePath)
    
        ' 데이터를 가져올 시트와 범위 설정
        With sourceWorkbook
            Set sourceWorksheet = .Worksheets("내경 ")
            Set sourceRange = sourceWorksheet.Range("E3:I14")
        End With
    
        ' 데이터 복사 후, 결과 파일에 붙여넣기
        sourceRange.Copy
        targetWorksheet.Range("E" & targetRow).PasteSpecial Paste:=xlValues
        
        ' 다음에 붙여넣을 행 위치 조정
        targetRow = targetRow + 12

        ' 열었던 엑셀 파일 닫기
        sourceWorkbook.Close False
    
        ' 다음 파일명 읽어오기
        filePath = Dir
    Loop
    
    ' 결과 파일 저장
    targetWorksheet.Parent.Save
    
End Sub