4강 - VBA로 엑셀Sheet에 연결하기(ADO를 이용하여 조회 및 저장)

3강에서는 ADO를 이용하여 엑셀 데이터베이스에 연결하고 Data를 엑셀Sheet로 가져와 보았다.

이번 강에서는 Data를 엑셀Sheet가 아닌 아래 그림과 같이 Form으로 가져와서 수정하고 저장하는 기능을 구현해 보기로 한다.

아래와 같이 따라 해보자.

1. Form만들기

VBA모드에서 Form을 추가하고 아래 그림과 같이 listbox, textbox, command button을 추가한다.

2. 조회버튼 코드 추가

"조회"버튼을 더블클릭하여 다음 코드를 추가한다. 조회버튼을 클릭하면 다음의 코드가 실행된다는 의미이다.


Private Sub cmdUserListInq_Click()
Dim userArray() As User
Dim recordCount As Integer
Dim listData() As String
Dim i As Integer

'//사용자가 입력한 조건을 selectUsers function에 넘겨주고 결과를 Array로 받는다.
userArray = selectUsers(argDeptName:=Me.argTxtDeptName.Value, argUserName:=Me.argTxtUserName.Value)

'//조회된 자료가 없으면 Ubound를 이용해서 userArray의 갯수를 check하면 에러가 나므로, 에러 무시하도록 On Error Resume Next를 사용한다.
'//Ubound이후에 에러가 나면 처리해야 하므로 Ubound밑에서 바로 On Error GoTo 0를 사용한다.
On Error Resume Next
recordCount = UBound(userArray)
On Error GoTo 0

If recordCount = 0 Then
    MsgBox "입력한 조건에 해당하는 Data가 없습니다"
    Exit Sub
End If

'//리스트에 넣을 배열을 조회된 레코드 갯수만큼의 크기로 초기화한다.
ReDim listData(1 To recordCount, 1 To 4)

'//조회된 레코드를 배열에 채운다.
For i = 1 To recordCount
    listData(i, 1) = userArray(i).deptName
    listData(i, 2) = userArray(i).userName
    listData(i, 3) = userArray(i).id
    listData(i, 4) = userArray(i).salary
Next i

'//배열을 리스트에 넣으면 리스트가 조회된다.
With frmUser.lstUser
    .List = listData
End With

End Sub

3. 저장버튼 코드 추가

"저장"버튼을 더블클릭하여 다음 코드를 추가한다.


Private Sub cmdSave_Click()

Dim argUser As User
Dim result As JobResult

'//입력값 Validation 로직 추가 필요(값이 비었는지, 숫자필드에 문자가 들어오는지 등...)

'//저장할 값을 구조체에 넣는다.
argUser.deptName = Me.txtDeptName.Value
argUser.userName = Me.txtUserName
argUser.id = CLng(Me.txtId.Value)
argUser.salary = CDbl(Me.txtSalary.Value)

'//저장 후 결과값을 받는다.
result = updateUser(argUser:=argUser)

'//저장후 변경된 내용을 list에 반영하는 로직 추가 필요

'//저장후 결과코드로 정상처리 여부를 판단한다.
If result.code = 0 Then
    MsgBox "저장이 완료되었습니다."
Else
    MsgBox "작업중 에러가 발생했습니다. 아래의 메시지를 확인바랍니다." & vbNewLine & vbNewLine & result.message
End If

End Sub

4. listbox 코드 추가

Form의 listbox 더블클릭하여 다음 코드를 추가한다. listbox를 클릭하면 아래 코드가 실행된다는 의미이다.


Private Sub lstUser_Click()

Me.txtDeptName = Me.lstUser.Column(0)
Me.txtUserName = Me.lstUser.Column(1)
Me.txtId = Me.lstUser.Column(2)
Me.txtSalary = Me.lstUser.Column(3)

End Sub

5. 모듈에 코드 추가

아래 소스코드를 Module에 붙여 넣는다.

첫번째 소스는 필요한 구조체를 선언하는 부분이고,


'사용자정보를 구조체로 선언한다.
Type User
    deptName As String
    userName As String
    id As Long
    salary As Double
End Type

'//작업결과를 받기 위해 구조체를 선언한다.
Type JobResult
    code As Long
    message As String
End Type

두번째는 위의 소스코드에서 Data를 가져오는 부분이다.


Public Function selectUsers(ByVal argDeptName As String, ByVal argUserName As String) As User()

Dim rs As New ADODB.Recordset
Dim strSQL As String
Dim strConn As String
Dim i As Integer
Dim j As String
Dim userArray() As User

'//조회조건을 설정할 문자열 변수
Dim strDeptName As String
Dim strUserName As String
'// 조회조건 설정 - 입력된 값이 있을 때만 SQL문의 조건절을 만든다.
If argDeptName = "" Then
    strDeptName = ""
Else
    strDeptName = " AND [부서] LIKE '%" & Replace(argDeptName, "'", "''") & "%'"
End If

If argUserName = "" Then
    strUserName = ""
Else
    strUserName = " AND [이름] LIKE '%" & Replace(argUserName, "'", "''") & "%'"
End If

strSQL = "SELECT [부서],[이름],[사번],[급여]" & _
" FROM [사원정보$] " & _
" WHERE [이름] > '' " & strDeptName & strUserName


'Excel을 Database로 사용
strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.Path & "\" & ActiveWorkbook.Name & ";" & "Extended Properties=Excel 12.0;"
rs.Open strSQL, strConn, adOpenStatic, adLockReadOnly, adCmdText


If Not rs.EOF Then '//자료가 존재하면 loop를 돌면서 userArray에 결과를 넣는다.
    ReDim userArray(CLng(rs.recordCount))

    i = 1

    Do Until (rs.EOF = True)

        userArray(i).deptName = rs("부서").Value
        userArray(i).userName = rs("이름").Value
        userArray(i).id = rs("사번").Value
        userArray(i).salary = IIf(IsNull(rs("급여").Value), 0, rs("급여").Value)

        rs.MoveNext
        i = i + 1
    Loop
End If

rs.Close
Set rs = Nothing

selectUsers = userArray


End Function

6. Data저장기능 구현

Data를 저장하는 Function이다. 저장버튼을 누르면 이 Function이 실행되어 Data가 저장(Update)된다.


Function updateUser(argUser As User) As JobResult

Dim db As New ADODB.Connection
Dim strSQL As String
Dim strConn As String
Dim result As JobResult
Dim updateCount As Long

On Error GoTo ErrHandler

'//DB연결
strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.Path & "\" & ActiveWorkbook.Name & ";" & "Extended Properties=Excel 12.0;"

db.Open strConn

strSQL = "UPDATE [사원정보$] " & _
" SET [부서] = '" & argUser.deptName & "'," & _
" [이름] = '" & argUser.userName & "'," & _
" [급여] = " & argUser.salary & _
" WHERE [사번] = " & argUser.id


db.Execute CommandText:=strSQL, recordsaffected:=updateCount

If updateCount = 0 Then
    result.code = -1
    result.message = "입력한 사번에 해당하는 Data가 없어서 업데이트 되지 않았습니다."
Else
    result.code = 0
End If


db.Close
Set db = Nothing

updateUser = result

Exit Function

'//에러처리
ErrHandler:

If (Err.Number <> 0) Then
    result.code = Err.Number
    result.message = Err.Description
    updateUser = result
End If

End Function

7. 조회버튼에 모듈코드 연결

마지막으로 아래 코드를 Module에 붙여 넣고 Excel sheet위에 추가한 "조회"버튼에 이 모듈코드가 실행되도록 연결한다.


Public Sub callFrmUser()

frmUser.Show

End Sub

위의 내용만으로 완전한 기능을 구현할 수 없다. Form에 추가된 textbox등은 이름이 위의 코드와 일치해야하고, 또 위에 언급되지 않은 부분이 있을 수 있으므로 자세한 내용은 첨부를 다운로드 받아서 확인해 보자.

첨부(실습용 파일) : 4강_VBA를_이용하여DB조회_저장.xlsm

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

엑셀웍스 책 출간 안내

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

자세히 보기

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