강좌 > 엑셀에서 Database사용하기 > 3강 - VBA로 엑셀Sheet에 연결하기(ADO를 이용하여 연결, 조회)

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

2021-05-03 / 24 댓글

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

1. 모듈 삽입

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

vba_query01

 

2. VBA코드 작성

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

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. Data출력

사원정보 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사용하기 강좌 목록 ]

 

엑셀웍스 책 출간 안내

그동안 엑셀웍스에 올린 '엑셀 강좌', '함수 사용법', 여러 '실무 예제'에서 핵심만 뽑아 '된다! 엑셀 수식 & 함수' 책으로 출간하였습니다.

 자세히 보기

온라인 구매처: 예스24   교보문고   알라딘 

“3강 - VBA로 엑셀Sheet에 연결하기(ADO를 이용하여 연결, 조회)”의 24개의 댓글

  2. 진해식

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

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

    어떻게 해야 되나요?

    답글
    1. Admin

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

      답글
  3. 김영학

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

    답글
    1. Admin

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

      답글
    1. Admin

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

      답글
  6. 우너석

    ADO로 다른폴더의 엑셀에서 데이터를 취해오지는 못하는지요...

    찾아봐도 뚜렷한 해법이 나타나질않네요...

    NAS내의 다른폴더에서 데이터값을 가지고오려고합니다.

    해당파일은 읽기권한밖에없어서 읽기전용으로 열리고있는 파일입니다...

    답글
    1. Admin

      안녕하세요. 파일을 네트워크드라이브에 놓고 사용해 보면 오류가 발생하네요. 정확히 원인을 알 수는 없지만 로컬드라이브가 아닌 네트워크드라이브에서는 지원이 안되는 것 같습니다.
      그럼...

      답글
      1. 우너석

        댓글감사합니다 ^^
        저도 해보다 안되서 포기하고는 액세스로 디비구축중입니다 ㅠ
        데이터베이스관련 올려주신내용들 몇번씩이나 곱씹으며 공부하고있습니다.
        함수 쫌 할줄알던 청년이 VBA 독학하다하다 이젠 회사DB구축해보려고 발악하고 있네요.
        좋은내용 올려주셔서 감사드리고 댓글도 달아주시니 힘이됩니다

        답글
        1. 킴디

          네트워크 경로를 "E\" 등 이런경로 말고 절대경로 "\\vn_hs01\test\" 등으로 등록하니 네트워크에서도 사용이 가능하네요
          저도 여기 엑셀웍스 도움으로 많은 공부가 되고 있습니다.

          답글
          1. 익명

            저같은 경우는 회사에서 NAS 內 권한이 없어 읽기전용으로 열리는 파일을 불러오고자했어요~ㅎ

            '다른 사용자가 단독 사용 모드로 파일을 이미 열었거나 데이터를 보고 쓸 수 있는 권한이 필요합니다.'

            라는 메세지가 떠서 오류가 나더라구요...

            저는 그래서 Temp폴더만들어서 거기에 복사해온다음에 ADO 활용해서 데이터 가지고오고 있습니다 ㅋㅋ

  7. 인빈서블

    와!!! 실행해보고 깜놀.....나 자체를 출력하고 사라지게 하다니....반전.ㅋㅋ
    정말 좋은 내용 잘 배웠씁니다. 진심 궁금했는데 여기저기 돌아다니다 겨우 여기서 알게 되었네요. sql문 활용 기본...

    답글
  9. na9ee

    안녕하세요 올려주신 강의 보면서 따라하고 있는데
    단추버튼을 생성하고 클릭했을때 가공된 값이 나오지 않고

    '9'런타임 오류가 발생하였습니다:
    아래 첨자 사용이 잘못되었습니다.

    라는 오류메시지가 뜨는데 어떻게 해야 될까요 ㅠ,.ㅜ

    답글
  10. Junhee

    질문이 있습니다. 그럼 oracle db 에서 데이터를 가져오는 vba 를 작성 한 후, 그 파일을 다른 PC에서 실행 해 보았습니ㅏ. 그러니 "3706 런타임 오류가 발생하였습니다. 공급자를 찾을수 없습니다. 올바르게 설치되지 않았을수 있습니다. " 라는 메세지와 함께 실행되지 않았습니다. 혹시 이럴땐, 그 상대 PC에도 Oracle client 를 설치해야하나요? 아님 다른 방법이 있을까요? OC를 설치 해야 한다면 어쩔수 없지만, 이게 너무 무거워서 되도록 피하고 싶어서요. 이 파일을 공유 하고 싶은데, 혹시 방법에 대해 조언주시면 정말정말 감사드리겠습니다. 좋은 하루 되세요

    답글
    1. Admin

      엑셀에서 ADO를 이용하여 Oracle에 연결하려면 엑셀 파일이 있는 PC에는 Oracle client를 설치해야 합니다. 다른 방법은 WEB방식으로 해야 하는데 Oracle database 앞단에 WAS가 따로 있어야 합니다. 아키텍처와 개발이 복잡해서 개인이 혼자서 하기에는 무리가 있을 것 같고 전문 개발업체에 의뢰를 하셔야 할 것 같습니다.

      답글
      1. junhee

        운영자님, 답글 정말 감사합니다. 그럼 다른 방법으로 찾아 봐야 겠네요. 파이썬쪽을 이용해서요. 아무튼 다시 한번 답변 주셔서 감사드리고, 항상 하시는 일 번창하시길 바래요~ 감사합니다.

        답글

댓글 남기기

Scroll to Top