[엑셀 VBA] 데이터 자동화 - 웹에서 제공하는 MySQL Sever를 이용한 자동화 개념

작성자
dra****
작성일
2021-08-05 14:56
조회
343

OneDrive와 SharePoint를 가지고 자동화 앱을 만들고 배포를 했습니다.
문제는 각각의 PC에서 돌아가는 엑셀 파일들이 파워쿼리처럼 데이터를 불러오고 가공해서 보여주는 것은 문제가 아닌데, 여러 사용자가 접속시, 다중 업데이트가 되지 않는 다는 문제점이 생긴 것입니다.

이 부분을 해결하려 많은 문서들과 포럼, 커뮤니티들을 검색했음에도 해결책이 딱히 없더군요. 혹시 아시는 분 계시면 조언을 부탁합니다.

그래서 생각해 낸 방법이, ADODB.ConnectionConnection String을 보면 MySQL 서버에 연결할 수 있는 방법이 있어 시도를 해 보았습니다.

몇 번의 시행착오를 거친 끝에 완벽하게 작동하는 것을 확인했습니다.

엑셀에서는 JSON파일로 변경할 필요도 없고 바로 SQL 명령문으로 CRUD가 완벽하게 제어가 됩니다.

조회하는 파트는 파워쿼리로 루틴을 짜서 자동 갱신만 하게 하고,
추가, 업데이트, 삭제와 같은 부분들만 코딩하니 손쉽게 모든 문제가 해결이 되었습니다.

웹사이트도 만들고, 안드로이드 앱도 만들고, 엑셀 앱도 만드느라 정신이 없네요.

궁극의 목적은 엑셀로 모든 사용자가 데이터를 관리하도록 하는 것이고,
앱은 PC를 사용할 수 없는 환경에서 사용하도록 하기 위함입니다.
굳이 웹 사이트를 만들 필요가 없지만, 사이트를 만들어 두면 그걸로 바로 안드로이드 앱으로 이식할 수 있는 장점이 있기 때문입니다.

이제 지저분하게 시트에 이것 저것 참조할 내용을 기록하지 않아도 됩니다. ㅎㅎㅎ

다만 VBA로 코딩 한 줄 할 때마다 습관적으로 ";"을 붙이는 부작용이 생기네요. ㅠㅠ

완료되면 파워쿼리 자동화와 갱신, MySQL 서버에 연결해서 CRUD를 구현 하는 방법을 공유하도록 하겠습니다.

 

전체 15

  • 2021-08-06 03:13

    맞습니다. 올초에 팀즈랑 파워앱스로 연동하면서 테스트용으로 엑셀을 백엔드로 사용한 적이 있는데, 이상하게도 원드라이브 클라우드에 올려져 있어도 엑셀은 simutaneous-read 가 안되더군요. 엑셀 프로그램 자체상 CRUD를 하려면 반드시 파일에 엑세스를 해야해서 그런 것으로 이해했었는데..

    그 당시 테스트용으로만 엑셀을 사용했고 (메일 수신 -> 엑셀 작성 -> 엑셀 파일 발송) 실제 배포할 때는, DB를 서버에서 따와서 배포했었습니다. 시도는 안해봤지만 그 당시 문의했던 MS 직원분 안내로는 쉐어포인트도 동시처리가 가능하다고 하고, 캐파는.. 예상에 웹 엑셀과 비슷한 수준?일 것으로 보입니다.

    ADODB로도 처리해볼까 고민도 했었는데, 알아보니 파워앱스와 오토메이트가 연동이 되는겁니다. 그래서 아웃룩 > 엑셀 > DB 는 오토메이트로, DB >프론트는 자바로 무사히 처리할 수 있었습니다


  • 2021-08-12 12:55

    어렵네요...


  • 2021-11-10 11:33

    좋은 정보 감사합니다.


  • 2021-08-08 02:51

    여긴 초보자가 없고 모두 전문가인가요?...무슨 말인지 하나도 못 알아듣겠습니다....앞으로 공부 열심히 해야 될 듯합니다.


    • 2021-08-08 03:07

      @hanwh**** 님 엑셀의 경험 단계가 있습니다. 처음에는 함수 가지고 놀다가, 자동화에 눈을 뜨면 VBA를 할 수 밖에 없습니다.

      그 단계가 넘어가면 Web으로 갑니다. 그럼 지금의 이 말을 이해하실 수 있습니다.


      • 2021-08-15 23:55

        @dra**** 님 저도 비슷한 단계에서 하나씩 개념정리를 하고 있는데요 ,
        공유해주신다는게 기다려지네요 ..ㅎㅎ

        혹시 엑셀 파일에서 보안은 어떻게 적용하셨느지 문의드려도 될까요 ?

        홈페이지를 만들으셨다고 하셨는데, 홈페이지의 계정으로 엑셀에서도 로그인하는 방식으로도 가능할까요 ?

        엑셀만 있으면 누구나 db에 데이터를 입력할 수 있는 것이 아니라 권한 제한을 두고 싶은데,

        어떻게 둬야 할지 고민이 되네요 ㅎ


        • 2021-08-16 23:16

          @센츠 님 엑셀 폼으로 로그인하고, 데이터베이스 자체에 접근 제한을 둡니다.

          엑셀에서  mySQL 서버로 접근하는 경우 로그인 체크 루틴을 계속 사용하고, 웹의 경우 session을 사용중입니다. 사실 웹의 query String으로 모두 처리하려 했는데, 굳이 보안이 심각하게 필요하지 않은 상황이라.

          꼼수로 CustomProperties를 사용하기도 하고,
          php에서 제공하는 hascode 생성 함수를 사용하기도 하고
          sha + 저만 이해할 수 있는 salt 를 쓰기도 합니다.

          조만간에 Golang으로 모두 이식하려 합니다. 포스팅 계획은 mySQL localhhost 이고,

          무료 hosting의 경우 간단한 소개만 하려 합니다. VBA도 버거운 분은 localhost까지만 가시면 되고,

          능력자 이시면 마소 sharepoint, onedrive의 문제점과 offfice.script의 한계점, power automate의 한계점. 파워쿼리 자동화까지 가려 합니다. 그리고 마소가 구현하고자 했던 본질을 SMTP, 엑셀, mySQL로 구현하는 것을 보여 드리고 싶은데, 글로 어디까지 보여 드릴수 있을까 하는 걱정이 앞섭니다. 구현이 모두 끝나고 실용화 되면 하나씩 포스팅 하겠습니다.

           


          • 2021-08-17 12:30

            @dra**** 님 포스팅은 오빠두 사이트에서 하시는걸까요 ??ㅎ

            제가 하고싶은것을 이미 구축을 다 하신분이시니.. 포스팅이 기다려지네요 ㅎㅎ

             

            웹쪽 지식도 없는건 아닌데, 하도 옛날에 해봣엇던거라..ㅎ

            알려주신 함수들 이용해서 한번 해봐야겠네요 ㅎㅎ

            감사합니다 🙂


  • 2021-11-22 11:20

    감사합니다


  • 2021-08-08 14:45

    굿


  • 2021-08-16 21:22

    너무 어려워요 ㅜㅜ 공부열심히해야겠네


  • 2021-08-16 22:21

    후아 공부는 끝이 없군요.... 인터넷 다음은 뭐가 될까요 ...? 이제 갇 공부하는 사람은 너무도 먼... 괴리감....T^T


  • 2021-08-06 17:52

    당사에 서버를 운영할 자원과 접근 권한만 있다면 SQL이 근본적인 해결은 맞지만

    현실이 그렇지 못해서 슬프죠 ㅜㅜ.. 대기업은 권한이 없고, 중소기업은 자원이 없고,... 뭐.. 그런 듯 합니다..ㅎㅎ

    그래서 결국 중소기업이라면 본인이 멀티플레이어로 수비부터 공격까지!!ㅋㅋ 다 하게 되는 것이겠죠?-0-;;


    • 2021-08-06 18:06

      @더블유에이 님 이상과 현실의 괴리네요... ㅎㅎㅎ

       


      • 2021-08-18 19:49

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

         


전체 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 | 조회 53
dra**** 2021.11.21 2 53
23337
HTML 라이브러리 사용하기 귀찮을 때, 사용하는 파싱함수 (getElementsByTag, getAttribute) (6)
트로피 오빠두엑셀 | 2021.11.09 | 추천 0 | 조회 70
트로피 오빠두엑셀 2021.11.09 0 70
23142
[잡설] 엑셀을 이용한 웹 자동화: SEO 이야기 (10)
dra**** | 2021.11.04 | 추천 0 | 조회 153
dra**** 2021.11.04 0 153
22802
[잡설] 웹 서버에서 내보내는 엑셀 파일 및 데이터 이야기 (6)
dra**** | 2021.10.25 | 추천 0 | 조회 111
dra**** 2021.10.25 0 111
22325
[엑셀 VBA] 파워쿼리 자동화 4. CRUD (3)
dra**** | 2021.10.12 | 추천 1 | 조회 149
dra**** 2021.10.12 1 149
22161
[엑셀 VBA] 파워쿼리 자동화 3. MySQL Class (8)
dra**** | 2021.10.07 | 추천 0 | 조회 169
dra**** 2021.10.07 0 169
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 | 조회 392
dra**** 2021.09.23 0 392
21496
[엑셀 VBA] 개체 Reference - ADODB.Recordset : Database 필수 첨부파일 (4)
dra**** | 2021.09.16 | 추천 1 | 조회 189
dra**** 2021.09.16 1 189
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 | 조회 201
dra**** 2021.09.16 0 201
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 | 조회 201
dra**** 2021.08.17 3 201
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