OfficeTutor 365

6. 중복 데이터 처리 -Ⅲ

자료다운로드 : ot006.xls 

안녕하세요. 두 달동안 여름 방학을 보내고 가을을 맞아 오튜공구함을 다시 시작하는 곽승주입니다. 오랜만에 쓰려니까 매우 쑥쓰럽군요. 지난 번에도 컬럼 연재가 늦었는데, 이번에도 상당히 늦었습니다.

이번 시간에는 중복데이터를 처리하는 세 번째 연재 중 마지막 부분입니다.
이번 중복데이터를 요약하자면, 워크시트의 목록을 하나의 데이터베이스 테이블로 저장하고 사용자가 지정한 간단한 조건에 따라 다시 쿼리하여 새 워크시트를 만들어 결과를 내보내는 것이 목적입니다. 그래서 폼에서는 목록의 영역(테이블이 될 부분이죠)을 선택하고 , 어느 필드에서 어떤 값을 가진 데이터만 복사할 것인지 아니면 이를 뺀 나머지를 복사할 것인지 지정하게 됩니다.

오늘은 데이터베이스파일을 만들고, 테이블을 만듭니다. 그리고 여기에서 지정한 조건에 따라 쿼리를 하여 워크시트에 뿌려주는 코딩을 보여 줄 것입니다. 먼저 소스를 보여드리죠.

먼저 나오는 것이 데이터베이스파일을 만드는 CreateDatabase()프로시져입니다. 데이터베이스자체에 대한 정의 (데이터베이스 생성, 테이브생성, 필드생성등등) 를 위해서는 지난 시간에 개체를 참조하는 그림에서 보았듯이 "Microsoft ADO Ext. 2.5 for DDL and Security"를 참조해야 합니다. 

CreateDatabase( )프로시져는 rngData라는 워크시트 목록영역을 넘겨받아 이를 테이블로 만들 것입니다.
Public Sub CreateDatabase(rngData As Range)
   Dim i As Byte

카탈로그란 데이터베이스 정의를 위한 개체입니다.
   Dim catCatalog As New Catalog

테이블을 의미하는 Table개체를 선업합니다.
   Dim tblTable As ADOX.Table

   Dim fldName As String

이전에 만든 mdb파일이 있다면 이번 작업을 위해 지워야 합니다. 그래서 작업중인 폴더에서 파일유무를 검사하고 있으면 지웁니다.
   If Len(Dir(Application.DefaultFilePath & "\임시.mdb")) <> 0 Then
      Kill Application.DefaultFilePath & "\임시.mdb"
   End If

카탈로그 개체를 생성하는 부분이죠. 이때 Provider=Microsoft.Jet.OLEDB.4.0는 액세스2000에서 지원하는 형식입니다. 97버전에서는 이렇게 만든 mdb파일을 읽을 수 없습니다. 97버전과 호환되려면 4.0대신 3.5를 사용합니다.
   catCatalog.Create "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Application.DefaultFilePath & "\임시.mdb;"

   Set tblTable = New Table

테이블의 이름은 "임시"라고 지정합니다.
   With tblTable
      .Name = "임시"
목록의 열만큼 필드를 만들어 줍니다. 이때 필드이름은 rngData.Item(1, i)와 같이 범위의 첫번째 행의 값들이 됩니다.
      For i = 1 To rngData.Columns.Count
         fldName = CStr(rngData.Item(1, i))

필드를 추가하는 부분입니다. 
         .Columns.Append fldName
      Next
   End With
카탈로그개체에 앞서 만든 테이블 개체를 붙입니다.
   catCatalog.Tables.Append tblTable

   Set tblTable = Nothing
   Set catCatalog = Nothing
End Sub

CreateRecordset() 프로시져는 앞서 만든 임시.mdb파일의 "임시"라는 테이블에 레코드를 추가하는 일을 합니다.
Public Sub CreateRecordset(rngData As Range)
   Dim i As Byte
   Dim r As Long
   Dim strSQL As String

데이터베이스 연결개체를 선언합니다.
   Dim cnnConnection As ADODB.Connection

선언한 연결개체에 임시.mdb파일을 연결하는 부분입니다.
   Set cnnConnection = New ADODB.Connection
   cnnConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Application.DefaultFilePath & "\임시.mdb"

   With cnnConnection
      For r = 2 To rngData.Rows.Count

영역의 행만큼 반복하면서 INSERT SQL문을 만듭니다.
         strSQL = "INSERT INTO 임시 VALUES("
         For i = 1 To rngData.Columns.Count
            strSQL = strSQL & "'" & rngData.Cells(r, i) & "',"
         Next
         strSQL = Left(strSQL, Len(strSQL) - 1) & ")"

완성한 SQL 문을 실행합니다.
         .Execute strSQL

      Next r
      .Close
   End With

   Set cnnConnection = Nothing
End Sub

여기에서는 사용자가 지정한 필드에서 지정한 값을 찾아 지정한 방법으로 데이터를 만들어줍니다.

Public Sub FilterRecordset(strFld As String, strValue As String)
   Dim cnnConnection As ADODB.Connection
   Dim rstRecordSet As ADODB.Recordset
   Dim fldField As ADODB.Field
   Dim strSQL As String
   Dim strRecordset As String
   Dim strFiltering As String
   Dim r As Long
   Dim c As Byte
   Dim i As Byte

   Set cnnConnection = New ADODB.Connection
   cnnConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Application.DefaultFilePath & "\임시.mdb"

   Set rstRecordSet = New ADODB.Recordset
   With rstRecordSet
      .ActiveConnection = cnnConnection
      .CursorLocation = adUseClient
      .CursorType = adOpenDynamic

먼저 테이블 전체를 조회합니다.
      strSQL = "SELECT * FROM 임시"
     .Open strSQL

사용자가 선택한 "Action" 즉 Copy 또는 Reverse Copy에 따라 앞에서 조회한 레코드에 필터링 조건을 만듭니다.
      If UserForm1.opCopy = True Then
         strFiltering = strFld & "='" & strValue & "'"
      ElseIf UserForm1.opReverse = True Then
         strFiltering = strFld & "<>'" & strValue & "'"
      End If

ActiveWorkbook.Worksheets.Add

앞에서 만든 필터링조건에 따라 필터링을 실행합니다.
      .Filter = strFiltering

      Debug.Print "Filtered Recordset>>>>>"

필드이름을 추가한 시트에 출력합니다.
      c = 1
      For Each fldField In .Fields
         ActiveSheet.Cells(1, c) = fldField.Name
      c = c + 1
      Next

레코드값을 출력합니다.
      r = 2
      Do Until .EOF
         c = 1
         For Each fldField In .Fields
            ActiveSheet.Cells(r, c) = fldField.Value
            c = c + 1
         Next
         r = r + 1
        .MoveNext
     Loop
   End With

   rstRecordSet.Close
   cnnConnection.Close

Set rstRecordSet = Nothing
Set cnnConnection = Nothing
End Sub

이상으로 중복데이터를 걸러내는 도구를 만들어 보았습니다. 그러나 사실 엑셀의 기능을 사용하는 것보다는 느립니다. 아마 전체 테이블을 조회하는 부분에서 시간이 걸리는 것 같습니다. 아마 엑셀의 기능을 사용하시는 것이 좋을 것입니다. 다만 제가 굳이 이런 걸 만든 것은 ADO개체의 사용을 보여드리고자 하는 예입니다. 다음 시간에는 훨씬 쉬운 주제로 만나보겠습니다.

그럼 수고하세요

목차 | 이전 | 다음