[엑셀 VBA] 파워쿼리 자동화 3. MySQL Class

작성자
dra****
작성일
2021-10-07 23:38
조회
111

MySQL 서버 세팅,
Worksheet Custom Property 설정 또는 해제. (저는 SCHEMA라는 이름의 워크시크를 쓰고 있습니다. 각자에 맞는 워크시트 이름으로 변경하시면 됩니다.)
Connection 개체 연결 및 해제,
recordset을 이용한 Create, Insert, Update,
Connection을 이용한 Delete 등을 구현합니다.
Row를 Array로 바꿔주는 함수
외부에서 Connection을 사용하기

이 클래스는 어떠한 프로젝트나 사용할 수 있도록 동적으로 매개 변수를 넘겨서 사용합니다.

Option Explicit
 
'//   mySQL Connector/ODBC Driver 설치                         //'
'//      https://dev.mysql.com/downloads/file/?id=506041    : ODBC
'//      https://dev.mysql.com/downloads/file/?id=506231    : ADO
'//   strPath : DB가 있는 엑셀 파일이음                        //'
'//   strSQL : SQL문                                           //'
'//   pasteRange : 검색한 레코드를 붙여 넣을 범위              //'
'//                                                            //'
'//   1. init : MySQL connect까지 자동                         //'
'//   2. connectToMySQL() : MySQL 원격 서버에 접속 -> Conn     //'
'//   3. ...                                       -> rs       //'
 
Private strConn As String
Public Conn As ADODB.Connection
Private rs As ADODB.Recordset
 
Private server As String
Private database As String
Private userID As String
Private password As String
Private port As String
Private result As Boolean
 
'  초기화
Public Function init() As Boolean
   result = IIf(ThisWorkbook.Worksheets("SCHEMA").CustomProperties.Count <= 0, False, True)
   If result = False Then
      result = setMySQLPropertiesToWorksheet()
   End If
 
   Call connectToMySQL
   init = result
End Function
 
' Custom Property에 MySQL 정보 담음
Private Function setMySQLPropertiesToWorksheet() As Boolean
   server = "서버부소입력"
   database = "데이터베이스 이름"
   userID = "사용자 아이디"
   password = "비밀번호"
   port = "3306"
 
   On Error GoTo SetPropertiesError
   With ThisWorkbook.Worksheets("SCHEMA").CustomProperties
      .Add NAME:="server", Value:=server
      .Add NAME:="database", Value:=database
      .Add NAME:="userID", Value:=userID
      .Add NAME:="password", Value:=password
      .Add NAME:="port", Value:=port
   End With
 
   setMySQLPropertiesToWorksheet = True
   Exit Function
 
SetPropertiesError:
   setMySQLPropertiesToWorksheet = False
End Function
 
'Custom Property 이름으로 값 반환하기
Public Function GetCustomPropertyValue(cpName) As Variant
   Dim cp As CustomProperty
 
   For Each cp In ThisWorkbook.Worksheets("SCHEMA").CustomProperties
      If cp.NAME = cpName Then GetCustomPropertyValue = cp.Value: Exit Function
   Next
   GetCustomPropertyValue = ""
End Function
 
' Custom Property 정보 보여주기
Public Sub showMySQLPropertiesInWorksheet()
   If ThisWorkbook.Worksheets("SCHEMA").CustomProperties.Count <= 0 Then Exit Sub
 
   Dim cp As CustomProperty
   For Each cp In ThisWorkbook.Worksheets("SCHEMA").CustomProperties
      Debug.Print cp.NAME & ": " & vbTab & cp.Value
   Next cp
End Sub
 
' Custom Property에 MySQL 정보 삭제
Public Function deleteMySQLPropertiesInWorksheet()
   If ThisWorkbook.Worksheets("SCHEMA").CustomProperties.Count <= 0 Then Exit Function
 
   Dim cp As CustomProperty
   For Each cp In ThisWorkbook.Worksheets("SCHEMA").CustomProperties
      cp.Delete
   Next cp
End Function
 
 
'  MySQL에 연결하기 - Conn
Public Function connectToMySQL() As Boolean
 
   server = GetCustomPropertyValue("server")
   database = GetCustomPropertyValue("database")
   userID = GetCustomPropertyValue("userID")
   password = GetCustomPropertyValue("password")
   port = GetCustomPropertyValue("port")
 
   strConn = "Driver={MySQL ODBC 8.0 Unicode Driver};Server=" & server & ";" & _
             "Port= " & port & ";" & _
             "Database=" & database & ";" & _
             "Uid=" & userID & ";" & _
             "Pwd=" & password & ";"
 
   On Error GoTo Error_OpenMySQL
      Set Conn = New ADODB.Connection
      Conn.Open strConn
   On Error GoTo 0
 
   connectToMySQL = True
   Exit Function
 
Error_OpenMySQL:
   MsgBox Err.Description
   connectToMySQL = False
End Function
 
'  MySQL 연결 종료
Public Function disconnectMySQL() As Boolean
   Conn.Close
   Set Conn = Nothing
   disconnectMySQL = True
End Function
 
' SQL문으로 실행 : INSERT, UPDATE, DELETE
Public Function ExecMySQL(strSQL As String) As Variant
   Dim aff As Long
   Call Conn.Execute(strSQL, aff)
   If aff > 0 Then ExecMySQL = aff: Exit Function
   ExecMySQL = False
End Function
 
' SQL문으로 실행 : SELECT 시 rs.recordcount=-1로 반환되는 단점이 있다. affectedRow 속성을 같이 반환한 방법을 생각해야 할 듯
Public Function ExecMySQL_Recordset(strSQL As String) As Recordset
   Dim aff As Long
   'Dim rs As ADODB.Recordset
   'Set rs = Conn.Execute(strSQL, aff)
   'Debug.Print rs.RecordCount
   Set ExecMySQL_Recordset = Conn.Execute(strSQL, aff)
End Function
 
Public Function Select_recordset(strSQL As String) As Recordset
   Dim rs As New ADODB.Record
   rs.CursorLocation = adUseClient
   rs.ActiveConnection = MySQL.Conn
   rs.Source = strSQL
   rs.Open
 
   Debug.Print rs.RecordCount
   If rs.RecordConut = 0 Then Set Select_recordset = Nothing
   Set Select_recordset = rs
End Function
 
 
Public Function Insert_Array(ByVal tbName As String, arrFields, arrValues) As Variant
 
   On Error GoTo ErrornNsert
      'rs.Open tbName, Conn, adOpenDynamic, adLockOptimistic, adCmdTable
      Dim rs As New ADODB.Recordset
      rs.ActiveConnection = Conn
      rs.CursorType = adOpenDynamic
      rs.LockType = adLockOptimistic
      rs.Open tbName, , , , adCmdTable
 
      rs.AddNew arrFields, arrValues
      rs.Update
 
      Dim id: id = Conn.Execute("SELECT @@Identity")(0)
 
      rs.Close
      Set rs = Nothing
      MsgBox "Successfully INSERTED into '" & tbName & "'." & vbNewLine & "id: " & id, vbInformation
      Insert_Array = id
 
      Exit Function
 
ErrornNsert:
   MsgBox Err.Description
   rs.Close
   Set rs = Nothing
   Insert_Array = False
End Function
 
Public Function Update_Connection(strSQL As String) As Boolean
   Dim aff As Long
 
   On Error GoTo Error
      Call Conn.Execute(strSQL, aff)
      If aff > 0 Then MsgBox aff & " record is updated..."
   On Error GoTo 0
 
   Update_Connection = True
   Exit Function
Error:
   MsgBox Err.Description
   Update_Connection = False
End Function
 
Public Function Update_Recordset(ByVal tbName As String, ByVal id As Long, arrFields, arrValues) As Boolean
 
   On Error GoTo Error
      Dim rs As New ADODB.Recordset
      rs.ActiveConnection = Conn
      rs.CursorType = adOpenDynamic
      rs.LockType = adLockOptimistic
      rs.Open tbName, , , , adCmdTable
 
      Dim criteria As String: criteria = "id = " & id
      rs.MoveLast
      rs.Find criteria, , adSearchBackward
      rs.Update arrFields, arrValues
      MsgBox "UPDATED!!!"
 
      Update_Recordset = True
   On Error GoTo 0
   Exit Function
 
Error:
   MsgBox Err.Description
   Update_Recordset = False
End Function
 
 
Public Function Delete_Using_Conn(ByVal id As Long, ByVal tbName As String) As Boolean
   Dim strSQL As String
   Dim affected As Long
 
   strSQL = "DELETE FROM " & tbName & " WHERE id = " & id
 
   On Error GoTo Error
      Conn.Execute strSQL, affected, adExecuteNoRecords
      'Debug.Print affected
      If affected > 0 Then
         MsgBox "DELETED..."
         Delete_Using_Conn = True
      End If
      'Delete_Using_Conn = IIf(affected > 0, True, False)
 
   On Error GoTo 0
   Exit Function
Error:
   MsgBox Err.Description
   Delete_Using_Conn = False
End Function
 
 
Public Function Table_Insert(tbName As String, tb As ListObject, Optional includeIndex As Boolean = False) As Boolean
   Dim id As Variant, i As Long, j As Long
 
   'DB Fields 이름 담아 둠
   Dim arrFields() As Variant, arrValues() As Variant
   arrFields = RangeToArray(tb.Range.Rows(1), , includeIndex)
 
'   For i = 1 To UBound(arrFields)
'      Debug.Print i & ": " & arrFields(i) '& ", " & arrValue(i)
'   Next i
 
   '테이블 총 행/열의 갯수 구함
   Dim cntRows As Long, cntCols As Long
   cntRows = tb.DataBodyRange.Rows.Count
   cntCols = tb.DataBodyRange.Columns.Count
 
 
   '테이블 루프를 돌면서 삽입 | 갱신을 수행함
   On Error GoTo Error
   Dim rs As New ADODB.Recordset
   rs.ActiveConnection = Conn
   rs.CursorType = adOpenDynamic
   rs.LockType = adLockOptimistic
   rs.Open tbName, , , , adCmdTable
 
 
   For i = 1 To cntRows
      id = tb.DataBodyRange.Rows(i).Cells(1, 1).Value
 
      If IsNumeric(id) Then GoTo Continue
 
      'row 값 읽어 들이기
      arrValues = RangeToArray(tb.DataBodyRange.Rows(i), , includeIndex)
      rs.AddNew arrFields, arrValues
Continue:
   Next i
 
   rs.UpdateBatch
 
   rs.Close
   Set rs = Nothing
 
   Table_Insert = True
   Exit Function
 
Error:
   MsgBox Err.Description
   Table_Insert = False
 
End Function
 
' // rng: 종적 배열로 만들 범위
' // tpose: 횡적 배열을 종적 배열로 변환 <- 보통 row로 데이터가 있기 때문에 defaul로 True로 설정 해 놓음
' // includeIndex: 첫행이 id or index이면 포함 할 지 여부
' // 보통 첫행이 DB의 id 부분이라 default로 Flase값을 설정 해 놓음
 
Public Function RangeToArray(rng As Range, Optional tpose As Boolean = True, Optional includeIndex = False)
   Dim arr()
   Dim arrResult()
   Dim i As Long, index As Long, endIndex As Long
   arr = IIf(tpose, Application.Transpose(rng.Value), rng.Value)
 
'   Debug.Print "rng LBound Value Columns: " & LBound(rng.Value, 1) : 헤더 포함 행의 개수
'   Debug.Print "rng UBound Value Columns: " & UBound(rng.Value, 2) : 칼럼의 개수
 
   If includeIndex = False Then
      ReDim arrResult(1 To UBound(arr, 1) - 1)
      index = 2
      endIndex = UBound(arr, 1) - 1
   Else
      ReDim arrResult(1 To UBound(arr, 1))
     index = 1
      endIndex = UBound(arr, 1)
   End If
 
   Dim tmp
   For i = 1 To endIndex
      tmp = VBA.Trim(arr(index, 1))
      If tmp = "" Then tmp = Null
      arrResult(i) = tmp
Continue:
      index = index + 1
   Next i
   RangeToArray = arrResult
End Function

 

1. Instance 생성 및 초기화 함수

'  MySQL Connection 연결(True) / 끊기(False)
Public Sub setMySQL(Optional opt As Boolean = True)
   On Error GoTo Error
 
      If MySQL Is Nothing And opt = True Then
         Set MySQL = New clsMySQL
         MySQL.init
      End If
 
      If Not MySQL Is Nothing And opt = False Then
         MySQL.disconnectMySQL
         Set MySQL = Nothing
      End If
   On Error GoTo 0
   Exit Sub
 
Error:
   MsgBox Err.Description
End Sub

 

2. 실제 사용하기   

   Call mdGlobal.setMySQL(True)
     ' 코드 구현
     ' 예시:  If MySQL.Update_Recordset(db.CA, id, arrFields, arrValues) = True Then MsgBox "Updated ..."
   Call mdGlobal.setMySQL(False)

   - mdGlobal 이라는 모듈안에 함수를 넣어 놓아서 mdGlobal.setMySQL 이리 표현한 것입니다.

관심이 있는 분들은 클래스 먼저 살펴 보세요.

recordset을 어떻게 다루는가에 대한 정보를 얻을 수 있을 겁니다.

시간 나는 데로 나머지 CRUD 과정 관련된 코드도 올리겠습니다.

회원등급 : 나무2단계
포인트 : 2923 EP
전체 6

  • 2021-10-08 09:04

    감사합니다.


  • 2021-10-14 17:37

    이걸 사람이 할 수 있는거군요 ...;


  • 2021-10-12 01:37

    정말 공부하고 싶은 내용이네요

    기초부터 하나씩 밟고 나가야 하는데.. VBA도 벅차다보니.. 가야 할 길이 참 멉니다


    • 2021-10-12 22:02

      정말 오래 걸립니다. 해도 해도 끝이 없습니다.

      그리고 이런 부분은 VBA로 명확하게 설명해 주시는 유튜버나 책자들이 없어 맨땅에 헤딩하는 마음으로 할 수 밖에 없습니다.

      게다가 동기 부여가 없으면 하기 싫은 것이 인간인지라...

      일단 이해하고 나면, 효용 가치는 무궁무진합니다.

      이 부분 이해하고 나니 엑셀을 어떻게 핸들링하는 것이 이상적인가에 한걸음 다가 섰습니다.

      특히 웹하고 핸드폰, 그리고 PC 세군데서 수십 수백명이 같은 자료를 보고, 수정하고 하는 것이 모두 가능해 집니다.


      • 2021-10-17 12:28

        하루종일 붙잡고 드디어 하나 마스터했습니다~~^^ㅋㅋ 감사합니다


        • 2021-10-17 16:43

          고생하셨습니다.

          글만 가자고 다른 사람의 생각을 이해 한다는 것이 보통 어려운 일이 아닌데.

          하물며, 암호같은 코드를 ㅎㅎㅎ

          CRUD파트와 연관해서 보세요. CRUD가 모두 이 MySQL class를 이용해서 하는 것입니다.


전체 44
번호 제목 작성자 작성일 추천 조회
41
New [잡설] 웹 서버에서 내보내는 엑셀 파일 및 데이터 이야기 (2)
dra**** | 2021.10.25 | 추천 0 | 조회 19
dra**** 2021.10.25 0 19
40
[엑셀 VBA] 파워쿼리 자동화 4. CRUD (2)
dra**** | 2021.10.12 | 추천 1 | 조회 85
dra**** 2021.10.12 1 85
39
[엑셀 VBA] 파워쿼리 자동화 3. MySQL Class (6)
dra**** | 2021.10.07 | 추천 0 | 조회 111
dra**** 2021.10.07 0 111
38
[엑셀 VBA] 파워쿼리 자동화 2. 자동화 실습 (3)
dra**** | 2021.09.28 | 추천 0 | 조회 146
dra**** 2021.09.28 0 146
37
[엑셀 VBA] 개체 Reference - QueryTable Object 첨부파일
dra**** | 2021.09.26 | 추천 0 | 조회 67
dra**** 2021.09.26 0 67
36
[엑셀 VBA] 파워쿼리 자동화 1. 자동화 순서 (7)
dra**** | 2021.09.23 | 추천 0 | 조회 224
dra**** 2021.09.23 0 224
35
[엑셀 VBA] 개체 Reference - ADODB.Recordset : Database 필수 첨부파일 (3)
dra**** | 2021.09.16 | 추천 1 | 조회 167
dra**** 2021.09.16 1 167
34
[잡설] IT가 흘러가고 있는 방향에 대한 단상 - 무엇을 공부해야 하는가? (14)
dra**** | 2021.09.16 | 추천 2 | 조회 143
dra**** 2021.09.16 2 143
33
[엑셀 VBA] MySQL 02. Range -> 배열 -> INSERT (4)
dra**** | 2021.09.16 | 추천 0 | 조회 118
dra**** 2021.09.16 0 118
32
[엑셀 VBA] MySQL 01. 워크시트 Custom Properties 활용하기 (3)
dra**** | 2021.09.14 | 추천 1 | 조회 84
dra**** 2021.09.14 1 84
31
[엑셀 VBA] 개체 Reference - ListObject Object - [표]가지고 놀기 첨부파일 (10)
dra**** | 2021.08.29 | 추천 4 | 조회 165
dra**** 2021.08.29 4 165
30
[엑셀 VBA] Windows API - winuser.h : 윈도우즈 프로그램의 핵심 첨부파일 (8)
dra**** | 2021.08.17 | 추천 3 | 조회 161
dra**** 2021.08.17 3 161
29
[잡설] 엑셀이 웹으로 가야 하는 이유 (20)
dra**** | 2021.08.16 | 추천 10 | 조회 343
dra**** 2021.08.16 10 343
28
[엑셀 VBA] 개체 Reference - FileSystem Object 첨부파일 (3)
dra**** | 2021.08.15 | 추천 1 | 조회 114
dra**** 2021.08.15 1 114
27
[엑셀 VBA] 데이터 자동화 - 웹에서 제공하는 MySQL Sever를 이용한 자동화 개념 (13)
dra**** | 2021.08.05 | 추천 1 | 조회 281
dra**** 2021.08.05 1 281
26
[엑셀 VBA] 개체 Reference - APPLICATION Object 첨부파일 (1)
dra**** | 2021.08.05 | 추천 0 | 조회 99
dra**** 2021.08.05 0 99
25
[엑셀 VBA] 개체 Reference - WORKBOOKS, WORKBOOK Object 첨부파일 (4)
dra**** | 2021.07.25 | 추천 2 | 조회 149
dra**** 2021.07.25 2 149
24
[엑셀 VBA] 개체 Reference - WORKSHEETS, WORKSHEET Object 첨부파일 (5)
dra**** | 2021.07.11 | 추천 1 | 조회 180
dra**** 2021.07.11 1 180
23
[엑셀 VBA] 개체 Reference - RANGE Object 첨부파일 (4)
dra**** | 2021.07.06 | 추천 3 | 조회 178
dra**** 2021.07.06 3 178
22
[엑셀 VBA] 자동화 - 엑셀 데이터베이스의 기본 작동 원리 및 구현 방법 (16)
dra**** | 2021.06.28 | 추천 4 | 조회 587
dra**** 2021.06.28 4 587