18강 - VBA로 MS SQL Server에 연결하여 Data처리하기(Stored Procedure로 조회)

18강에서는 VBA로 Microsoft SQL Server DB에 연결하여 Stored Procedure(SP)를 이용하여 조회하는 작업을 해보자.

13강에서 SQL Server를 이용하여 Excel VBA에서 SQL을 직접 작성하여 Data를 조회하는 기능을 구현해보았는데 이번에는 SQL을 작성하는 대신 DB Server에 Stored Procedure(일명 SP)를 만들어 놓고 Data를 조회해 보자.

1. 사전에 확인할 사항

SQL Server로 기능을 구현하기 위해서는 다음 사항을 먼저 확인한다. 제대로 되어 있지 않다면 다양한 에러가 발생하여 시작부터 좌절할 수 있으니.... 미리 확인을 해 둔다.

VBA에서 ADO를 사용하도록 설정 : ADO와 관련된 에러가 나면("사용자 정의형식이 정의되더 있지 않습니다"라고 난다) 3강을 참고하여 ADO(Microsoft ActiveX Data Objects 6.1 Library - 6.1은 버전인데 6.1이 버전이 없을 수도 있다. 없다면 낮은 버전을 선택해도 대부분 문제없이 작동한다)가 참조되도록 설정한다.
ODBC확인 - ODBC에 SQL Server가 있어야 한다. Windows OS를 잘못 설치하지 않았다면 당연히 있을 것이다. 다음 그림과 같이 있는 지 확인해 보자. 제어판>관리도구>데이터원본(ODBC) 를 선택하여 시스템 DSN에서 추가 버튼을 눌러보면 확인할 수 있다. Window OS 버전에 따라 찾는 방법이 약간 다를 수 있다. 아래 그림에서 "SQL Server"라고 되어 있느데 VBA에서 DB연결문자열 만들때 동일하게 적어주어야 한다.
SQL Server Port 번호 확인 - SQL Server는 기본 통신 Port가 1433인데 SQL Server 설치 후 별도로 지정하지 않는다면 기본 Port인 1433으로 지정되어 있다. 그런데 보안상의 이유로 시스템 관리자가 Port번호를 변경할 수 있다. 만약에 Port번호가 1433이 아니라 20100이라면 VBA에서 연결 문자열을 만들때 다음과 같이 서버의 IP주소 다음에 comma를 찍고 Port번호를 적어준다. Port번호가 기본 Port인 1433인 경우에는 적어주지 않아도 된다.
conn.ConnectionString = "Driver={SQL Server};Server=192.168.65.132,20100;Database=test_db;uid=sa;pwd=x1234"

2. Table, Stored Procedure만들기

Users Table에서 Data를 가져올 것이다. Table과 Store Procedure는 다음의 스크립트를 이용해서 만든다.

Table 만들기

CREATE TABLE users (
    deptname varchar(50) NOT NULL,
    username varchar(50) NOT NULL,
    id int NOT NULL  PRIMARY KEY,
    salary float NOT NULL,
    datehired datetime NULL
);

조회에 필요한 데이터는 INSERT SQL을 수행하여 입력하거나 SQL Server Management Studio를 이용하여 각자 입력한다.

Stored Procedure 만들기

CREATE PROCEDURE [dbo].[USP_SELECT_USERS]
@DEPTNAME VARCHAR(50),
@DATEHIRED DATE
AS
SELECT deptname,username,id,salary,datehired
  FROM dbo.users
 WHERE deptname = @DEPTNAME
   AND datehired = @DATEHIRED;

부서명과 입사일을 매개변수로 입력받는 Procedure이다.

3. 엑셀 VBA에서 Stored Procedure를 이용하여 Data조회

다음 그림과 같이 Users Table에서 부서(deptname)가 "영업팀"이고 입사일(datehired)이 "2001-01-01"인 사원만 가져와서 엑셀시트에 조회해 보자

VBA에서 소스입력 : VBA Module에 다음의 소스코드를 입력한다. SQL Server의 Stored Procedure를 이용하여 Data를 가져와서 엑셀 시트에 필드명과 Data를 출력하는 기능이다.

'//Stored procedure로 Data를 가져와서 Excel sheet에 출력하기
Sub selectUsersWithStoredProcedure()

Dim conn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim cmd As New ADODB.Command
Dim i As Long

    '//엑셀 Sheet에서 기존 조회내용 지우기
    Sheets("출력").Select
    Rows("5:5").Select '//5행부터 이전에 조회된 Data가 있으므로 새로 조회 시 전부 지운다.
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Delete shift:=xlUp

    Set conn = New ADODB.Connection
    
    '//연결문자열을 만든다. 다음과 같이 Drive, Server명을 적고, Database명, DB에 로그인을 위한 Id,Password를 적는다.
    '//만약에 Port번호가 1433이 아니라 20100이라면 VBA에서 연결 문자열을 만들때 다음과 같이 서버의 IP주소 다음에 comma를 찍고
    '// Port번호를 적어준다. Port번호가 기본 Port인 1433인 경우에는 적어주지 않아도 된다.
    '// conn.ConnectionString = "Driver={SQL Server};Server=192.168.65.132,20100;Database=test_db;uid=sa;pwd=x1234"
    
    conn.ConnectionString = "Driver={SQL Server};Server=192.168.65.132;Database=test_db;uid=sa;pwd=x1234"
    '//Database Connection을 Open한다.
    conn.Open

    With cmd
        .ActiveConnection = conn
        .CommandType = adCmdStoredProc
        .CommandText = "dbo.USP_SELECT_USERS" '//Stored Procedure명
        .Parameters("@DEPTNAME") = "영업팀" '//매개변수명과 매개변수로 넘길 값을 적어준다.
        .Parameters("@DATEHIRED") = "2001-01-01" '//매개변수명과 매개변수로 넘길 값을 적어준다.
    End With

    Set rs = cmd.Execute

    '//Database에서 Data를 가져와서 Data가 있으면 Excel sheet에 출력한다.
    If rs.EOF Then
        MsgBox "조회조건에 해당하는 자료가 없습니다."
    Else

        '//users Table의 필드명을 가져와서 Excel sheet에 첫행에 표시한다.
        For i = 0 To rs.Fields.Count - 1
          Cells(5, i + 1).Value = rs.Fields(i).Name
        Next

        '//조회한 결과집합(rs)을 "출력"Sheet의 A6지점을 꼭지점으로 해서 출력한다.
        With ActiveSheet
           .Range("A6").CopyFromRecordset rs
        End With

    End If


    conn.Close
    
    Set conn = Nothing
    Set cmd = Nothing
    Set rs = Nothing

End Sub

위의 VBA소스코드를 버튼에 연결하기 : 리본메뉴 > 개발도구 > 디자인모드가 클릭된 상태에서 "출력" 시트에 있는 "Stored Procedure로 Data가져오기" 버튼을 더블클릭하면 VBA편집창이 나타나는데 다음과 같이 코드를 입력한다.
```
Private Sub cmdSelectWithSP_Click()
    selectUsersWithStoredProcedure
End Sub
```

이것으로 18강을 마친다. 구체적인 기능은 첨부의 실습용 파일을 참고한다.

첨부(실습용 엑셀 매크로 파일) :
18강_VBA로 MS SQL Server에 연결하여 Data처리하기(Stored Procedure로 조회).xlsm

[ 엑셀에서 Database사용하기 강좌 목록 ]

엑셀웍스 책 출간 안내

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

자세히 보기

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

“18강 - VBA로 MS SQL Server에 연결하여 Data처리하기(Stored Procedure로 조회)”의 4개의 댓글

진해식
2018-12-01 / 11:32

감사합니다^^

답글
이슬
2019-03-10 / 10:55

정리가 너무 잘되어 있어 함상 참조를 하고 싶어지는 자료 입니다 감사 합니다

답글
박균하
2019-03-28 / 15:37

안녕하세요.
정리가 너무 잘되어 있어서 본문 내용 및 첨부파일 확인하여 엑셀 VBA 작업중에 있는데
아래와 같이 TEST라는 테이블에서 데이터를 조회하여 TEST1이라는 테이블에 데이터 입력후
TEST 테이블에서는 해당 데이터를 삭제할려고 하는데 strSQL1 만 쿼리 실행이 되고
strSQL2는 실행이 안되는데 각 쿼리문마다 conn.Open 하고 conn.Close 해줘야 각각 쿼리가 실행이
되는지 문의드리오니 시간이 되시면 확인 부탁드립니다.

Sub TEST()

Dim conn As ADODB.Connection
Dim affectedCount As Long
Dim requester As String
Dim strSQL1 As String
Dim strSQL2 As String

Set conn = New ADODB.Connection
'//연결문자열을 만든다. 다음과 같이 Drive, Server명을 적고, Database명, DB에 로그인을 위한 Id,Password를 적는다.
conn.ConnectionString = "Driver={SQL Server};Server=172.16.5.10;Database=TEST;uid=sa;pwd=1234"

'//Database Connection Open한다.
conn.Open

strSQL1 = "INSERT INTO TEST1 SELECT * FROM TEST WHERE ID = 1"

conn.Execute CommandText:=strSQL1

strSQL2 = "DELETE FROM TEST WHERE ID = 1"

conn.Execute CommandText:=strSQL2

//Connection개체의 연결을 닫는다.
conn.Close

'//Connection개체를 소멸시킨다.
Set conn = Nothing

End Sub

답글
진해식
2019-04-19 / 12:40

안녕하세요. 항상 어려운 문제가 생길때마다 와서 도움 받고 있습니다.
보안관련으로 VBA 소스에 DB 로그인 정보를 하드코딩하지 않는 방법으로 커넥션을 만들 방법이 있을까요?
예를들면 사용자 PC에 설치된 엑셀에 기존연결을 만들어서 기존 연결명만 소스에 코딩하여 커넥션을 구현하는 방법이 가능한지 알고 싶습니다.

감사합니다.

답글