Home > 강좌 > 엑셀에서 Database사용하기 > 6강 – VBA로 엑셀Sheet에 연결하기(Data삭제-ADO이용안함)

6강 – VBA로 엑셀Sheet에 연결하기(Data삭제-ADO이용안함)

5강에서는 ADO를 이용하여 엑셀Sheet에 Data를 추가하는 작업을 해 보았다. 이번 강좌에서는 엑셀Sheet에서 Data를 삭제하는 기능을 구현해 보고자 한다.

5강에서도 언급했지만, 엑셀을 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가 삭제되도록 다음의 코드를  삭제버튼 클릭이벤트에 입력한다.

Private Sub cmdDelete_Click()

Dim argUser As User
Dim result As JobResult
    
    If Me.txtId.Value = "" Then
        MsgBox "조회 후 삭제하세요."
        Exit Sub
    End If
    
    argUser.id = CLng(Me.txtId.Value)
    
    '//삭제처리하고 결과값을 받는다.
    result = deleteUser(argUser:=argUser)

    
    '//처리 후 결과코드로 정상처리 여부를 판단한다.
    If result.code = 0 Then
        MsgBox result.message
    Else
        MsgBox "작업중 에러가 발생했습니다. 아래의 메시지를 확인바랍니다." & vbNewLine & vbNewLine & result.message
    End If
    
    '//처리후 변경된 내용을 조회하여 Form에 반영한다.
    loadUserToForm
    

End Sub

 

2. Data 삭제(Delete) Function

Data를 Delete하는 Function이다. 위의 코드에서 ”deleteUser(argUser:=argUser)” 부분이다. 강좌 첫부분에서 언급한 "엑셀에서 Delete문은 사용할 수 없는 제약"를 극복하기 위해 VBA를 이용하여 Key값에 해당하는 행을 찾아서 직접 삭제하도록 구현하였다. 향후 RDBMS를 사용할때는 다른 로직은 수정할 필요 없고 deleteUser Function에서 SQL을 이용하도록 수정하면 된다(인수 및 리턴값은 동일하게 설계함).

Function deleteUser(argUser As User) As JobResult
'// 엑셀에서는 SQL을 이용하여 Delete를 할 수 없다.
'// Delete를 시도하면 다음과 같이 에러가 발생한다.
'// 이 ISAM에서는 연결된 테이블의 데이터를 삭제할 수 없습니다.
'// 따라서 VBA를 이용하여 Key값에 해당하는 행을 찾아서 직접 삭제한다.
    
Dim result As JobResult
Dim affectedCount As Long
Dim rowCount As Long
Dim i As Long
Dim CalcMode As Long


On Error GoTo ErrHandler

    With Application
        CalcMode = .Calculation
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
    End With


    ActiveWorkbook.Sheets("사원정보").Select
    
    '//사번이 있는 열로 끝행의 위치를 구한다.
    Range("C1").Select
    rowCount = Range(Selection, Selection.End(xlDown)).Rows.Count
    
    '//loop를 돌면서 key값이 일치하는 행이 나오면 행을 삭제한다.
    For i = 2 To rowCount
        If CLng(Cells(i, 3).Value) = argUser.id Then
            Rows(i & ":" & i).Delete shift:=xlUp
            affectedCount = 1
            Exit For
        End If
    Next i
    
    If affectedCount = 0 Then
        result.code = -1
        result.message = "입력한 사번에 해당하는 Data가 없어서 삭제되지 않았습니다."
    Else
        result.code = 0
        result.message = "자료가 삭제되었습니다."
    End If
    
CommonEnd:
    
    deleteUser = result
    
    With Application
        .ScreenUpdating = True
        .Calculation = CalcMode
    End With
    
    
Exit Function

'//에러처리
ErrHandler:

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

 

첨부(실습용 파일) : 5강_6강_VBA를_이용하여DB에Data추가_삭제_20170616수정.xlsm

3강부터 6강까지 엑셀을 Database처럼 사용하여 자료의 조회,저장,추가,삭제하는 기능을 구현해 보았다. 간단한 자료관리 기능을 구현할 때는 괜찮은 방법이지만 여러 사람이 자료를 공유하거나 자료가 복잡한 경우에는 이 방법이 그다지 효율적이지 못하다. 다음 강좌 부터는 MS SQL Server, Oracle, MySQL, Maria DB 등 관계형 DB를 이용하여 자료관리기능을 구현해 보고자 한다.

 

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

 

“6강 – VBA로 엑셀Sheet에 연결하기(Data삭제-ADO이용안함)”의 5개의 댓글

  1. 조회 저장 추가 기능은 모두 다른 엑셀 파일을 불러서 가능합니다.
    strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.Path & "\" & "복사본.xlsx" & ";" & "Extended Properties=Excel 12.0;"
    이런 방식으로 말입니다.
    헌데 삭제의 경우
    ActiveWorkbook.Sheets("사원정보").Select
    이 부분을 다른 파일(위의 경우에는 "복사본.xlsx")의 데이터를 지정하여 삭제하는 방법이 없다는 말씀일까요?

    1. 강의에서는 SQL로 삭제처리하는 것이 안된다는 이야기였고요, 말씀하신대로 다른 파일의 데이터를 지정하여 삭제하는 것이 가능하지만 SQL과 달리 파일을 열어야합니다. 작업을 다 하고 난 뒤에는 파일을 닫아 주어야 하고요. 사용하기가 좀 까다롭습니다. 아래 코드를 참조하세요. 파일을 여는 것(Open)만 예를 들었습니다. 닫는 처리는 따로 해 주셔야 합니다.

      Sub deltest()

      Dim wbk As Workbook
      Set wbk = Workbooks.Open("C:\temp\test.xlsx")

      wbk.Worksheets("Sheet1").Select
      Rows("2:2").Select
      Selection.Delete Shift:=xlUp

      End Sub

      1. 아... 감사드립니다. 그렇군요... sql로는 삭제가 안되어서 일반적인 코드로 진행해야하군요... 친절한 응답 감사드립니다... 행복하세요^^

  2. 여기 프로그램에 listbox에 현재에 3개출력되는데 4개이상출력하려고합니다 어떻게 하나요

    1. listbox에는 조회조건에 해당하는 모든 레코드를 출력합니다. 입력된 조회조건 값을 AND조건으로 출력하는 것입니다. 3개로 제한하지는 않습니다. 부서와 이름을 입력하지 않으면 업로드된 샘플기준으로는 총 10개의 레코드가 출력됩니다. 만약 조회조건 중에서 부서에만 "영업"을 입력하면 3개의 레코드가 출력되고, 이름에만 "김"을 입력하면 4개의 레코드가 출력됩니다. 감사합니다.

답글 남기기

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