파일을 열지않고 값 읽어오기2

Step 1: 무엇이 문제였을까요?

이 강좌의 예제는 실행용 파일과 데이터 파일(주상도관련.XLS), 이렇게 두 개의 파일로 구성되어 있으며 반드시 같은 폴더에 두어야 코드가 제대로 작동합니다.

질문주신 분이 사용하신 코드는 VB0051 강좌에서 소개해 드린 XLM 매크로를 이용한 것이었습니다. 이것을 이용하여 A1:W1000 정도 되는 데이터를 워크시트에 불려오니까… 되기는 되는데… 세월아 네월아 기다려야 된다는 것이지요.

이번에는 데이터 파일을 열지 않고 DAO(Data Access Objects)를 이용해 SQL 쿼리문으로 값을 읽어 오는 방법을 사용합니다. 예제를 실행해 보면 불과 1초도 안 되어 순식간에 처리됩니다.

같은 폴더에 압축을 해제 하였는데도 오류가 발생하는 경우, VB Editor 상태에서 '도구-참조' 메뉴를 선택하신 다음, '사용 가능한 참조' 목록에서 'Microsoft DAO O.O Object Library'를 선택하고 '확인'을 누른 후 다시 실행해 보시기 바랍니다.

아이엑셀러

Step 2: 코드 살펴보기

Sub ReadValue()

    Dim strSQL As String
    Dim dbMyDB As DAO.Database
    Dim rstRST As DAO.Recordset
    Dim sngStart As Single
    Dim i As Long
    Const SourceFile As String = "주상도관련.XLS"
    sngStart = Timer

    strSQL = "Select 공번, 심도상부, 심도하부, 토층, 분류, 분류2, 암층, 암종, 터널구간, TCR, RQD, "
    strSQL = strSQL & " RQD등급, BRMR, RMR, RMR등급, Q, Q등급, SMR, SMR등급, SCR, 추가1, 추가2, 추가3 "
    strSQL = strSQL & "FROM [주상도관련$] "

    Set dbMyDB = OpenDatabase(ThisWorkbook.Path & Application.PathSeparator & SourceFile, _
        False, True, "excel 8.0;")
    Set rstRST = dbMyDB.OpenRecordset(strSQL)
    Application.ScreenUpdating = False

    Worksheets.Add after:=Worksheets("Preface")

    With ActiveSheet
        .UsedRange.Clear
        For i = 0 To rstRST.Fields.Count - 1
            Cells(1, i + 1).Value = rstRST.Fields(i).Name
        Next

        .Range("A2").CopyFromRecordset rstRST
        .UsedRange.Columns.AutoFit
    End With

    Application.ScreenUpdating = True
    MsgBox "불과 " & Format(Timer - sngStart, "0.00") & "초 소요되었습니다!", , "www.iExceller.com"

   rstRST.Close
   dbMyDB.Close
   Set rstRST = Nothing
   Set dbMyDB = Nothing

End Sub

그런데... 어떤 연유에서인지는 알기 어려우나, 원본 데이터를 이와 같이 그냥 있는 그대로 불러오는 것은 그다지 의미가 없을 것입니다. 보통의 경우, 원본 데이터 중 조건을 충족하는 특정 항목만 가져온다거나 연산을 수행하고 난 다음 그 결과값만 표시한다거나 하는 경우가 많습니다. 이런 경우는 각종 데이터들을 다루다 보면 비일비재 할 것입니다.

Step 3: 조건에 맞는 결과만 가져오기

다음 버튼을 클릭하면 '공번'별로 소요된 비용을 집계하여 그 결과만을 <실행 결과>와 같은 형태로 보여줍니다.

<실행 결과>

아이엑셀러

합계를 구하는 과정을 추가하였음에도 불구하고 앞의 코드와 마찬가지로 순식간에 자료가 추출됨을 알 수 있습니다.

코드는 앞의 것과 거의 비슷하고 쿼리(Query)문의 구조가 조금 바뀌었습니다.

    strSQL = "Select 공번, Sum(비용) As 비용합계 "
    strSQL = strSQL & "FROM [주상도관련2$] "
    strSQL = strSQL & "Group By 공번 "

다른 부분은 똑같으니까 설명은 생략합니다.

데이터를 많이 다루는 분은(특히나 대용량 데이터) 이번 시간에 소개해 드린 내용과 더불어 VB0170, VB0171 강좌 등에 대해서도 잘 정리해 두시기 바랍니다.

다음에 또…

정리 — DAO로 값 읽어오기 코드 핵심

자주 묻는 질문 (FAQ)

마치며

대용량 데이터를 다룰 때는 셀 단위로 읽지 말고 쿼리로 한 번에 가져오는 것이 핵심입니다. 조건 추출이나 합계 같은 연산도 쿼리문에서 끝낼 수 있습니다.