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

공개됨 글쓴이 Admin댓글 5개
      

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이다. 위의 코드에서 ”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이용안함)에 1개의 응답

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

    • 강의에서는 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

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

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

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

댓글 남기기

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