Home > 강좌 > 엑셀에서 Database사용하기 > 5강 – VBA로 엑셀Sheet에 연결하기(ADO를 이용하여 Data추가)

5강 – VBA로 엑셀Sheet에 연결하기(ADO를 이용하여 Data추가)

4강에서는 엑셀Sheet에서 ADO를 이용하여 Data를 조회하고 수정하는 작업을 해 보았다. 이번 강에서는 Data를 엑셀Sheet에 추가하는 기능을 구현해 보고자 한다.

그 전에 엑셀을 Database처럼 사용할때의 제약사항을 살펴보기로 하자. 엑셀을 Database처럼 사용할 수 있지만, RDBMS(관계형Database Management System)가 제공하는 기능을 다 이용할 수는 없다.
다음과 같은 몇 가지 제약이 있다.
1) Primary key를 설정할 수 없다. 따라서 Insert전에 중복check를 해야 한다.
2) Foreign key, consraints 등도 쓸수 없다.
3) Delete SQL문을 쓸 수없다. 지원하지 않음
4) Excel sheet는 칼럼의 data type을 지정할 수 없기 때문에 숫자 필드에 text값이 들어갈 수 있다.  data type issue는 다음 링크를 참고
http://stackoverflow.com/questions/34667917/inserting-into-empty-excel-tables-with-sql-without-losing-data-type-property

위의 제약사항을 염두에 두고 다음과 같이 해보자.

lec04_save02

1) Data를 새로이 추가하기 위해서 폼의 필드를 초기화해야 한다. 그래야 새로이 Data를 입력할 수 있다. 위 폼의 "추가"버튼을 누르면 필드가 초기화 되도록 다음의 코드를 입력한다.


Private Sub cmdAdd_Click()

With Me
    .txtId.Enabled = True
    .txtId.BackColor = &HFFFFFF

    .txtId.Value = ""
    .txtUserName.Value = ""
    .txtDeptName.Value = ""
    .txtSalary.Value = ""
End With

End Sub

2) 사용자가 추가 버튼을 누르고 textbox를 초기화한 후 추가할 자료를 입력하고 난 후 "저장"버튼을 누면 자료가 새로이 저장되도록 다음 코드를 입력한다. 4강에서는 저장버튼을 누르면 조회된 Data를 저장(Update)만 했는데 이번 강에서는 저장(Update), 추가(Insert) 2가지 기능을 하도록 소스코드를 다음과 같이 수정한다.

Private Sub cmdSave_Click()

Dim argUser As User
Dim result As JobResult
    
    
    '//입력값 Validation 로직 추가 필요(값이 비었는지, 숫자필드에 문자가 들어오는지 등...)
    If checkTextBox(Me.txtId, "사번", True, , "NUMERIC", False) = False Then Exit Sub
    If checkTextBox(Me.txtDeptName, "부서", True, , , True) = False Then Exit Sub
    If checkTextBox(Me.txtUserName, "이름", True, , , True) = False Then Exit Sub
    If checkTextBox(Me.txtSalary, "급여", True, , "NUMERIC", True) = False Then Exit Sub

    
    '//저장할 값을 구조체에 넣는다.
    argUser.deptName = Me.txtDeptName.Value
    argUser.userName = Me.txtUserName
    argUser.id = CLng(Me.txtId.Value)
    argUser.salary = CDbl(Me.txtSalary.Value)
    
    If Me.txtId.Enabled Then '//"추가"버튼을 누를때 Enable=True했기 때문에 Data Insert Mode이다.
        '//insert 후 결과값을 받는다.
        result = insertUser(argUser:=argUser)
    Else
        '//update 후 결과값을 받는다.
        result = updateUser(argUser:=argUser)
    End If
    
    '//처리 후 결과코드로 정상처리 여부를 판단한다.
    If result.code = 0 Then
        MsgBox result.message
    Else
        MsgBox "처리 중 에러가 발생했습니다. 아래의 메시지를 확인바랍니다." & vbNewLine & vbNewLine & result.message
    End If
    
    
    '//처리 후 변경된 내용을 조회하여 Form에 반영한다.
    loadUserToForm queryKey:=argUser.id
        

End Sub

3) Data를 Insert하는 Function이다. 위의 코드에서 " insertUser(argUser:=argUser)" 부분이다. 강좌 첫부분에서 언급한 엑셀을 Database처럼 사용할 때의 제약을 극복하기 위해 Primary Key중복 check, Data type check가 안되는 것을 cover하기 위한 부분이 추가되었다.

Function insertUser(argUser As User) As JobResult

Dim db As New ADODB.Connection
Dim rs As New ADODB.Recordset '//조회용
Dim rs2 As New ADODB.Recordset '//조회용
Dim strSQL_EMPTY_CHECK As String
Dim strSQL As String
Dim strConn As String
Dim result As JobResult
Dim CalcMode As Long
   
    
On Error GoTo ErrHandler


  
    '//Excel을 Database로 사용할때는 Primary key를 설정할 수 없으므로 Insert시 자동으로 중복Key check가 안된다. 따라서 Insert전에 중복인지 아래와 같이 check한다.
    strSQL = "SELECT COUNT(*) AS CNT FROM [사원정보$] WHERE [사번] = " & argUser.id
    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 rs("CNT") > 0 Then
        result.code = -1
        result.message = "사번이 동일한 자료가 이미 존재합니다."
    Else
        '//Data가 하나도 없을 경우에 처음으로 INSERT하면 Data type이 text로 들어가버림, 이 문제를 해결하기위해 첫번째 행이 INSERT될때는 VBA로 DATA를 넣는다.
        strSQL_EMPTY_CHECK = "SELECT COUNT(*) AS CNT FROM [사원정보$]"
        rs2.Open strSQL_EMPTY_CHECK, strConn, adOpenStatic, adLockReadOnly, adCmdText
        If rs2("CNT") = 0 Then
        
            With Application
                CalcMode = .Calculation
                .Calculation = xlCalculationManual
                .ScreenUpdating = False
            End With
            
            ActiveWorkbook.Sheets("사원정보").Select
            
            If IsNumeric(argUser.deptName) Then
                Cells(2, 1).Value = "'" & argUser.deptName
            Else
                Cells(2, 1).Value = argUser.deptName
            End If
            
            If IsNumeric(argUser.userName) Then
                Cells(2, 2).Value = "'" & argUser.userName
            Else
                Cells(2, 2).Value = argUser.userName
            End If

            Cells(2, 3).Value = argUser.id
            Cells(2, 4).Value = argUser.salary
            
            With Application
                .ScreenUpdating = True
                .Calculation = CalcMode
            End With

        Else
    
            '//DB open
            db.Open strConn
        
            strSQL = "INSERT INTO [사원정보$] ([부서],[이름],[사번],[급여]) " & _
                     "VALUES ( '" & esc(argUser.deptName) & "','" & _
                                    esc(argUser.userName) & "'," & _
                                    argUser.id & "," & _
                                    argUser.salary & ")"
                                    
            db.Execute CommandText:=strSQL
                
            db.Close
            Set db = Nothing
            
        End If
        
        rs2.Close
        Set rs2 = Nothing
        
        result.code = 0
        result.message = "자료가 추가되었습니다."

    End If
    
    rs.Close
    Set rs = Nothing


CommonEnd:
    
    insertUser = result
    
   
Exit Function

'//에러처리
ErrHandler:

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

5강은 여기에서 마무리하고 6강에서는 Data를 삭제하는 기능을 구현하고자 한다.

전체코드가 다 설명된 것은 아니므로 상세한 내용은 첨부파일의 소스코드를 참고한다.

 

실습용 파일 다운로드 : 5강_6강_VBA를_이용하여DB에Data추가_삭제_20170616수정.xlsm

사번을 숫자에서 문자열로 변경한 경우의 실습용 파일  : 5강_6강_VBA를_이용하여DB에Data추가_삭제_20170616수정_20200915사번을문자열로변경한케이스.zip

 

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

 

“5강 – VBA로 엑셀Sheet에 연결하기(ADO를 이용하여 Data추가)”의 9개의 댓글

    1. 조회버튼 누르면 혹시 "매크로를 실행할수 없다"는 메시지가 나오지 않나요? 혹시 그렇다면 매크로 실행하는 것으로 설정하시면 됩니다. 다른 메시지가 뜬다면 메시지내용을 알려주시면 확인해보고 답변드리겠습니다.

      1. 메시지는 나오지 않고, 조회버튼을 눌르면 시트에 있는 내용이 리스트박스안에 출력이 됩니다
        부서나 이름을 입력하고 조회눌려도 조회가 되지가 않습니다

        1. 확인해보니 프로그램에 버그가 있었네요. 부서나 이름을 입력하고 조회하면 입력한 조건에 맞는 내용이 출력되도록 수정해서 첨부파일로 다시 올려놓았습니다.
          감사합니다.

  1. 엑셀배우미

    지금도 답변을 달아주실지는 모르겠지만 정말정말 도움이 필요합니다 ㅜㅜ
    올려주신 파일에 '사번' 칸에, 지금 숫자만 들어가게 되어있는데요...!

    제가 사번에 글자가 들어갈 수 있도록 esc, string 등을 다 수정해가면서 작업해놨는데...
    추가를 누른 후 사번칸에 숫자가 아닌 글자를 넣고 저장을 누르면 "필요한 매개 변수 중 한 개 이상에 값이 주어지지 않았습니다" 라는 경고 메시지가 뜨면서 저장이 안되더라고요 ㅜㅜ

    숫자를 넣으면 정상 저장 됩니다. DB쪽도 건드렸었는데, 통 안되네요...혹시 사번에 글자를 넣을 수 있게 한 파일도 올려주실 수 있으신가요? 대조하면서 공부해보고 싶습니다...

    1. 사번을 문자열로 바꿔서 하는 예제 파일을 원글에 첨부했습니다. 프로그램소스에서 사번과 id라는 값으로 검색해서 보시면 변경된 부분을 대조할 수 있습니다.

      1. 엑셀배우미

        헉 너무너무 감사합니다!!
        살펴보니까 strSQL 부분에 대한 지식 미숙으로 수정이 안된 곳들이 많았네요 ㅠㅠ
        수정하신 부분을 보니 그래~ 그렇지 생각을 하면서도, 역시 난 멍충멍충했다는 생각이...
        샤릉합니다...!!

  2. 안녕하세요 자료를 받아 다른 것으로 바꾸는 와중에 궁금한 것이 있어 질문드립니다.

    argUser.deptName = Me.txtDeptName.Value
    argUser.userName = Me.txtUserName

    텍스트 값으로 값을 넘겨 받을 때 Value를 붙이는 것과 안붙이는 것에 차이는 무엇인가요?
    둘다 해봤는데 이상은 없어서 궁금하여 질문드립니다. 감사합니다.

    1. 동일합니다. 차이가 없습니다. 기본값이 .Value라서 안붙여도 결과가 동일합니다.

답글 남기기

이메일 주소는 공개되지 않습니다.