VBA 코딩 패턴: DB Query 결과 엑셀로 가져오기

DB Query 결과 엑셀로 가져오기 를 구현하는 VBA 코딩 패턴을 소개한다.

관련글: VBA 코딩 패턴: ADO DB Connection String 생성

1. 엑셀 화면과 동작 방식

1.1. 엑셀 화면

“B1″셀에 DB에 연결하기 위한 연결 문자열(Connection String)을 입력하고, “B2″셀에 데이터를 추출하기 위한 SQL Query를 입력하도록 구성한다.

“개발도구 > 삽입 > ActiveX 컨트롤 > 명령단추”를 클릭하여 버튼을 추가하고, 이름을 “Query실행”으로 변경한다.

예시 화면은 다음과 같다.

1.2. DB Query 결과 엑셀로 가져오기 동작 방식

위 그림과 같은 방식으로 동작한다.

1. 엑셀에 추가한 “Query 실행” 버튼 클릭
2. VBA 코드에서 DB 연결
3. 연결한 DB에서 Query 실행
4. DB에서 실행한 Query 결과 반환
5. 엑셀 시트에 Query 결과 표시

2. DB 접속을 위한 Provider(공급자) 선택

위 동작방식의 “2. VBA 코드에서 DB 연결”에서 사용하는 공급자이다. ADO.Connection 개체의 Provider 속성으로 지정하거나, ConnectionString으로 지정할 수 있다.

보통은 ConnectionString으로 지정하고, 그 예는 다음과 같다.

• MS OLE DB Provider for ODBC Drivers
  • Provider=MSDASQL.1; Password=[비밀번호]; Persist Security Info=True; User ID=[UserID]; Data Source=[ODBC 제어판에서 설정한 DSN]; Initial Catalog=[DB명]
• MS OLE DB for SQL Server (SQL Server 인증)
  • Provider=SQLOLEDB.1; Password=[비밀번호]; Persist Security Info=True; User ID=[UserID]; Initial Catalog=[DB명];Data Source=[서버주소(IP 또는 host명)]
• MS OLE DB for SQL Server (Windows 인증)
  • Provider=SQLOLEDB.1; Password=[비밀번호]; Integrated Security=SSPI; Persist Security Info=False; User ID=[UserID]; Initial Catalog=[DB명];Data Source=[서버주소(IP 또는 host명)]
• Oracle Provider for OLE DB
  • Provider=OraOLEDB.Oracle.1;Password=[비밀번호]; Persist Security Info=True; User ID=[UserID]; Data Source=[서버주소(IP 또는 host명)]

PC에 적합한 Provider(공급자)가 설치되어 있다면, Oracle, MS SQL Server, MySQL, MariaDB, PostgreSQL 등의 RDBMS 서버에 접속할 수 있고, MS Access, SQLite 등의 local RDBMS에도 접속할 수 있다.

ConnectionString은 다음과 같은 방법으로 생성할 수 있다.

ConnectionString 생성 방법 1. UDL 파일 이용

UDL(Universal Data Link) 파일은 Microsoft Data Access Components(MDAC)에 포함된 개념이다.

참고: UDL(유니버설 데이터 링크) 구성 – OLE DB Driver for SQL Server | Microsoft Docs

다음 절차로 실행한다.

1. 바탕화면에서 마우스 우측 버튼 클릭 > 새로 만들기 > 텍스트 문서
2. 파일명을 test.udl로 변경하고 엔터
3. test.udl 파일을 더블클릭하여 열고 “공급자” 탭에서 공급자 선택후 “연결” 탭에서 서버, 사용자 이름, 암호, 데이터베이스 등을 입력후 “연결 테스트”한 다음 “확인”하여 저장
4. 저장한 test.udl 파일을 편집기(메모장 등)로 열어 보면 ConnectionString을 얻을 수 있음

이 방법은 Windows OS의 32bit, 64bit 플랫폼에 따라 Provider가 표시된다. 즉, 64bit OS에서는 64bit Provider만 표시된다. 64bit OS에서 32bit Provider를 표시할 수 있는 방법은 별도의 글로 포스팅하겠다.

ConnectionString 생성 방법 2. VBA 코드

이전 글 VBA 코딩 패턴: ADO DB Connection String 생성 에서 상세하게 설명해 두었다. 요약하면 다음과 같다.

• Microsoft ActiveX Data Objects 2.8 Library 참조 추가
• Microsoft OLE DB Service Component 1.0 Type Library 참조 추가
• 다음 코드로 ConnectionString을 생성

Private Sub btnBuildConnectionString_Click()
On Error GoTo 0

    Dim cn As ADODB.Connection, MSDASCObj As MSDASC.DataLinks, oCurrentRange As Range
    Dim eOrgXlEnableCancelKey As XlEnableCancelKey

    Set oCurrentRange = ActiveCell

    Set MSDASCObj = New MSDASC.DataLinks

    eOrgXlEnableCancelKey = Application.EnableCancelKey
    Application.EnableCancelKey = xlDisabled
    Set cn = New ADODB.Connection
    cn.ConnectionString = oCurrentRange
    If MSDASCObj.PromptEdit(cn) = True Then
        oCurrentRange = cn.ConnectionString
    End If

    Set cn = Nothing
    Set MSDASCObj = Nothing

    Application.EnableCancelKey = eOrgXlEnableCancelKey
End Sub

Provider 관련 주의사항

사용하려는 Provider와 Excel의 32bit, 64bit 플랫폼이 같아야 한다. 32bit Excel을 사용하고 있는 경우는 32bit Provider를 사용해야 하고, 63bit Excel을 사용하고 있는 경우는 64bit Provider를 사용해야 한다.

64bit Windows OS와 32bit Excel을 사용하는 경우에 첫 번째 방법으로 생성한 ConnectionString으로 Database연결하려고 할 때 “공급자를 찾을 수 없습니다”라는 오류가 발생할 수 있다.

이 경우는 Excel과 Provider의 플랫폼이 동일한지 확인하고 적합한 Provider를 사용하면 해결된다.

3. DB Query 결과 엑셀로 가져오기 구현

3.1. 참조 추가

VBA 코드편집기 메뉴에서 “도구 > 참조추가”를 실행하여, “Microsoft ActiveX Data Objects 2.8 Library”를 참조 추가한다.

3.2. DB Query 결과 엑셀로 가져오기 VBA 코드

엑셀 시트에 추가한 버튼의 클릭 이벤트 프로시져에 다음과 같은 코드를 작성한다.

Private Sub cmd_RunQuery_Click()
    Dim sSQL As String
    Dim oCon As ADODB.Connection, oRS As ADODB.Recordset
    Dim sConnectionString As String

    sConnectionString = Range("B1").Value2
    sSQL = Range("B2").Value2

    Set oCon = New ADODB.Connection
    Set oRS = New ADODB.Recordset
    oCon.ConnectionTimeout = 10
    oCon.Open sConnectionString
    oCon.CommandTimeout = 60

    oRS.Open sSQL, oCon, adOpenForwardOnly, adLockReadOnly
    
    Dim oSht As Worksheet, lColIdx As Long
    Set oSht = Worksheets.Add(After:=Me)
    For lColIdx = 0 To oRS.Fields.Count - 1
        oSht.Range("A1").Offset(0, lColIdx).Value2 = oRS.Fields(lColIdx).Name
    Next lColIdx
    oSht.Range("A2").CopyFromRecordset oRS

    oRS.Close: Set oRS = Nothing
    oCon.Close: Set oCon = Nothing
End Sub

위 코드에 대한 설명은 다음과 같다.

• 6행: 엑셀 시트의 “B1” cell값을 sConnectionString 변수로 가져온다.
• 7행: 엑셀 시트의 “B2” cell값을 sSQL 변수로 가져온다.
• 12행: ADODB.Connection 개체 oCon에 sConnectionString 값을 전달하여 데이터베이스에 접속한다.
• 15행: 데이터베이스에서 SQL Query를 실행하고 그 결과를 ADODB.Recordset 개체 oRS로 가져온다.
• 18행: SQL Query 실행결과를 표시할 시트 oSht를 생성한다.
• 19-21행: 개체 oRS의 필드(컬럼) 목록을 시트 oSht의 첫 번째 행에 출력한다.
• 22행: oSht 시트의 “A2” 셀에 oRS의 내용(SQL Query 실행결과)을 출력한다.

3.3. 예시 파일 다운로드

아래 github Link에서 위에 설명한 코드가 포함된 예시 엑셀파일을 확인할 수 있다.
파일 Link: github DAToolset/VBACode/VBA 코딩 패턴-DB Query1.xlsm