 |
5. 중복 데이터 처리 -Ⅱ
자료다운로드 : 오튜공구함005.xls
안녕하세요. 오튜가족 여러분
오늘은 지난 시간에 이어 중복데이터를 처리하는 기능을 만들어 보겠습니다. 오랜만에 연재를 다시 시작하려니까 방학숙제하는 기분인데요, 요즘 초등학생들은 방학숙제가 없다더군요. 사실 그동안 논 것은 아니고 여러 가지 일이 있었습니다.
회사일(저희 부장님이 퇴사하는 바람에 저혼자 모든 일을 하게 되었거든요), 번역일(장난이 아니더군요. 한마디로 행복끝-불행시작입니다 ), 개인홈페이지 자료만들기(공개하기엔 좀 아까운 것이 많았지만...), 그리고 이 컬럼연재물 준비....
사실 이것 때문에 저는 나름대로 고생을 했는데요, 이상하게도 프로그램이 진척이 안되고 내용이 너무 어려워지고 구현도 오래 걸려 애초의 계획에서 많이 후퇴했습니다. 무슨 살(煞)이 끼었는지... 굿을 할 수도 없구.... 괜히 주제 잘못 골랐다 싶어 취소하고 싶었지만 그래도 체면이 있지... 어쨌든 간단히나마 프로그램이 돌아가는 걸 보고 이렇게 여러분앞에 나서게 되었습니다.
묻고답하기 게시판에는 많은 중복데이터 관련 질문이 있더군요. 대개의 경우 중복데이터를 골라내 처리하는 것인데 엑셀의 필터링기능을 사용하면 가능합니다. 그러나 이를 이용하지 않고 ADO의 필터링 기능을 사용하고자 합니다. 물론 중복테이더처리에 ADO까지 동원할 필요는 없습니다. 그저 ADO연습삼아 만들어 보도록 하죠.
프로그램은 하나의 폼과 하나의 모듈로 구성됩니다.
- 폼에서는 사용자가 중복데이터부분을 선택하고 그 처리를 어떻게 할 것인지 결정하도록 합니다.
- 모듈에서는 ADO관련 코드를 두었습니다. 폼에서 결정한 사항을 모듈에서 처리할 겁니다.
먼저 폼을 만들어 보겠습니다.
 |
데이터범위 텍스트상자를 클릭하면 셀범위를 입력할 수 있게 됩니다. 셀범위는 원하는 데이터가 있는 셀을 클릭하면 됩니다. 드래그 할필요는 없습니다.
필드 리스트상자는 선택한 범위를 하나의 데이터베이스 테이블이라고 가정하고 필드를 보여줄 것입니다.
중복값은 데이터범위를 선택시 클릭한 셀의 값이 들어갑니다. 따라서 셀 범위 선택시 필터링할 값이 있는 셀을 선택하는 게 좋습니다.
Action에서는 중복데이터를 어떻게 처리할 것인가를 결정합니다. 선택한 데이터만 복사하거나 선택한 데이터를 제외한 나머지 데이터만 복사합니다.
확인을 클릭하시면 새 시트가 만들어지고 거기에 데이터가 복사됩니다. |
위와 같은 폼을 만들었다면 이제는 코딩에 앞서 ADO를 사용하기 위해 다음과 같이 ADO개체를 참조합니다.
먼저 유저폼의 코드를 살펴보겠습니다.
| UserForm.bas |
Option Explicit
작업대상이 되는 범위를 저장하는 범위개체변수입니다. 유저폼내 여러프로시져가 이를 참조할 필요가 있어 전역변수로 두었습니다.
Dim rngData
As Range
cmdCancel버튼의 클릭이벤트입니다.
Private Sub cmdCancel_Click()
유저폼을 언로드합니다. 여기서 Me는 유저폼을 말합니다.
Unload Me
End Sub
처리할 데이터범위를 담는 txtRange입력상자의 이벤트입니다. 여기서는 마우스를 클릭했을 때 이벤트를 사용하고 있습니다. txtRange입력상자에 마우스를 클릭하면 유저폼은 잠깐 숨겨지고 데이터범위를 입력받는 상자가 나타날 것입니다. 입력은 마우스로 데이터가 있는 범위중 임의의 셀을 클릭합니다. 그러면 이를 기준으로 Range개체의 CurrentRegion을 이용합니다. 따라서 처리할 목록이 데이터베이스형태를 갖추고 있어야 합니다.
Private Sub txtRange_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
Dim rngSelect As Range
On Error GoTo ErrHandler
유저폼을 숨깁니다. 다음 셜명에 나올 것이지만 셀범위를 입력받기 위해 InputBox를 띄울 것인데 이때 유저폼을 숨겨 두 개의 대화상자가 같이 있는 걸 막기 위한 것입니다.
Me.Hide
InputBox메소드(함수인 InputBox와 다릅니다)는 다양한 종류의 데이터를 받아들일 수 있습니다. InputBox에 필요한 매개변수중 Type은 받아들일 입력의 종류를 정하는 것입니다. Type은 다음과 같습니다.
0 수식
1 숫자
2 문자열
4 논리값 (True 또는 False)
8 Range 개체와 같은 셀 참조 영역
16 #N/A와 같은 오류값
64 값 배열
#문자열과 숫자를 모두 사용할 수 있는 입력란을 만들려면 Type을 1 + 2의 값(3)으로 설정합니다. Type값은 비록 숫자이긴 하지만 데이터형식은 Variant입니다. InputBox 대화 상자에는 [확인] 버튼과 [취소] 버튼이 있습니다. [확인] 버튼를 선택하면 InputBox 메서드는 대화 상자에 입력한 값을 돌려주고 [취소] 버튼를 선택하면 False를 돌려줍니다.
수식이 필요한 경우 Type 값을 0으로 줍니다. 이때 InputBox 메서드는 "=2*PI()/360"과 같은 수식을 문자열로 돌려줍니다.
이번 예와 같이 셀범위가 필요한 경우Type 을 8로 줍니다. 이때 InputBox 메서드는 Range 개체를 돌려줍니다. 따라서 결과값을 받으려면 아래와 같이 Set 명령문을 사용해야 합니다.
Set myRange = Application.InputBox(prompt := "예제", type := 8)
만일 Set 문을 사용하지 않으면 변수는 Range 개체가 아닌 Range 개체에 있는 값으로 설정됩니다.
위의 설명대로 셀범위를 받기 위해 InputBox메소드를 사용했으며 Type은 8로 하였습니다.
Set rngSelect = Application.InputBox(Prompt:="필터링하려는 테이터가 있는 위치를 마우스로 선택하세요", Title:="중목데이터 선택",
Default:=ActiveCell.Address, Left:=1, Top:=1, Type:=8)
선택한 셀의 값이 필터링하려는 값으로 가정하고 중복값 텍스트상자에 입력합니다.
txtValue.Text = rngSelect.Item(1, 1)
돌려받은 셀범위를 중심으로 하나의 블록처럼 셀범위를 확대합니다. 즉 선택한 셀을 중심의 작업할 데이터베이스영역을 정합니다.
rngSelect.CurrentRegion.Select
다시 위에서 선택한 영역을 rngData 개체변수로 받습니다.
Set rngData = Selection
위의 Range의 주소를 txtRange에 입력하여 사용자가 원하는 작업영역을 선택하였는가 알 수 있도록 보여줍니다.
txtRange.Text = rngData.Address
아래의 Sub AddItemIntoList(…)프로시져는 데이터베이스 영역인 rngData를 받아 가장 상단의 행을 필드로 가정하여 리스트박스의 목록으로 삽입합니다.
AddItemIntoList rngData
이제 InputBox 대화상자가 사라졌으므로 다시 유저폼을 보여줍니다.
Me.Show
사용한 범위개체변수를 메모리에서 풀어줍니다.
Set rngSelect = Nothing
Set rngData = Nothing
ErrHandler:
End Sub
다음의 프로시져 Sub AddItemIntoList 는 선택한 데이터범위를 데이터베이스 테이블, 선택한 데이터범위에서 최상단 행의 각각의 열을 필드로 가정합니다. 그리고 필드의 이름을 필트 리스트상자에 입력합니다.
매개변수로는 범위개체변수를 받습니다. 즉 선택한 데이터범위를 넘겨받습니다.
Private Sub AddItemIntoList(rngData As Range)
Dim i As Byte
Dim rngFlds As Range
Dim strFirstCell As String
선택한 범위에서 그 주소는 예를 들어 "$A$2:$E$110" 처럼 표시될 것입니다. 이때 ":" 이전의 주소값인 "$A$2" 를 추출합니다. 이는 필드의 값을 얻기 위한 준비인데, 이보다 더 좋은 방법도 있을 겁니다. 여기서는 Left()함수와 InStr()함수를 사용하였습니다.
strFirstCell = Left(rngData.Address,
InStr(1, rngData.Address, ":") - 1)
Left()함수는 문자열의 왼쪽부터 시작하여 지정한 만큼의 문자열을 되돌려주는 함수입니다.
InStr()는 찾으려는 문자의 위치를 알려주는 함수입니다. 즉 InStr(1, rngData.Address, ":") 은 rngData.Address내에서 처음(1)부터 시작하여 ':' 라는 문자를 찾아 그위치를 돌려주라는 의미이죠.
가령 $A$2:$E$110라는 주소가 있을때Left("$A$2:$E$110", InStr(1, "$A$2:$E$110", ":") - 1) 하면 '$A$2'만을 돌려줄 것입니다.
앞서 구한 범위의 첫번째 셀값을 가지고 필드이름이 있는 셀범위를 구합니다.
Set rngFlds =
Range(ActiveSheet.Range(strFirstCell),
ActiveSheet.Range(strFirstCell).End(xlToRight))
여기서 눈에 띄는 것은 .End(xlToRight) 이죠. 이는 Range개체의 속성중 하나로서 Range개체를 중심으로 상하좌우로 데이터가 있는 범위의 끝을 의미합니다.
End속성에 사용할 수 있는 값은 다음과 같습니다. xlUp, xlDown, xlToLeft, xlToRight
자세한 사항은 첨부한 ot005.xls파일의 Range_End워크시트와 그 시트의 Sub RangeEnd연습() 프로시저를 실행해보시면 아실 겁니다. 꼭 보시길 바랍니다
필드이름이 있는 셀범위(rngFlds)를 구하였다면 각각의 필드값을 필드리스트상자(lbFields)에 입력합니다. 이때 Columns라는 하위개체를 사용하고 있습니다. 그리고 다시 열의 갯수를 알려주는 Count속성을 이용하여 루프문을 돌리면서 범위의 각 값을 리스트상자(lbFields) 항목으로 추가합니다.
For i = 1 To rngFlds.Columns.Count
리스트상자(lbFields)에 아이템을 추가하는 것은 AddItem방법을 사용하고 있습니다. 또 범위개체(rngFlds)의 각 셀의 값을 읽어오기 위해 Item(r,c)을 사용하였습니다.
Item속성(혹은 개체로도 사용합니다)은 Item(r,c) 혹은 Item(r) 사용하는 데 r과 c값에 의해 지정된 범위의 개체를 돌려줍니다.
lbFields.AddItem rngFlds.Item(1, i)
Next
Set rngFlds = Nothing
End Sub
확인(OK)버튼을 클릭하는 경우 처리하는 이벤트입니다. 여기서 나오는 굵게표시한 부분은 다음 시간에 설명드릴 프로시져라 오늘은 생략합니다. 다만 기능만 간단히 설명하면
CreateDatabase 임시데이터베이스를 만드는 프로시져
CreateRecordset 선택한 범위에 데이터를 입력하는 프로시져
FilterRecordset 조건에 따라 필터링하는 프로시져
Private Sub cmdOK_Click()
Dim i As Byte
CreateDatabase rngData
CreateRecordset rngData
아래의 코드는 필터링할 필드와 값을 찾아 필터링프로시져(FilterRecordset)에 넘겨주는 부분입니다. 리스트상자(lbFields)의 모든 항목을 루프로 돌리면서 선택되었는가(Selected)를 찾아냅니다.
For i = 0 To lbFields.ListCount - 1
If lbFields.Selected(i) Then
FilterRecordset lbFields.List(i), txtValue.Text
찾아내어 필터링프로시져에 값을 넘겼다면 더이상 루프를 돌릴 필요가 없으므로 아래와 같이 For문을 빠져나옵니다.
Exit For
End If
Next i
앞서에서는 Me는 현재의 유저폼을 의미한다고 말씀드렸죠. 모든 작업이 끝났으므로 자신을 메모리에서 언로드합니다.
Unload Me
End Sub
|
오늘은 여기까지입니다. 다음 시간에 나머지 부분을 가지고 찾아 뵙겠습니다.
|
