[기초방] VBA 100제 #94 [ ADODB 데이터조회 ]

엑셀 VBA를 사용하면 외부 데이터베이스처럼 엑셀 파일에서 데이터를 조회하고 관리할 수 있다.

이번 포스팅에서는 ADODB를 사용해 SQL 쿼리로 데이터를 필터링하고 엑셀에 출력하는 방법을 소개한다.

 

이 코드는 엑셀 데이터를 SQL 방식으로 조회하고 원하는 셀에 결과를 자동으로 출력한다.

코드와 주석

1. Haja_Guid_Pivot 서브 프로시저

Option Explicit  ' 변수 선언을 강제하여 코드의 안정성을 높임

Sub Haja_Guid_Pivot()

    ' ADODB 참조를 위한 GUID를 문자열로 선언
    Dim StrGuid$: StrGuid = "{B691E011-1797-432E-907A-4D8C69339129}"
    
    ' 오류 발생 시 무시하고 다음 코드로 넘어감 (참조 추가 시 에러 방지)
    On Error Resume Next
        ' 가장 최신 버전의 ADODB를 참조에 추가함
        ThisWorkbook.VBProject.References.AddFromGuid StrGuid, 0, 0
    On Error GoTo 0  ' 이후 오류를 정상적으로 처리하도록 복구

    ' 기초방94 서브 프로시저 호출
    Call 기초방94

End Sub

 

2. 기초방94 서브 프로시저

Sub 기초방94()

    ' ADODB 레코드셋 객체 생성
    Dim Rs As New ADODB.Recordset
    ' 파일 경로, SQL 쿼리, 연결 문자열을 위한 변수 선언
    Dim strPath$
    Dim strSQL$, strConn$
    ' 데이터 범위와 반복문에 사용할 변수 선언
    Dim rngAll As Range, rngA As Range, rngX As Range
    Dim i&  ' 정수형 변수 선언
    
    ' 현재 열려 있는 엑셀 파일의 전체 경로를 저장
    strPath = ThisWorkbook.FullName
    
    ' E4:E11 범위에 있는 성명 목록을 rngAll에 설정
    Set rngAll = [E4:E11]
    ' F4 셀을 데이터 출력 시작 셀로 설정
    Set rngX = [F4]
    
    ' ADODB 연결 문자열 설정 (엑셀 파일을 데이터베이스처럼 사용)
    strConn = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
              "Data Source=" & strPath & ";" & _
              "Extended Properties=Excel 12.0;"
    
    ' 성명 목록을 순회하면서 각 성명에 대한 데이터를 조회
    For Each rngA In rngAll
    
        ' 해당 성명에 대한 SQL 쿼리 작성
        strSQL = " SELECT 국어, 영어, 수학 " & _
                 " FROM [기초방94$K3:N24] " & _
                 " WHERE 성명 = '" & rngA & "'"

        ' SQL 쿼리를 실행해 Recordset에 결과를 저장
        Rs.Open strSQL, strConn

        ' 조회된 결과가 있으면 데이터 출력
        If Not Rs.EOF Then
            ' 레코드셋 데이터를 F4부터 순차적으로 복사
            rngX.CopyFromRecordset Rs
            
            ' 출력 위치를 한 행 아래로 이동
            Set rngX = rngX.Offset(1)
        End If

        ' Recordset을 닫아 다음 조회에 대비
        Rs.Close
    Next rngA

    ' 사용한 객체 메모리 해제
    Set Rs = Nothing

End Sub

---

코드 설명

1. Haja_Guid_Pivot 프로시저

• 이 프로시저는 ADODB 라이브러리를 참조에 추가해 데이터베이스 연결을 가능하게 한다.
• GUID를 사용해 가장 최신 버전의 ADODB를 참조에 추가하고, 이후 기초방94 프로시저를 호출해 데이터를 조회한다.
• 오류가 발생하더라도 무시하고 다음 코드로 넘어가도록 설정해 참조 추가 시 오류를 방지한다.

2. 기초방94 프로시저

• 이 프로시저는 SQL 쿼리를 통해 엑셀 데이터를 조회하고, 그 결과를 F4 셀부터 출력한다.
• E4범위에 있는 성명 목록을 순회하며, 각 성명에 대해 K3범위의 성적 데이터를 조회한다.
• SQL 쿼리에서 조회한 국어, 영어, 수학 점수를 F4 셀부터 순차적으로 출력하며, 출력 위치를 매번 한 행 아래로 이동한다.

---

코드 동작 예시

1. 원본 데이터 (기초방94)

성명	국어	영어	수학
홍길동	90	85	80
김철수	70	75	85
이영희	95	90	88

 

3. F4 셀부터 출력된 결과

국어	영어	수학
90	85	80
70	75	85
95	90	88

---

코드의 특징과 장점

1. SQL 쿼리를 활용한 엑셀 데이터 조회
   • 엑셀 파일을 데이터베이스처럼 활용해 필터링된 데이터를 조회한다.
   • SQL 쿼리를 사용하면 조건부 조회와 정렬이 간편해진다.
2. 자동화된 데이터 출력
   • 조회한 데이터는 F4 셀부터 순차적으로 출력되며, 출력 위치가 자동으로 아래로 이동한다.
3. ADODB를 통한 연결
   • ADODB를 사용해 엑셀 데이터와 원활하게 연결한다.
   • 엑셀 외부 데이터와도 동일한 방식으로 처리할 수 있다.
4. 참조 추가 오류 방지
   • GUID로 참조를 추가하면서 오류를 무시하도록 설정해 안정적인 코드 실행을 보장한다.

---

결론

이 코드는 엑셀 데이터를 SQL 쿼리로 조회하고 자동으로 출력하는 방법을 보여준다.

이를 통해 반복적인 데이터 조회와 출력 작업을 자동화할 수 있다.

 

또한, ADODB를 사용하면 엑셀뿐만 아니라 외부 데이터베이스와의 연동도 가능하다.

이 코드를 활용하면 시간을 절약하고 데이터 처리의 정확도를 높일 수 있다. 앞으로 더 많은 데이터나 다양한 조건으로 조회할 때도 쉽게 확장할 수 있다.

 

 

 

 

 

코드 동작 원리

1. Va, Vb, Vc 변수 선언
   • Va: 국어, 영어, 수학 점수 데이터를 담기 위한 변수.
   • Vb: 성명 목록을 담는 변수.
   • Vc: 점수를 조회할 기준이 되는 성명 데이터 범위를 담는 변수.
2. With Application 구문
   • 엑셀의 Application 객체를 사용해 다양한 엑셀 메서드를 효율적으로 호출한다.
   • 이 코드에서는 ArrayToText 메서드를 사용해 데이터를 텍스트 배열로 변환하고 저장한다.
3. ArrayToText 함수 사용
   • 이 함수는 엑셀 범위를 텍스트 형태의 배열로 변환한다.
     1. Va: L4:N23 범위에 있는 점수 데이터를 변환해 저장.
     2. Vb: E4:E11 범위에 있는 성명 목록을 변환해 저장.
     3. Vc: K4:K23 범위의 성명 데이터를 변환해 저장.
4. 동적 수식을 F4 셀에 설정
   • Formula2 메서드를 사용해 엑셀 동적 배열 수식을 F4 셀에 입력한다.
   • 설정된 수식:
   • excel
     코드 복사
     =CHOOSEROWS(Va, XMATCH(Vb, Vc))
   • 수식 설명:
     • XMATCH 함수: Vb 범위의 각 성명과 Vc 범위의 성명을 매칭하여 인덱스를 반환한다.
     • CHOOSEROWS 함수: Va 배열에서 **해당 인덱스에 맞는 행(점수)**을 가져온다.
     • 이 수식을 통해 성명별로 매칭된 점수 데이터를 F4 셀부터 출력한다.

 

사용된 주요 함수 및 메서드 설명

1. ArrayToText 메서드
   • 엑셀의 범위를 텍스트 배열로 변환한다.
   • VBA에서 배열을 쉽게 다룰 수 있도록 도와준다.
2. Formula2 메서드
   • 엑셀 365에서 지원되는 동적 배열 수식을 VBA를 통해 설정할 때 사용한다.
   • 기존 Formula 메서드와 달리, 배열 수식을 설정할 수 있다.
3. CHOOSEROWS 함수
   • 주어진 배열에서 특정 행을 선택해 반환한다.
   • 성명과 매칭된 점수를 가져올 때 사용된다.
4. XMATCH 함수
   • 배열에서 특정 값의 위치(인덱스)를 반환한다.
   • 성명 목록을 다른 범위에서 매칭할 때 유용하다.

 

 

엑셀 365의 LET, REDUCE, VLOOKUP 함수로 자동화된 데이터 조회 및 테이블 생성

 

---

코드와 주석

---

코드 설명

1. rngAll
   →rngAll, "Q16:Q23 범위의 데이터를 참조하는 변수. VLOOKUP에서 조회할 키 값들이 포함됨.",
   • Q16범위의 데이터가 조회할 **키 값(예: 학생 이름, 코드)**을 포함한다.
   • REDUCE 함수에서 이 범위를 반복적으로 순회하며 VLOOKUP을 수행한다.
2. tbl
   →tbl, "K4:N23 범위의 테이블 데이터를 참조하는 변수. VLOOKUP 함수가 이 범위에서 데이터를 조회함.",
   • K4범위의 테이블에서 **조회할 데이터(예: 국어, 영어, 수학 점수)**가 포함된다.
   • VLOOKUP 함수는 이 범위에서 매칭된 데이터를 가져와 테이블로 출력한다.
3. rst
   →rst, "REDUCE 함수의 결과를 저장하는 변수. 각 키 값에 대해 VLOOKUP 결과를 누적하여 테이블 형태로 만듦.",
   • REDUCE 함수는 rngAll의 각 키 값에 대해 VLOOKUP 결과를 누적한다.
   • VSTACK 함수는 누적된 데이터를 세로로 쌓아 테이블 형태로 만든다.
4. DROP
   →DROP, "REDUCE 결과의 첫 번째 빈 행을 삭제하고 최종 결과를 반환하는 함수.",
   • REDUCE 함수는 빈 문자열로 시작하기 때문에 첫 번째 행이 비어 있다.
   • DROP 함수를 사용해 첫 번째 빈 행을 삭제하고 최종 테이블을 반환한다.

---

사용된 주요 함수 설명

1. LET 함수
   • LET 함수는 변수 선언과 재사용이 가능해 복잡한 수식을 간단하게 관리할 수 있다.
2. REDUCE 함수
   • REDUCE 함수는 초기값과 배열의 각 요소를 순회하며 누적된 결과를 계산한다.
3. VLOOKUP 함수
   • VLOOKUP 함수는 키 값에 해당하는 행을 테이블에서 조회한다.
4. VSTACK 함수
   • VSTACK 함수는 여러 배열을 세로로 쌓아 하나의 배열로 반환한다.
5. DROP 함수
   • DROP 함수는 배열의 특정 행이나 열을 제거한 새로운 배열을 반환한다.

---

 

동작 과정 정리

1. **Q16범위의 각 키 값(예: 학생 이름)**에 대해 REDUCE 함수가 반복 실행된다.
2. 각 키 값이 VLOOKUP 함수로 K4범위에서 조회된다.
3. 조회된 결과는 VSTACK 함수로 누적되어 세로로 쌓인다.
4. DROP 함수가 첫 번째 빈 행을 제거해 최종 테이블이 출력된다.

---

결론

이 코드는 엑셀 365의 동적 배열 함수를 활용해 여러 키 값에 대한 데이터를 조회하고, 테이블 형태로 정리하는 방법을 보여준다. VLOOKUP을 통해 데이터를 효율적으로 조회하고, REDUCE와 VSTACK으로 결과를 누적해 테이블을 자동으로 생성할 수 있다.

 

기초방94.xlsm

0.04MB