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

공개됨 글쓴이 Admin댓글 4개
      

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

 

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

 

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

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

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

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

댓글 남기기

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