[엑셀 VBA] 파워쿼리 자동화 4. CRUD

작성자
dra****
작성일
2021-10-12 22:18
조회
150

[엑셀 VBA] 파워쿼리 자동화 4. CRUD

이번에 포스팅하는 내용은, 조회, 수정, 삭제, 추가와 관련된 내용입니다.

1. 자료 조회
WorrbookQuery에 있는 SQL 부분을 수정합니다.

아래 그림에서 보면 조회 조건은, 조회기간, 직책별 검색을 하는 부분입니다.

거의 모든 테이블에 거는 조건이 비슷하기에 SQL을 자동으로 만들어 주는 함수를 하나 만들었습니다.

1.1 getSQLByExpense 함수

'SQL문 만들어 반환하기
Public Function getSQLByExpense(prefix As String, tbName As String) As String
   Dim SDATE As Date: SDATE = shEnquiry.Range(prefix & "_SDATE").Value
   Dim EDATE As Date: EDATE = shEnquiry.Range(prefix & "_EDATE").Value
   Dim db_DATE_field As String: db_DATE_field = "DATE_" & prefix
 
   Dim strSQL As String
   Dim POSITION1 As String: POSITION1 = shEnquiry.Range(prefix & "_POSITION1").Value
 
   If UCase(POSITION1) = "ALL" Then
      strSQL = "SELECT * FROM `" & tbName & "` WHERE " & db_DATE_field & ">='" & SDATE & "' AND " & db_DATE_field & "<='" & EDATE & "' ORDER BY POSITION1, EID, " & db_DATE_field & ";"
   Else
      strSQL = "SELECT * FROM `" & tbName & "` WHERE POSITION1='" & POSITION1 & "' AND (" & db_DATE_field & ">='" & SDATE & "' AND " & db_DATE_field & "<='" & EDATE & "') ORDER BY POSITION1, EID, " & db_DATE_field & ";"
   End If
 
   'Debug.Print strSQL
   getSQLByExpense = strSQL
End Function

 

1.2 refresh 함수 : 이 함수가 조회를 하게 하는 함수 입니다.

' CA 데이터 갱신하기
Public Sub CA_Refresh()
   Call mdGlobal.setWorksheets
 
   Dim strFormula As String
   Dim strSQL As String: strSQL = ThisWorkbook.Worksheets("ENQUIRY").getSQLByExpense("CA", "ys_tbCA")
   svName = GetCustomPropertyValue("server")
   dbName = GetCustomPropertyValue("database")
 
   strFormula = "let Source = MySQL.Database(""" & svName & """, """ & dbName & """, [ReturnSingleDatabase=true, Query=""" & strSQL & """]), #""Transform"" = Table.TransformColumnTypes(Source,{{""DATE_CA"", type date}}) in #""Transform"""
 
   ThisWorkbook.Queries("qryCA").formula = strFormula
 
 
   On Error GoTo Error
   Application.EnableEvents = False
      ws.ENQUIRY.ListObjects("tableCA").TableObject.Refresh
      Call toolsSheet.fillRowNumber(ws.ENQUIRY.Cells(10, "B"), "D10")
   Application.EnableEvents = True
 
   Exit Sub
Error:
   MsgBox Err.Description
End Sub

 

strSQL은 getSQLByExpense() 함수를 이용해서 가져옵니다.

strFormula에 M Script언어를 대입하는데, 여기에 DB 정보 및 SQL만 넘겨 줍니다.

그리고 표를 Refresh하면 마법처럼 원하는 자료를 가지고 옵니다.

 

 

2. 수정

아래 그림을 보면 따라다니는 버튼을 두 개 만들어 두었습니다.

하나는 update이고, 하나는 delete 버튼입니다.

업데이트를 할 때, WHERE id='id'를 주어야 하기에 'id' column 값을 가져 오는 부분과

나머지 필드를 모두 가져오는 루틴이 있어야 합니다.

제일 먼저 선택한 행의 Row Number를 알아야 하기에 그것을 찾아 오는 함수 하나를 만들었습니다.

 

2.1 getListObjectRowNumber()

'선택한 셀이 ListObject이면 선택한 셀의 행 번호 가져오기
Public Function getListObjectRowNumber() As Long
   If TypeName(Selection.ListObject) = "ListObject" Then
      getListObjectRowNumber = Selection.row - Selection.ListObject.Range.row
      Exit Function
   End If
   getListObjectRowNumber = -1
End Function

그리고 난 다음 나머지 필드명과 값을 구해옵니다.

이전에 포스팅한 RangeToArray() 함수를 참조하면 됩니다.

 

2.2 update()함수

' CA UPDATE
Public Sub CA_Update()
   mdGlobal.set_query_data
   Dim row_num: row_num = ThisWorkbook.Worksheets("ENQUIRY").getListObjectRowNumber
 
   Dim id: id = lobj.tableCA.DataBodyRange(row_num, 1).Value
   If id = "" Or Not IsNumeric(id) Then
      MsgBox "There is no 'id' or not a valid 'id' for updating.": Exit Sub
   End If
 
   Dim arrFields(): arrFields = toolsSheet.RangeToArray(lobj.tableCA.HeaderRowRange)
   Dim arrValues(): arrValues = toolsSheet.RangeToArray(lobj.tableCA.DataBodyRange.Rows(row_num))
 
   Call mdGlobal.setMySQL(True)
      If MySQL.Update_Recordset(db.CA, id, arrFields, arrValues) = True Then MsgBox "Updated ..."
   Call mdGlobal.setMySQL(False)
End Sub

 

참고로 저는 테이블이름이나 고정되어 있는 값들의 변수들 오타를 줄이기 위해서

Type이나 Enum을 즐겨 씁니다. 그럼 오타를 낼 확률이 매우 적습니다.

' 표 객체
Type pListObjects
   tableCA           As ListObject
   tableBEN          As ListObject
   tableCOMMUTE      As ListObject
   tableOT           As ListObject
   tableEMP          As ListObject
   tableCASHIER      As ListObject
   tableDINING       As ListObject
   tableDISHWASHER   As ListObject
   tableMANAGER      As ListObject
   tableBENEFITS     As ListObject
   tableSalary       As ListObject
   tableSalary_BEN   As ListObject
   tableSalary_CA    As ListObject
End Type
Public lobj As pListObjects이

이런식으로 사용하면 타이핑 할일도 없고, 매우 편합니다.

 

3. 삭제

삭제는 아이디 값만 있으면 되기에 매우 쉽습니다. 삭제 전에 확인 메시지 한 번 띄워 주고 갑니다.

' CA DELETE
Public Sub CA_Delete()
   If MsgBox("Sure to DELETE?", vbYesNoCancel, "DELETE") <> vbYes Then Exit Sub
 
   mdGlobal.setAll
   Dim row_num: row_num = ThisWorkbook.Worksheets("ENQUIRY").getListObjectRowNumber
 
   Dim id: id = lobj.tableCA.DataBodyRange(row_num, 1).Value
 
   If id = "" Or Not IsNumeric(id) Then
      MsgBox "There is no 'id' or not a valid 'id' for updating.": Exit Sub
   End If
 
   Call mdGlobal.setMySQL(True)
      If MySQL.Delete_Using_Conn(id, db.CA) = True Then
 
         lobj.tableCA.ListRows(row_num).Delete
         Call fillRowNumber(Cells(10, "B"), "D10")
 
         MsgBox "Deleted ..."
      End If
   Call mdGlobal.setMySQL(False)
End Sub

 

4. 데이터 추가

추가를 하기 위해서 시트에 바로 입력하기 보다는 폼 양식을 선택했습니다.

이유는 직원 EID나 이름 같은 곳에서 오타를 내는 실수를 방지 하기 위함이기도 합니다.

폼에 있는 컨트롤의 이름을 필드명과 같게 하면 매우 쉽게 각각의 값들을 가져 올 수 있습니다.

 

4.1 MySQL에 삽입하고, 시트에도 내용이 추가되도록 해서 수정과 삭제를 가능하게 합니다.

'MySQL에 삽압후 Sheet에도 삽입
Private Sub btnInsertCA_Click()
   With Me
      If valueCheck(.cboPos, "Select POSITION, please") = False Then Exit Sub
      If valueCheck(.cboEmpName, "Select Employee's Name, please") = False Then Exit Sub
      If valueCheck(.txtDateCA, "Select CA DATE, please") = False Then Exit Sub
      If valueCheck(.cboManager, "Select Who approved this CA, please") = False Then Exit Sub
 
      Dim EID As String: EID = .cboEmpName.list(.cboEmpName.ListIndex, 0)
      Dim FIRST_NAME As String: FIRST_NAME = .cboEmpName.Value
      Dim POSITION1 As String: POSITION1 = .cboPos.Value
      Dim DATE_CA As Date: DATE_CA = .txtDateCA.Value
      Dim CA_FOR As String: CA_FOR = "CA"
      Dim AMOUNT As Single: AMOUNT = .txtAmountCA.Value
      Dim APPROVED As String: APPROVED = .cboManager.Value
      Dim INPUTER As String: INPUTER = GetCustomPropertyValue("admin")
   End With
 
   Dim arrFields(), arrValues()
   Dim id
 
   mdGlobal.set_query_data
   arrFields = Array("EID", "FIRST_NAME", "POSITION1", "DATE_CA", "CA_FOR", "AMOUNT", "APPROVED", "INPUTER")
   arrValues = Array(EID, FIRST_NAME, POSITION1, DATE_CA, CA_FOR, AMOUNT, APPROVED, INPUTER)
 
   Call mdGlobal.setMySQL(True)
      id = MySQL.Insert_Array(db.CA, arrFields, arrValues)
      Debug.Print id
      If IsNumeric(id) Then
         Dim listRow As listRow
         Set listRow = lobj.tableCA.ListRows.Add(AlwaysInsert:=True)
 
         ReDim arrValues(1 To 10)
         arrValues = Array(id, EID, FIRST_NAME, POSITION1, DATE_CA, CA_FOR, AMOUNT, APPROVED, INPUTER, VBA.Now)
         listRow.Range.Value = arrValues
         mdGlobal.setWorksheets
         Call toolsSheet.fillRowNumber(ws.ENQUIRY.Cells(10, "B"), "D10")
      End If
   Call mdGlobal.setMySQL(False)
End Sub

 

5. 데이터의 가공

예전의 마소 FORMS를 이용해서 만들었던 출퇴근부를 웹과 PWA로 핸드폰으로 이식을 했습니다.

디자인을 너무 못해서 디자인하는게 제일 어렵더군요.... ㅠㅠ

여기서 핸드폰으로 출퇴근을 등록합니다. 직책 선택하면 자동으로 해당 직책의 이름들이 뜨고,

선택해서 출근인지 퇴근인지만 선택만 하면 됩니다. 이렇게 하면 이 자료가 MySQL 데이터로 들어가고, 이를 엑셀에서 조건에 맞게 조회를 하는 것입니다.

직책, 이름, 일자별로 정렬하고 수정 삭제를 합니다.

 

이렇게 얻은 데이터를 가지고 가공해서 최종 원하는 급여 명세서를 만듭니다.

이 표를 만들때, 제가 잘 몰랐던 표의 막강한 기능들을 알게 되었으며, 행열 추가 삭제시 자동으로 다른 표까지 조정을 다 해주며, SQL에서 해서 가져와야 할 각각의 계산식은 그냥 날로 먹을 수 있습니다. 저는 행추가 삽입 삭제 등등의 기능이 VBA로 했을 때 이렇게 강력한 줄은 몰랐습니다. 예전에는 수동으로 했기에 그리 대단한 줄 몰랐었는데.

이제 모든 데이터는 임대한 DB server에 있고 엑셀은 데이터를 가져와서 조회, 수정, 삭제, 추가의 기능만 하는 단말기가 되었습니다. 파일이 유실되어도 전혀 상관없습니다. 그냥 이 xlsm 파일만 있으면 언제 어디서나 가능하기 때문입니다. 물론 웹과 핸드폰에서도 이 모든 기능을 구현하도록 해 놓았습니다.

이렇게 해서 예전에 FORMS를 이용해서 만들었던 부분들을 새로이 구성하고 에러 처리를 확실하게 내 놓으니 사용하시는 분들이 멘붕에 빠지지 않고 잘 사용하고 계십니다.

지금은 새로운 프로젝트를 하는데, 공부해야 할 내용이 너무 많아, 당분간은 다시 reference 업로드로 가려 합니다.

전체 3

  • 2021-10-14 14:52

    잘 보았습니다. 개인적인 사견으로,... REST API를 구현한다면 더 편리할 것 같습니다. ~ ^^

    excel ==> web server ==> db 순으로 처리를 한다면 보안 및 userful한 환경에서도 보다 더 괜찮을 것 같습니다.
    excel에서 adodb를 사용하여 connection 한다면 분명 db server id와 pw 유출 위험도 있구요...


  • 2021-10-14 15:23

    파워쿼리가 너무 편해서.... Excel -> DB Server로 직접 소통하니 코딩 양이 많이 줄었어요.

    그리고 보안이 그리 크게 문제 되는 곳이 아니어서 로그인하게 하고 VBA 막아 노니 어느정도 커버가 되네요. 내부 직원이 맘 먹고 털으려면 뭘 못하겠습니까...정보를 빼가려 하지 않는다는 전제 하에...

    웹은 웹대로 똑같이 구현해 놓았기에, 엑셀->Web->DB 이렇게 가려면 굳이 엑셀로 만들 이유가 없을 듯 하기도 하고요. 엑셀로는 입출력이 웹보다는 많이 편하기에 ㅎㅎㅎ

    좋은 의견 고맙습니다.
    보안이 중요한 프로젝트때에는 그리 구현을 해야 하는데...
    그럼 포스팅 내용이 Server response 관련된 내용으로 가야 하는데...ㅠㅠ

    ToString.... 코더의 냄새가 물씬 나는 좋은 아이디입니다. ^^


  • 2021-10-27 17:13

    감사합니다


전체 48
번호 제목 작성자 작성일 추천 조회
23999
New [엑셀 VBA] Snippet - Public IP, Local IP, Mac Address 구하기 (4)
dra**** | 2021.11.25 | 추천 1 | 조회 32
dra**** 2021.11.25 1 32
23794
[엑셀 VBA] Snippet - 변수를 clipboard로 복사하기 (5)
dra**** | 2021.11.21 | 추천 2 | 조회 54
dra**** 2021.11.21 2 54
23337
HTML 라이브러리 사용하기 귀찮을 때, 사용하는 파싱함수 (getElementsByTag, getAttribute) (6)
트로피 오빠두엑셀 | 2021.11.09 | 추천 0 | 조회 70
트로피 오빠두엑셀 2021.11.09 0 70
23142
[잡설] 엑셀을 이용한 웹 자동화: SEO 이야기 (10)
dra**** | 2021.11.04 | 추천 0 | 조회 154
dra**** 2021.11.04 0 154
22802
[잡설] 웹 서버에서 내보내는 엑셀 파일 및 데이터 이야기 (6)
dra**** | 2021.10.25 | 추천 0 | 조회 111
dra**** 2021.10.25 0 111
22325
[엑셀 VBA] 파워쿼리 자동화 4. CRUD (3)
dra**** | 2021.10.12 | 추천 1 | 조회 150
dra**** 2021.10.12 1 150
22161
[엑셀 VBA] 파워쿼리 자동화 3. MySQL Class (8)
dra**** | 2021.10.07 | 추천 0 | 조회 170
dra**** 2021.10.07 0 170
21839
[엑셀 VBA] 파워쿼리 자동화 2. 자동화 실습 (8)
dra**** | 2021.09.28 | 추천 0 | 조회 224
dra**** 2021.09.28 0 224
21764
[엑셀 VBA] 개체 Reference - QueryTable Object 첨부파일 (2)
dra**** | 2021.09.26 | 추천 0 | 조회 95
dra**** 2021.09.26 0 95
21675
[엑셀 VBA] 파워쿼리 자동화 1. 자동화 순서 (8)
dra**** | 2021.09.23 | 추천 0 | 조회 393
dra**** 2021.09.23 0 393
21496
[엑셀 VBA] 개체 Reference - ADODB.Recordset : Database 필수 첨부파일 (4)
dra**** | 2021.09.16 | 추천 1 | 조회 190
dra**** 2021.09.16 1 190
21491
[잡설] IT가 흘러가고 있는 방향에 대한 단상 - 무엇을 공부해야 하는가? (21)
dra**** | 2021.09.16 | 추천 4 | 조회 218
dra**** 2021.09.16 4 218
21482
[엑셀 VBA] MySQL 02. Range -> 배열 -> INSERT (5)
dra**** | 2021.09.16 | 추천 0 | 조회 202
dra**** 2021.09.16 0 202
21412
[엑셀 VBA] MySQL 01. 워크시트 Custom Properties 활용하기 (4)
dra**** | 2021.09.14 | 추천 1 | 조회 112
dra**** 2021.09.14 1 112
20740
[엑셀 VBA] 개체 Reference - ListObject Object - [표]가지고 놀기 첨부파일 (12)
dra**** | 2021.08.29 | 추천 4 | 조회 218
dra**** 2021.08.29 4 218
20211
[엑셀 VBA] Windows API - winuser.h : 윈도우즈 프로그램의 핵심 첨부파일 (8)
dra**** | 2021.08.17 | 추천 3 | 조회 202
dra**** 2021.08.17 3 202
20194
[잡설] 엑셀이 웹으로 가야 하는 이유 (23)
dra**** | 2021.08.16 | 추천 12 | 조회 476
dra**** 2021.08.16 12 476
20153
[엑셀 VBA] 개체 Reference - FileSystem Object 첨부파일 (3)
dra**** | 2021.08.15 | 추천 1 | 조회 141
dra**** 2021.08.15 1 141
19717
[엑셀 VBA] 데이터 자동화 - 웹에서 제공하는 MySQL Sever를 이용한 자동화 개념 (15)
dra**** | 2021.08.05 | 추천 2 | 조회 343
dra**** 2021.08.05 2 343
19715
[엑셀 VBA] 개체 Reference - APPLICATION Object 첨부파일 (2)
dra**** | 2021.08.05 | 추천 0 | 조회 117
dra**** 2021.08.05 0 117