[기초방] VBA 100제 #95-1 [열을 역순으로 복사 feat. ADODB]

---

1. 코드에 한글 주석 추가 및 설명 📝

vba

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

Sub Haja_Guid_ADODB()

    ' 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  ' 이후 코드에서 정상적인 오류 처리 복구

    ' 기초방95 서브 프로시저 호출 (데이터 조회 및 출력)
    Call 기초방95

End Sub

Sub 기초방95()

    ' ADODB 레코드셋 객체 생성 (데이터 조회용)
    Dim Rs As New ADODB.Recordset
    
    ' 파일 경로, SQL 쿼리, 연결 문자열 선언
    Dim strPath$, strSQL$, strConn$
    
    ' 데이터 범위와 반복문에 사용할 변수들 선언
    Dim rngAll As Range, rngA As Range, rngX As Range
    Dim i&  ' 정수형 변수
    
    ' 현재 열려 있는 엑셀 파일의 전체 경로를 strPath에 저장
    strPath = ThisWorkbook.FullName
    
    ' 성명 목록이 있는 E4:E11 범위를 rngAll에 저장
    Set rngAll = [e4:e11]
    
    ' 출력 시작 셀을 X3으로 설정 (데이터가 여기에 기록됨)
    Set rngX = [X3]
    
    ' 엑셀 파일을 데이터베이스로 사용할 수 있도록 ADODB 연결 문자열 생성
    strConn = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
              "Data Source=" & strPath & ";" & _
              "Extended Properties=Excel 12.0;"
    
    ' SQL 쿼리 작성: 기초방95 시트의 B3:K23 범위에서 데이터를 열 순서대로 조회
    strSQL = " SELECT 열10,열9,열8,열7,열6,열5,열4,열3,열2,열1" & _
             " FROM [기초방95$B3:K23] "

    ' SQL 쿼리를 실행하고 결과를 Recordset 객체에 저장
    Rs.Open strSQL, strConn

    ' 열 제목을 X3 셀에 출력
    rngX.Resize(1, 10) = Array("열10", "열9", "열8", "열7", "열6", "열5", "열4", "열3", "열2", "열1")

    ' 만약 Recordset에 데이터가 있을 경우
    If Not Rs.EOF Then
        ' 출력 위치를 한 행 아래로 이동
        Set rngX = rngX.Offset(1)
        
        ' Recordset의 데이터를 복사하여 엑셀에 출력
        rngX.CopyFromRecordset Rs
    End If

    ' 작업이 끝난 후 Recordset을 닫음
    Rs.Close

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

End Sub

---

2. 코드의 구간별 동작 설명 📋

Haja_Guid_ADODB 프로시저

1. ADODB GUID 참조 추가:
   • ThisWorkbook.VBProject.References.AddFromGuid를 사용해 ADODB 라이브러리 참조를 추가한다. GUID는 ADODB 라이브러리의 고유 식별자다.
   • On Error Resume Next로 참조 추가 실패 시에도 오류를 무시하도록 처리했다.
2. 기초방95 프로시저 호출:
   • 데이터베이스 접근 및 엑셀 시트에 데이터 조회 결과를 출력하는 서브 프로시저 기초방95를 호출한다.

---

기초방95 프로시저

1. ADODB Recordset 객체 생성:
   • SQL 쿼리를 통해 조회한 데이터를 저장하는 용도로 ADODB.Recordset 객체를 사용한다.
2. 경로 및 데이터 범위 설정:
   • ThisWorkbook.FullName을 사용해 현재 엑셀 파일의 경로를 가져온다.
   • 성명이 있는 범위(E4:E11)와 데이터 출력 위치(X3)를 각각 설정한다.
3. ADODB 연결 문자열 생성:
   • 엑셀 파일을 데이터베이스로 사용할 수 있도록 ACE OLEDB 공급자를 사용해 연결 문자열을 생성한다.
4. SQL 쿼리 실행:
   • SQL 쿼리를 사용해 기초방95 시트의 B3범위 데이터를 열 순서대로 조회한다.
   • 쿼리 결과를 Recordset에 저장한다.
5. 데이터 출력:
   • 데이터가 있으면 CopyFromRecordset 메서드를 사용해 조회 결과를 엑셀 시트에 복사한다.
   • 열 제목을 X3 셀에 기록한 후, 데이터를 그 아래에 출력한다.
6. 메모리 정리:
   • 사용한 Recordset 객체를 닫고 메모리를 해제한다.

3. 확장 기능 및 실무 응용 🚀

1. 조건부 조회 추가
   • 성명 목록을 순회하며 각 성명에 대해 조건부 SQL 쿼리를 실행할 수 있다.
      
     strSQL = "SELECT * FROM [기초방95$B3:K23] WHERE 성명 = '" & rngA.Value & "'"
2. 다중 시트 데이터 조회
   • 여러 시트에서 데이터를 조회하고 결과를 통합 출력하도록 수정할 수 있다.
3. 동적 범위 처리
   • E4:E11 대신 동적으로 범위를 설정하여 변동되는 데이터를 처리할 수 있다.
     Set rngAll = Sheet1.Range("E4", Sheet1.Cells(Sheet1.Rows.Count, "E").End(xlUp))

---

5. 오류 해결 및 디버깅 팁 🛠️

1. ADODB 참조 오류
   • ADODB GUID를 찾지 못하는 경우, VBA 환경설정에서 Microsoft ActiveX Data Objects Library를 수동으로 참조 추가해야 한다.
2. SQL 쿼리 오류
   • 쿼리 구문이 잘못된 경우 데이터 조회에 실패할 수 있다. 이때 Debug.Print strSQL로 쿼리를 출력해 확인한다.
3. OLEDB 드라이버 문제
   • ACE OLEDB 드라이버가 설치되지 않은 경우 드라이버를 설치하거나 다른 방법을 고려해야 한다.

기초방95-1.xlsm

0.03MB