DAO를 사용한 여러 시트 데이터 통합

Step 1: 준비하기

이번 예제는 두 개의 파일로 구성됩니다. 통합 작업을 실행하는 통합 문서 이외에 DAOSample.xls라는 파일이 더 필요하며, 두 파일을 반드시 같은 폴더에 두어야 합니다. 그렇지 않으면 오류가 납니다.

또한 버튼을 누르기 전에 VB Editor(Alt + F11)에서 '도구-참조' 메뉴를 선택하고 'Microsoft DAO 3.x Object Library'를 체크해야 합니다. 역시 그렇지 않으면 오류 메시지가 나타납니다.

Step 2: 어떤 데이터를 통합하나요?

방금 무슨 일을 하였느냐 하면… 일단 아래의 파일을 보시기 바랍니다.

아이엑셀러

위 테이블은 어떤 지점의 거래처별/일자별 판매실적 데이터 입니다. 이 지점의 영업담당 홍길동씨는 Exceller의 강좌를 열심히 잘 따라하여 거래처별 실적을 위와 같이 데이터 베이스 형태에 충실하게 매일매일 정리를 잘 해 놓았습니다.

그런데 년말이 되자 한 가지 문제가 생겼습니다. 월별 데이터를 모두 합쳐서 하나의 시트에 통합을 해야 하는데 일일이 수작업으로 처리하기가 어렵다는 것입니다. 물론 데이터 통합이나 다중 통합 피벗 테이블 등을 잘 활용하면 불가능 한 것은 아니지만 데이터가 여러 파일에 분산되어 있다거나 시트가 수십, 수백 개로 나뉘어져 있다면 손발이 아주 바쁘게 움직여야 하겠지요?

하지만 DAO(Data Access Object)를 활용하면 쉽게 처리가 됩니다.

Step 3: 코드 살펴보기

Sub ConsolidateData()
    Dim dbsMyDatabase As Database
    Dim rstMyRST As Recordset
    Dim shtUnion As Worksheet
    Dim strMySQL As String
    Dim Msg As String
    Dim strFileName As String
    Dim i As Integer
    ' 등장인물, 즉 변수를 선언하는 부분부터 예사롭지가 않군요. 눈에 익숙하지 않은
    ' 데이터 타입이 잔뜩 올라와 있지요? 아주 예전에 소개를 드린 기억이 나긴 합니다만…
    ' 사실 DAO라는 녀석은 쉽게 말하자면, 엑셀의 졸개가 아니라 데이터베이스 프로그램인
    ' 억세스의 졸개라고 할 수 있습니다. 즉 내 밑에 있는 부하가 아니라 남의 부하를 데려와서
    ' 작업을 시킬려니까 이것 저것 챙겨주어야 할 것이 많다고 생각하시면 되겠습니다.

    Set shtUnion = Worksheets.Add
    shtUnion.Move after:=Worksheets("Preface")
    strFileName = ThisWorkbook.Path & "\" & "DAOSample.xls"
    On Error GoTo ET
    ' 워크시트를 한 장 삽입하고 작업 대상 파일 이름을 문자열 변수에 담아둡니다.

    strMySQL = "select * from [1월$] union all "
    strMySQL = strMySQL & "select * from [2월$] union all "
    strMySQL = strMySQL & "select * from [3월$] union all "
    strMySQL = strMySQL & "select * from [4월$] union all "
    strMySQL = strMySQL & "select * from [5월$] union all "
    strMySQL = strMySQL & "select * from [6월$] union all "
    strMySQL = strMySQL & "select * from [7월$] union all "
    strMySQL = strMySQL & "select * from [8월$] union all "
    strMySQL = strMySQL & "select * from [9월$] union all "
    strMySQL = strMySQL & "select * from [10월$] union all "
    strMySQL = strMySQL & "select * from [11월$] union all "
    strMySQL = strMySQL & "select * from [12월$] "
    ' 엑셀의 시트에 접근하기 위해서는 [시트명$]와 같이 $를 시트명 뒤에 붙입니다.

    Set dbsMyDatabase = OpenDatabase(strFileName, False, False, "Excel 8.0;")
    Set rstMyRST = dbsMyDatabase.OpenRecordset(strMySQL)
    ' 여기서부터 DAO를 이용합니다. OpenDatabase라는 메서드를 이용하여 엑셀 파일을
    ' 엽니다. 이 때 파일의 형식(Excel 8.0)도 함께 DAO에게 알려줍니다.

    With ActiveSheet
        [A1].CurrentRegion.Clear
        For i = 0 To rstMyRST.Fields.Count - 1
            .Cells(1, i + 1).Value = rstMyRST.Fields(i).Name
            ' 필드명을 첫 행에 나타내고…

        Next i
        [A2].CopyFromRecordset rstMyRST
        ' 새로 삽입한 시트 위에 레코드들을 뿌려 줍니다.

    End With
    Columns("A:A").EntireColumn.AutoFit
    Msg = "DAO를 이용하여 눈 깜짝할 사이에 ""DAOSample.xls"" 파일 내의 모든 시트를 통합하였습니다"
        MsgBox Msg, , "www.iExceller.com"

    DecorateTable
    ' 위 외부 프로시저는 테이블의 서식을 지정하기 위한 것입니다.

    rstMyRST.Close
    dbsMyDatabase.Close '엑셀 데이터 베이스도 닫습니다
    Set rstMyRST = Nothing
    Set dbsMyDatabase = Nothing
    ' 사용하였던 오브젝트 변수들을 닫습니다. 즉 메모리를 비워줍니다.

    Exit Sub
ET:
    MsgBox Err.Description, , "에러 코드: " & Err.Number
    Err.Clear
End Sub

엑셀 시트에 접근할 때는 [시트명$]처럼 시트명 뒤에 $를 붙입니다. 12개월 시트를 union all로 이어 붙인 SQL을 OpenRecordset에 전달하면 모든 시트의 레코드가 하나의 레코드셋으로 만들어지고, CopyFromRecordset으로 새 시트에 한꺼번에 뿌려 줍니다. OpenDatabase 메서드에서는 파일의 형식(Excel 8.0)도 함께 지정합니다.

표의 서식을 지정하는 외부 프로시저는 다음과 같이 간단하게 작성할 수 있습니다.

Sub DecorateTable()

    With [A1].CurrentRegion
        .Borders.LineStyle = xlContinuous
        .Columns.AutoFit
        With .Rows(1)
            .Font.Bold = True
            .Interior.ColorIndex = 15
            .HorizontalAlignment = xlCenter
        End With
    End With

End Sub

처음 접하는 분들은 눈이 좀 피곤하기도 하겠습니다만, 생각보다는 엄청나게 간단하지요? 더군다나 이러한 방법은 데이터 량이 많으면 많을수록 그 진가를 발휘합니다.

다음 시간에 또…

정리 — DAO 시트 통합 핵심 코드

자주 묻는 질문 (FAQ)

마치며

데이터량이 많을수록 DAO를 이용한 통합은 진가를 발휘합니다. 수작업으로 시트를 합치던 업무에 응용해 보시기 바랍니다.