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

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

[엑셀 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 업로드로 가려 합니다.

스크랩
공유
회원등급 : 27레벨
포인트 : 3751 EP
전체 5

  • 2021-10-14 14:52

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

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


  • 2021-12-13 20:19

    @dra**** 님 이런 엄청난 자료들이...감사합니다!


  • 2022-01-11 21:16

    @dra**** 님 감사합니다,


  • 2021-10-14 15:23

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

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

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

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

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


  • 2021-10-27 17:13

    @dra**** 님 감사합니다


전체 60
번호 제목 작성자 작성일 추천 조회
알림
🎉다양한 이벤트와 함께 진행하는, PPT 보고서 디자인 특강 안내 - 9/24 (토) 오후 9시~ (15)
오빠두엑셀 | 2022.09.08 | 추천 9 | 조회 2396
오빠두엑셀 2022.09.08 9 2396
공지사항
[VBA] 구하라 그러면 주어질 것이다. - VBA 마스터 E-Book (영문판) 첨부파일 (4)
dra**** | 2022.07.19 | 추천 4 | 조회 209
dra**** 2022.07.19 4 209
40590
[엑셀 VBA] 예약 DB - 1. 엑세스, SQL, Recordset, ListObject 의 상관 관계 (4)
dra**** | 2022.07.29 | 추천 3 | 조회 184
dra**** 2022.07.29 3 184
40511
[잡설] 엑셀의 꽃 파워 쿼리를 버리다. (2)
dra**** | 2022.07.29 | 추천 4 | 조회 281
dra**** 2022.07.29 4 281
40321
[경험담..해결했습니다.] &H80004005(-2147467259)시스템오류 (2)
티엠프이 | 2022.07.25 | 추천 2 | 조회 149
티엠프이 2022.07.25 2 149
40074
M365 업데이트 이후 발생하는 H80004005 (-2147467259) 자동화 오류 해결방법
오빠두엑셀 | 2022.07.23 | 추천 0 | 조회 157
오빠두엑셀 2022.07.23 - 157
39608
엑셀 그래프에 관한 유튜브 채널정보 입니다. (4)
레몬네이드 | 2022.07.15 | 추천 -1 | 조회 141
레몬네이드 2022.07.15 -1 141
36473
[엑셀VBA] 시트 이름을 변수로 설정하고 싶을때 간단한 방법 (2)
알파고 | 2022.06.27 | 추천 2 | 조회 203
알파고 2022.06.27 2 203
35196
Print_Area 동적 인쇄 영역설정 첨부파일
sean | 2022.06.21 | 추천 4 | 조회 248
sean 2022.06.21 4 248
34607
셀레니움으로 개별공시지가 조회 첨부파일 (3)
나야 | 2022.06.02 | 추천 1 | 조회 228
나야 2022.06.02 1 228
26273
엑셀 2021, M365 가로스크롤 기능 (23)
더블유에이 | 2022.01.23 | 추천 16 | 조회 711
더블유에이 2022.01.23 16 711
Re:엑셀 2021, M365 가로스크롤 기능
김학동 | 2022.01.23 | 추천 1 | 조회 240
김학동 2022.01.23 1 240
Re:엑셀 2021, M365 가로스크롤 기능
김동희 | 2022.05.30 | 추천 0 | 조회 89
김동희 2022.05.30 0 89
24189
[엑셀 VBA] Snippet - OCR: 이미지 파일을 텍스트 파일로 변환하기 (25)
dra**** | 2021.12.01 | 추천 8 | 조회 1671
dra**** 2021.12.01 8 1671
23999
[엑셀 VBA] Snippet - Public IP, Local IP, Mac Address 구하기 (6)
dra**** | 2021.11.25 | 추천 2 | 조회 619
dra**** 2021.11.25 2 619
23794
[엑셀 VBA] Snippet - 변수를 clipboard로 복사하기 (8)
dra**** | 2021.11.21 | 추천 2 | 조회 749
dra**** 2021.11.21 2 749
23337
HTML 라이브러리 사용하기 귀찮을 때, 사용하는 파싱함수 (getElementsByTag, getAttribute) (8)
오빠두엑셀 | 2021.11.09 | 추천 0 | 조회 385
오빠두엑셀 2021.11.09 - 385
23142
[잡설] 엑셀을 이용한 웹 자동화: SEO 이야기 (19)
dra**** | 2021.11.04 | 추천 8 | 조회 907
dra**** 2021.11.04 8 907
Re:[잡설] 엑셀을 이용한 웹 자동화: SEO 이야기
영일만친구 | 2022.04.25 | 추천 0 | 조회 109
영일만친구 2022.04.25 0 109
22802
[잡설] 웹 서버에서 내보내는 엑셀 파일 및 데이터 이야기 (8)
dra**** | 2021.10.25 | 추천 1 | 조회 563
dra**** 2021.10.25 1 563
22325
[엑셀 VBA] 파워쿼리 자동화 4. CRUD (5)
dra**** | 2021.10.12 | 추천 2 | 조회 878
dra**** 2021.10.12 2 878
22161
[엑셀 VBA] 파워쿼리 자동화 3. MySQL Class (9)
dra**** | 2021.10.07 | 추천 1 | 조회 806
dra**** 2021.10.07 1 806
21839
[엑셀 VBA] 파워쿼리 자동화 2. 자동화 실습 (13)
dra**** | 2021.09.28 | 추천 2 | 조회 825
dra**** 2021.09.28 2 825
21764
[엑셀 VBA] 개체 Reference - QueryTable Object 첨부파일 (2)
dra**** | 2021.09.26 | 추천 0 | 조회 336
dra**** 2021.09.26 - 336
21675
[엑셀 VBA] 파워쿼리 자동화 1. 자동화 순서 (13)
dra**** | 2021.09.23 | 추천 1 | 조회 2147
dra**** 2021.09.23 1 2147