3강 – VBA로 엑셀Sheet에 연결하기(ADO를 이용하여 연결, 조회)

공개됨 글쓴이 Admin댓글 9개

2강에서는 MS Query를 이용하여 데이터베이스에 연결해 보았다. 이번에는 VBA를 이용하여 연결해 보자. VBA를 이용하면 Microsoft Query를 이용하지 않고도 데이터베이스에 연결하고 Data를 조회하고 가공할 수 있다.
아래와 같이 따라 해보자. 이번 강좌에 첨부된 파일은 완성된 결과파일이므로 학습의 효과를 위해서 2강에 첨부된 파일을 가지고 따라 해 보는 것이 좋을 것 같다.

1) 2강 자료를 연 상태에서 Alt+F11을 눌러서 VBA모드로 이동한다. 아래 그림과 같이 삽입>모듈을 클릭한다.

vba_query01

 

2) 아래의 코드를 모듈에 붙여 넣는다.

Option Explicit
'//
'// https://xlworks.net
'// 2017.7.19
'// 엑셀에서 Database사용하기 강좌(3강 실습자료)
'//

Sub test()

' DB사용시 주의 사항 :
' - 64bit Office(Excel)에서 DAO 3.6 Library를 더 이상 지원하지 않으므로 DAO대신 ABODB를 사용
' - 사전에 VBA편집기의 도구(Tools) => 참조(References)에서 "Microsoft ActiveX Data Object 2.8 Library" 선택

 Dim rs As New ADODB.Recordset
 Dim strSQL As String
 Dim strConn As String
 Dim i As Integer
 
 
 '기존 조회내용 지우기
 Sheets("출력").Select
 Rows("1:1").Select
 Range(Selection, Selection.End(xlDown)).Select
 Selection.Delete Shift:=xlUp
 
 '조회할SQL을 만들어서 String변수에 넣는다.
 strSQL = "SELECT * FROM [사원정보$] " & _
          " WHERE 부서 = '영업팀'"
 
 
 'Excel을 Database로 사용
 strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.Path & "\" & ActiveWorkbook.Name & ";" & "Extended Properties=Excel 12.0;"
 
 rs.Open strSQL, strConn, adOpenForwardOnly, adLockReadOnly, adCmdText
 
 If rs.EOF Then
     MsgBox "조회조건에 해당하는 자료가 없습니다."
 Else
     '타이틀을 표시한다.
     For i = 1 To rs.Fields.Count
         Cells(1, i).Value = rs.Fields(i - 1).Name
     Next

     With ActiveSheet
     '조회한 결과집합(rs)을 "출력"Sheet의 A2지점을 꼭지점으로 해서 출력한다.
         .Range("A2").CopyFromRecordset rs
     End With
 End If
 
 
 rs.Close
 Set rs = Nothing
 
End Sub

 

3) 코드가 제대로 작성되었는지 확인하기 위해서 아래와 같이 컴파일을 한다. 제대로 입력했는데도 오류가 날 것인데… 엑셀에서 데이터베이스 작업을 하기 위해서 라이브러리가 필요한데 이 엑셀파일에 추가가 안되었다는 이야기이다.

vba_query02

vba_query03

 

4) 아래 순서대로 라이브러리를 추가하고 다시 컴파일을 해 보면 에러가 나지 않을 것이다.

vba_query04

vba_query05

5) 이제 프로그램 코드는 완성되었고 엑셀 Sheet에 버튼을 달아서 완성된 프로그램을 실행해 보자. 먼저 “사원정보”Sheet를 선택한 후  개발도구 메뉴에서 삽입>양식컨트롤에서 단추를 선택하고 마우스 커서를 엑셀 시트에 놓고 클릭하면 버튼이 생긴다.

vba_query06

버튼이 만들어 질때 다음과 같은 화면이 나타나면 좀 전에 작성한 프로시저의 이름(test)를 선택한다. 버튼을 누르면 test라는 프로시저(프로그램 기능이라고 생각하면 된다)가 실행되도록 하는 것이다.

vba_query08

* 사용자에 따라서는 “개발도구 메뉴”가 보이지 않을 수도 있다. 이 때는 엑셀 옵션에서 다음과 같이 “개발도구”를 클릭상태로 설정한다(오른쪽 메뉴에서 밑에서 세번째).

vba_query07

6) 사원정보 Sheet에 만들어진 버튼을 클릭하면 “출력”Sheet에 아래와 같이 Data가 출력된다. SQL의 조건대로 Data를 가져온 것이다.

vba_query09


첨부(실습용 파일) : 
3강_VBA를_이용하여DB연결하기.xlsm

이번 강좌에서는 단순히 Data를 가져오기만 했다. 다음 강좌부터는 VBA를 이용하여 Data 수정,삭제 등의 작업을 해 보기로 한다.

주의 : 엑셀파일은 Database시스템이 아니므로 여러 명이 동시에 접속해서 사용하는 환경이라면 문제가 될 수 있다. 몇명(4~5명 이내) 정도가 동시에 사용하는 수준이라면 Access DB를 사용하고 그 보다 많은 경우라면 Database시스템(MySQL, Maria DB, SQL Server, Oracle등)을 사용하는 것을 권장한다.
[ 엑셀에서 Database사용하기 강좌 목록 ]

 

3강 – VBA로 엑셀Sheet에 연결하기(ADO를 이용하여 연결, 조회)에 1개의 응답

  1. 안녕하세요. 첨부된 파일을 사용하지 않고 새로 만든 파일에서 스크립트를 복사하여 붙여넣고
    가져오기 단추를 눌렀을때 아래부분이 오류로 뜹니다.

    rs.Open strSQL, strConn, adOpenForwardOnly, adLockReadOnly, adCmdText

    어떻게 해야 되나요?

    • 안녕하세요. 첨부된 파일에 보시면 “사원정보” 시트가 있습니다. 이 “사원정보” 시트에서 자료를 가져오는 예제이므로 새로 파일을 만들어서 작업하실 경우 “사원정보” 시트가 있어야 합니다. 시트명, 시트안에 있는 타이틀, 필드별로 들어
      있는 자료의 형식도 일치해야 합니다. “사원정보”시트는 database table로 인식하기때문에 그렇습니다.
      감사합니다.

  2. 구글링하다 방문하게 되었습니다. 포스팅 너무 도움이 됩니다. 고맙습니다.
    질문이 하나 있습니다. 예를들어
    급여 좌측에 2칸을 띄우고 ‘인사고가’라는 Field를 구성하면
    부서 이름 사번 급여 F5 F6 인사고가 로 구성이 됩니다.
    이름정의를 하고 해도 ‘rs.Fields.Count’ 수치가 4 로 나오지 않고 7로 나옵니다.
    특정 Range만 테이블로 구성을 해서 ADO를 사용하고 싶은데 방법이 없을까요?

    • 안녕하세요. 중간에 빈 필드가 있으면 내부적으로 필드이름을 자동으로 F5(다섯번째 필드라는 의미), F6의 형태로 인식합니다. 5번째,6번째 필드를 제외하고 이름정의를 할 수 있으나 ADO에서 인식이 안되네요 ㅠ…

    • 안녕하세요. 반드시 시트이름이 “사원정보”일 필요는 없습니다. 이름을 다른 것으로 바꾸면 프로그램소스코드에서도 참조하는 이름을 바꾸면 됩니다.
      현재는 SQL 부분에 [사원정보$] 이렇게 되어 있는데 만약, 시트이름을 “직원정보”라고 바꾸면 프로그램에서도 [직원정보$]로 바꾸면 됩니다.

댓글 남기기

이메일은 공개되지 않습니다.