[엑셀/VBA] 엑셀 Database SQL 사용하기 1편 - ADODB.Recordset

 

  엑셀과 데이터베이스를 연동하기 위해 필요한 사용법을 정리. 먼저 기본적인 사용법을 정리하기 위해 임의의 데이터베이스(엑셀 파일)을 두고 VBA에 SQL 쿼리를 추가하여 데이터를 불러오는 코드를 작성하였다. 이번 편에서는 다루는 코드는 기능적으로는 이전에 포스팅했던 Getobject 함수를 사용한 내용과 동일하다. 

참고링크 : 2021.08.19 - [코딩/엑셀,VBA] - [엑셀/VBA] 엑셀 다른 파일에서 데이터 가져오기 : Getobject 함수 사용하기

 

  이번 포스팅과 차이점은 이전 포스팅은 Getobject를 통해 파일에 접근하여 데이터를 복사해서 가져왔다면, 이번 편은 가져올 데이터를 데이터베이스 취급하고 다른 파일의 엑셀 데이터를 SQL 쿼리로 가져온다는 것이다.

 

  참고로, 구글링을 해보니 엑셀 VBA와 Access, Oracle과 같은 DB를 연동하여 사용할 수 있다. 엑셀파일을 데이터베이스로 사용할 경우 동시 접근이 불가하기 때문에 다량의 사용자가 이용할 경우 다른 데이터베이스 시스템을 사용하는게 좋다고 한다.

 

  이번 test 코드에 대한 내용을 먼저 아래와 같이 정리해보았다.

 

 

Concept1. 데이터베이스가 되는 엑셀파일 "DB.xlsx", 실제 VBA를 통해 데이터를 불러올 VBA 파일 "test.xlsx"이 있다.

 

 

Concept2. 데이터베이스가 되는 "DB.xlsx"파일의 내용은 아래와 같다.

 

 

  

1. VBA Database 사용을 위한 환경 설정

 

-. VBA에서 Database를 사용하려면 아래와 같이 개발환경 설정을 해준다. 엑셀에서 Alt+F11 또는 VBA 개발메뉴를 선택해서 도구-참조 선택.

 

 

 

-. 그 다음, 아래 매뉴와 같이 "Microsoft ActiveX Data Objects 2.8 Library"를 선택한다. 아래 참조한 Library를 "ADODB"라고 한다. 내가 참고한 자료들에 따르면 ADODB는 호환성문제로 버전을 2.7, 2.8, 6.1을 참조로 하는게 조다고 한다. 같이 선택시 호환성 충돌이 일어나므로 1개만 선택.

 

 

 

2. VBA 코드 작성

 

1) 변수 선언

 

 

<코드>

  Dim rs As New ADODB.RecordSet #ADODB RecordSet 사용
  Dim strConn As String #커넥션 스트링 변수
  Dim strSQL As String #SQL 쿼리 작성을 위한 변수
  Dim strPath As String #불러올 파일 경로
  Dim FileName As String #파일명

 

-. 변수 중 rs는 ADODB의 RecordSet을 사용하기 위한 변수이다. 보통 우리가 엑셀에서 입력한 DB의 한 행 data를 Record라고 한다. 이 Record가 모인 집합을 RecordSet이라 부른. 참고로 열은 데이터베이스에서 Field라고 한다.

 

-. "1. VBA database 사용을 위한 환경 설정"에서 ADODB를 정상적으로 참조에 추가하였으면 아래와 같이 자동완성되어 보여진다.

 

 

 

 

2) 파일 경로 및 커넥션 스트링(Connection String) 작성

 

 

<코드>

    strPath = "C:\Users\Desktop\VBA CODE\Project11 - 엑셀 DB 및 매크로\" #파일경로
    FileName = "DB.xlsx" #파일명
    
    #커넥션 스트링(엑셀을 데이터베이스로 사용하기 위해 작성해야 하는 구문)
    strConn = "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=" & strPath & FileName & ";" & "Extended Properties=Excel 12.0;"

 

-. VBA에서 DB를 사용하기 위해선 커넥션 스트링이라는 구문을 작성해야 한다. 위 코드중 strConn이 그 부분이다. 커넥션 스트링을 DB에 접근하기 위한 "열쇠"같은 개념이다. 위 구문은 엑셀 데이터베이스를 DB로 접근 하기 위해 설정한 구문이다. 만약, Oracle, Access 등 다른 DB를 사용할 경우 당연히 커넥션 스트링 구문은 달라진다.

 

-. 커넥션 스트링 구문 작성시 DB 파일경로를 포함시켜주어야 한다. 위에서 확인해보면 알겠지만 strPath & FileName이 DataSource에 추가되어 있다.

 

 

3) SQL 쿼리 작성

 

  SQL 쿼리는 사실 이전 파이썬에서 사용했던 쿼리문들이랑 비슷하다. DB를 공부해보니 SQL 쿼리 자체는 어떤 언어에서 쓰든 유사한 것 같다. 이전에 파이썬의 SQlite에 대해 정리한 내용이 있으니 참고하면 좋을 것 같다. SQL 쿼리문에 대해서는 다른 엑셀 VBA 포스팅에서 다시 정리해보도록 하겠다.

 

---

※ Python SQLite3 사용법 참고

2021.03.13 - [코딩/DB] - SQLite3 - Table Data 삽입 정리(INSERT)

2021.03.15 - [코딩/DB] - SQLite3 - Table Data 조회 방법 정리(SELECT)

2021.03.18 - [코딩/DB] - SQLite3 - Table Data 수정 및 삭제 정리(UPDATE, DELETE)

---

 

<코드>

#불러올 파일에서 "DB"라는 시트에서 모든 데이터를 조회하는 쿼리구문
strSQL = "SELECT * FROM [DB$]"
    
#SQL 명단 가져올건데 strConn이라는 커넥션 스트링 사용하자.
rs.Open strSQL, strConn

 

-. "SELECT * FROM [DB$]"

  위 쿼리의 의미는 "DB"라는 시트에서 모든 데이터를 조회한다라는 의미이다. 주의할 점은 [DB$] 와 같이 시트명을 설정해주어야 한다. 중괄호 및 "$"를 넣어줘야함을 기억하자.

 

-. rs.Open strSQL, strConn

  rs는 처음 변수 선언시 ADODB.RecordSet으로 선언했던 객체이다. 위 구문의 의미는 커넥션 스트링인 strConn으로 정해진 엑셀 데이터베이스에 연결해서 위 커리 구문 strSQL을 실행시키겠다는 의미이다.

 

 

4) 복사 및 db 연결 종료

 

 

<코드>

 #EOF는 데이터의 끝을 의미한다.
    If rs.EOF Then
        MsgBox "데이터 없음"
        rs.Close #rs 닫기
        Set rs = Nothing #rs 초기화
        Exit Sub
    
    Else
        Sheet1.Range("A1").CurrentRegion.Offset(1).Clear
        Sheet1.Range("A1").CopyFromRecordset rs
    End If

    rs.Close #rs 닫기
    Set rs = Nothing #rs 초기화

 

-. 데이터가 없다면 db 연결을 닫는다. 데이터가 있다면 Sheet1.Range("A1")에 데이터(RecordSet)를 복사해서 붙여넣는다는 의미이다.

 

 

3. 전체 코드 및 결과

 

<전체 코드>

Option Explicit

Sub 데이터불러오기()
    
    Dim rs As New ADODB.RecordSet 'ADODB RecordSet 사용
    Dim strConn As String '커넥션 스트링 변수
    Dim strSQL As String 'SQL 쿼리 작성을 위한 변수
    Dim strPath As String '불러올 파일 경로
    Dim FileName As String '파일명
    
    
    strPath = "C:\Users\Desktop\VBA CODE\Project11 - 엑셀 DB 및 매크로\" '파일경로
    FileName = "DB.xlsx" '파일명
    
    '커넥션 스트링(엑셀을 데이터베이스로 사용하기 위해 작성해야 하는 구문)
    strConn = "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=" & strPath & FileName & ";" & "Extended Properties=Excel 12.0;"
    
    strSQL = "SELECT * FROM [DB$]"
    
    'SQL 명단 가져올건데 strConn이라는 커넥션 스트링 사용
    rs.Open strSQL, strConn
    
    'EOF는 데이터의 끝을 의미한다.
    If rs.EOF Then
        MsgBox "데이터 없음"
        rs.Close
        Set rs = Nothing
        Exit Sub
    
    Else
        Sheet1.Range("A1").CurrentRegion.Offset(1).Clear
        Sheet1.Range("A1").CopyFromRecordset rs
    End If

    rs.Close
    Set rs = Nothing
    
    
End Sub

 

<결과>