[엑셀 VBA] 예약 DB - 4. 표 가지고 놀기

작성자
dra****
작성일
2022-10-13 18:10
조회
207

1. 시트상의 표를 VBA로 구현할 때는 ListObject 객체로 합니다.
표의 이름을 tbBook이라 해 보겠습니다.

Dim lobj as ListObject
set lobj = Worksheets('book').ListObjects('tbBook')

이렇게 해면 인스턴스가 생성이 됩니다.

 

2. 테이블의 전체 행(rows, records) 갯수 구하기

Dim cntRows as Long
cntRows = lobj.ListRows.Count

 

3. DB에 들어갈 각각의 칼럼(필드) 이름을 배열로 정의하기

Dim arrFields(), arrValues()
arrFields = Array("Ser", "MEM_Ser", "GCODE", "GUIDE", "GUEST", "GC_Ser", "PAX", "TOFF_DATE", "WEEK_DAY", "HOLES", "SELL_RATE", "SELL_TOTAL", "R_TIME", "RECHECK_DATE", "A_TIME", "GUEST_CONFIRM", "BOOK_DATE", "CONFIRM_CODE", "VCHER_DATE", "MEMO", "PROGRESS_Ser", "SELL_PAY_DATE", "NET_RATE", "INCOME", "NET_TOTAL", "MODIFIED", "ADMIN_Ser", "RECHECK", "LOC_INVOICE")

이렇게 배열로 정해 놓고 반복문을 통해 각각의 시트 행의 들어 있는 값을 구해서 arrValues()에 넣어 둡니다.

lobj.HeaderRowRange를 이용하는 방법도 있는데, 이런 경우는 원하는 칼럼만 골라내지 못하고 전체를 넣어야 합니다.

칼럼 이름은 MySQL 서버상의 테이블에 있는 칼럼 이름과 반드시 같아야 합니다.

참고) Dictionary를 다룰 줄 아신다면 이게 더 편리하고 빠를 수도 있습니다.

 

4. 이제 각행을 돌면서 값을 배열에 담아 SQL문으로 만들어 MySQL서버에 INSERT/UPDATE 를 합니다.

    Dim rows as long
    Dim col as Byte, colIndex as Byte
 
    For rows = 1 To lobj.ListRows.Count
        For col = LBound(arrFields) To UBound(arrFields)
	'칼럼 인덱스 구하기
            colIndex = lobj.ListColumns(arrFields(col)).index
	'현재 행의 해당 칼럼의 값을 대입하기
            val = lobj.ListRows(rows).Range(, colIndex).Value
 
            If arrFields(col) = "Ser" Then serial = val
	'날짜형 데이터면 포맷함수로 설정
            If arrFields(col) = "TOFF_DATE" Or arrFields(col) = "RECHECK_DATE" Or arrFields(col) = "BOOK_DATE" Or arrFields(col) = "SELL_PAY_DATE" Then
                val = VBA.Format(val, "yyyy-mm-dd")
            End If
 
	'MySQL로 넘길때 Boolean Type이 없으므로 0 또는 1로 변환하기            
            If arrFields(col) = "GUEST_CONFIRM" Or arrFields(col) = "RECHECK" Then
                val = mdUtils.BooleanToInt(val)
            End If
 
            arrValues(col) = val
        Next col
 
        '레코드 존재 여부 확인
        criterion = " WHERE Ser='" & serial & "';"
        result = Record_Exists("book", criterion)
 
        '레코드가 존재하면 UPDATE
        If result = True Then
            strSQL = makeSQL("update", "book", arrFields, arrValues)
            result = mySQL.mysql_ExecSQL_strSQL(strSQL)
            If result = True Then
	    '시트에 상태 표시하기	
                lobj.ListRows(rows).Range(, lobj.ListColumns.Count + 2).Value = "UPDATED"
                Debug.Print serial & ": UPDATED"
            Else
                lobj.ListRows(rows).Range(, lobj.ListColumns.Count + 2).Value = "UPDATE FAILED"
                Debug.Print serial & ": UPDATE FAILED"
            End If
        Else
        '레코드가 존재하지 않으면 INSERT
            strSQL = makeSQL("insert", "book", arrFields, arrValues)
 
            result = mySQL.mysql_INSERT_strSQL(strSQL)
            If result > -1 Then
	    '시트에 상태 표시하기
                lobj.ListRows(rows).Range(, lobj.ListColumns.Count + 2).Value = "INSERTD: " & result
                Debug.Print serial & ": INSERTD: " & result
            Else
                lobj.ListRows(rows).Range(, lobj.ListColumns.Count + 2).Value = "INSERT FAILED"
                Debug.Print serial & ": INSERT FAILED"
            End If
        End If
    Next rows
 
    If Not mdGlobal.mySQL Is Nothing Then Set mySQL = Nothing
 
Error_Section:
    Debug.Print Err.Description
    If Not mdGlobal.mySQL Is Nothing Then Set mySQL = Nothing

시트의 테이블 옆에 결과값을 표기하도록 해서, 오류가 있는 행들에 대해서는 따로 작업을 해야 합니다.

다음에는 makeSQL() 함수에 대해서 포스팅 하겠습니다.

매개변수로 넘겨진 arrFields()와 arrValues()를 가지고 반복문을 돌면서 만드는 것인데, 그리 어렵지는 않습니다.

 

 

 

스크랩
공유
회원등급 : 29레벨
포인트 : 4281 EP
전체 6

  • 2022-10-16 20:15

    @dra**** 님 항상 유익한 고급정보를 깔끔하게 공유해주셔서 감사드립니다!!^^


    • 2022-10-17 20:58

      @오빠두엑셀 님 별말씀을 ^^

      모두가 시트가 아닌 DB에 데이터를 두고 CRUD를 구현하는 그날까지... 열심히 포스팅


      • 2022-10-18 02:35

        @dra**** 님 확실히 요즘은 클라우드 서버가 저렴한 버전으로 많이 제공되고,

        대부분의 클라우드 플랫폼에서 1년 정도 무료 테스트가 가능한 플랜을 제공하고 있어서, 예전보다는 서버를 설치하고 구축하는 과정이 많이 편리해진 것 같습니다. 앞으로 엑셀도 클라우드에 많이 연동되면, 서버의 중요성이 더 커지겠죠? 🙂 ㅎㅎ

        앞으로도 좋은 포스팅 부탁드리겠습니다!!


  • 2022-11-08 11:41

    @dra**** 님 안녕하세요 엑셀 데이터 내용을 db에 insert 하는 내용으로 이해하고 보고 있는데요.

    database 연결하여 구문넣는거 까지 연결이 어려워서.. 글을 쓰게 되었습니다.

    실례지만 전체 코드 공유가 가능한지 문의드립니다.


    • 2022-11-09 00:30

      @열번 님 다음 포스팅때 전체 소스 공유 할게요. ^^


      • 2022-11-10 09:14

        @dra**** 님 감사합니다. 실습날을 기다리겠습니다.


전체 69
번호 제목 작성자 작성일 추천 조회
알림
🎉 오피스 분야 30주 연속 1위! - 「 진짜쓰는 실무엑셀 」 전자책이 출간되었습니다! (37)
오빠두엑셀 | 2022.09.28 | 추천 9 | 조회 3206
오빠두엑셀 2022.09.28 9 3206
공지사항
[VBA] 구하라 그러면 주어질 것이다. - VBA 마스터 E-Book (영문판) 첨부파일 (6)
dra**** | 2022.07.19 | 추천 4 | 조회 372
dra**** 2022.07.19 4 372
44293
[엑셀 VBA] 예약 DB - 9. 시트의 바우처&견적서 양식에 데이터 뿌리기 (3)
dra**** | 2022.11.11 | 추천 1 | 조회 166
dra**** 2022.11.11 1 166
44227
[엑셀 VBA] 예약 DB - 8. Listbox 와 폼 컨트롤 연동하고 DB로 보내기
dra**** | 2022.11.09 | 추천 2 | 조회 79
dra**** 2022.11.09 2 79
44219
[엑셀 VBA] 예약 DB - 7. MySQL 클래스와 사용법 (2)
dra**** | 2022.11.09 | 추천 2 | 조회 75
dra**** 2022.11.09 2 75
43438
[엑셀 VBA] 예약 DB - 6. 폼 콘트롤과 DB 필드명 일치시키기
dra**** | 2022.10.21 | 추천 2 | 조회 111
dra**** 2022.10.21 2 111
43292
[엑셀 VBA] 예약 DB - 5. makeSQL() 함수 - INSERT, UPDATE문 자동 생성 (4)
dra**** | 2022.10.18 | 추천 3 | 조회 131
dra**** 2022.10.18 3 131
43091
[엑셀 VBA] 예약 DB - 4. 표 가지고 놀기 (6)
dra**** | 2022.10.13 | 추천 1 | 조회 207
dra**** 2022.10.13 1 207
43017
[엑셀 VBA] 예약 DB -3. 표(테이블, ListObject)을 사용해야 하는 이유1 (11)
dra**** | 2022.10.11 | 추천 1 | 조회 163
dra**** 2022.10.11 1 163
42998
[잡설] 엑셀이 웹으로 가야하는 이유2 (9)
dra**** | 2022.10.10 | 추천 5 | 조회 272
dra**** 2022.10.10 5 272
42982
[엑셀 VBA] 예약 DB - 2. 다시 웹으로 - Access에서 MySQL로 데이터 이식
dra**** | 2022.10.10 | 추천 2 | 조회 139
dra**** 2022.10.10 2 139
40590
[엑셀 VBA] 예약 DB - 1. 엑세스, SQL, Recordset, ListObject 의 상관 관계 (4)
dra**** | 2022.07.29 | 추천 3 | 조회 355
dra**** 2022.07.29 3 355
40511
[잡설] 엑셀의 꽃 파워 쿼리를 버리다. (3)
dra**** | 2022.07.29 | 추천 4 | 조회 583
dra**** 2022.07.29 4 583
40321
[경험담..해결했습니다.] &H80004005(-2147467259)시스템오류 (3)
티엠프이 | 2022.07.25 | 추천 2 | 조회 295
티엠프이 2022.07.25 2 295
40074
M365 업데이트 이후 발생하는 H80004005 (-2147467259) 자동화 오류 해결방법
오빠두엑셀 | 2022.07.23 | 추천 0 | 조회 293
오빠두엑셀 2022.07.23 - 293
39608
엑셀 그래프에 관한 유튜브 채널정보 입니다. (7)
레몬네이드 | 2022.07.15 | 추천 -1 | 조회 219
레몬네이드 2022.07.15 -1 219
36473
[엑셀VBA] 시트 이름을 변수로 설정하고 싶을때 간단한 방법 (2)
알파고 | 2022.06.27 | 추천 2 | 조회 465
알파고 2022.06.27 2 465
35196
Print_Area 동적 인쇄 영역설정 첨부파일 (1)
sean | 2022.06.21 | 추천 4 | 조회 487
sean 2022.06.21 4 487
34607
셀레니움으로 개별공시지가 조회 첨부파일 (3)
나야 | 2022.06.02 | 추천 1 | 조회 298
나야 2022.06.02 1 298
26273
엑셀 2021, M365 가로스크롤 기능 (28)
더블유에이 | 2022.01.23 | 추천 17 | 조회 851
더블유에이 2022.01.23 17 851
Re:엑셀 2021, M365 가로스크롤 기능
김학동 | 2022.01.23 | 추천 1 | 조회 280
김학동 2022.01.23 1 280
Re:엑셀 2021, M365 가로스크롤 기능
김동희 | 2022.05.30 | 추천 0 | 조회 131
김동희 2022.05.30 0 131
24189
[엑셀 VBA] Snippet - OCR: 이미지 파일을 텍스트 파일로 변환하기 (25)
dra**** | 2021.12.01 | 추천 8 | 조회 2138
dra**** 2021.12.01 8 2138
23999
[엑셀 VBA] Snippet - Public IP, Local IP, Mac Address 구하기 (6)
dra**** | 2021.11.25 | 추천 2 | 조회 781
dra**** 2021.11.25 2 781