웹서핑을 하다가.. 우연히 발견하여 일단 쟁겨놓습니다.


출처 : 

1. http://link2me.tistory.com/421

2. http://link2me.tistory.com/485



VBA 와 MYSQL 연동을 위한 준비



엑셀에서 직접 MYSQL DB 에 있는 데이터를 불러오는 방법입니다.

'VBA SQL 연동' 이라고 검색해서 찾아보셔도 됩니다.


가장 먼저 해야 할 사항은 MySQL Connector ODBC 5.1

파일을 설치하는 겁니다.


unknown.gif mysql-connector-odbc-5.1.8-win32.msi


위 파일을 다운로드 받아서 실행하세요.


273E984453D37ADE0369E7


2533A74453D37ADE141E5C


다음(Next)를 눌러주기만 하면 설치가 완료됩니다.


제어판에서 설치된 것을 확인해 봅니다.


2164104E53D37C4325B6DB


246B2B4E53D37C431B4618


2358F04E53D37C44323620


설치되어 있는 것을 확인했습니다.


이제 엑셀을 띄워서 VBA 에서 확인을 합니다.


253EDF4B53D37C81244DF5


223E434B53D37C8227DDE7


위와 같이 체크되어 있는지 확인을 합니다.

VBA 파일내에서 위와 같이 여러개의 값이 설정되어 있어야 합니다.

그리고 반드시 MYSQL 원격접속이 허용되어 있어야만 가능합니다.

보통은 보안상의 설정 때문에 localhost 로 설정되어 있습니다.


이제부터는 VBA 코드에 연결할 작업을 해야 합니다.







엑셀과 MySQL 연동처리


엑셀과 MySQL 연동처리를 하기 위한 기초 설명은 http://link2me.tistory.com/421 참조하면 됩니다.

기본적인 환경설정을 위한 정보는 다 설정되었다고 가정하고 추가적인 걸 설명하겠습니다.

MySQL DB 설정에서 DB.Table 을 % 권한을 부여하면 외부 엑셀에서 접속이 가능합니다.

변수선언, DB연결, DB open 하고 테이블의 Column 가져다가 엑셀에서 작업, DB close 하는 순서로 코드가 작성됩니다. 아래 코드는 개념적인 이해를 돕는데 사용하려고 인터넷 자료를 이것 저것 참조하고 짜집기를 좀 한 것입니다. SQL 을 다루는 것이므로 SQL 에 대한 공부가 좀 선행되어야 합니다.

엑셀 Cell 에 있는 값을 조건으로 SQL 문의 WHERE 조건을 걸 때 변수를 어떻게 입력하는지 아셔야 연동하여 원하는 작업을 할 수 있습니다.

저는 MySQL 과 연동하여 MySQL 이라고 했지만 다른 DB와도 연동이 되며, 엑세스와도 연동이 가능합니다.


Sub getMySQLData()

    Dim DBconn As ADODB.Connection

    Dim dbRecset As ADODB.Recordset

    Dim sSQL As String

    Dim iRow As Long, n As Long


    Set DBconn = New ADODB.Connection

    DBconn.ConnectionString = "DRIVER={MySQL ODBC 5.1 Driver};" & _

                            "SERVER=localhost;" & _

                            "PORT=3306" & _

                            "DATABASE=test;" & _

                            "UID=testname;PASSWORD=testpasswd;OPTION=3"   

 

    DBconn.Open  '// 실제 DB 접속

'// 테이블에서 가져온 데이터의 조건을 걸어서 검사하고 싶다면 For Each 문을 여기에 설정

    '// 테이블에서 가져올 Column 을 SELECT 한다. 

    sSQL = "SELECT * FROM tblName Where 조건"     

 

    '// Create a recordset and set the CursorLocation property for record navigation

    Set dbRecset = New ADODB.Recordset

    dbRecset.CursorLocation = adUseClient

 

    '// MySQL DB 데이터 가져오기

    dbRecset.Open Source:=sSQL, ActiveConnection:=conn, CursorType:=adOpenForwardOnly, _

                  LockType:=adLockReadOnly, Options:=adCmdText


   dbRecset.MoveFirst   '// MySQL 가져온 데이터의 첫번째 열로 이동하라

 

    '//  첫번째 열의 값을 Cells 에 저장하라 

    For n = 1 To dbRecset.Fields.Count 

        Worksheets(1).Cells(1, n).Value = dbRecset.Fields(n - 1).Name 

    Next n

 

    '// MySQL에서 가져온 데이터를 엑셀 시트에 저장 

    For iRow = 1 To dbRecset.RecordCount   '// Record(행) 수

        For n = 1 To dbRecset.Fields.Count   '// Fields(열) 수

            Worksheets(1).Cells(iRow + 1, n) = dbRecset.Fields(n - 1)

         Next n

         dbRecset.MoveNext

     Next iRow

'// For Each 문의 Next rngC

'//  접속 종료

    dbRecset.Close

    DBconn.Close


    Set dbRecset = Nothing

    Set DBconn = Nothing 

End Sub


Where 조건을 줄 때 어떻게 하는지 한번 살펴보자.

엑셀 셀의 변수를 어떻게 주었는지 주의해서 보셔야 합니다.


strSQL = "select 전화번호 from DB테이블명 "
strSQL = strSQL & "WHERE RIGHT(전화번호,4)='" & myTel & "' "
strSQL = strSQL & "ORDER BY RIGHT(전화번호,4) "


strSQL = "SELECT 품명, SUM(수량) AS 수량합  "
strSQL = strSQL & "FROM
DB테이블명 "
strSQL = strSQL & "WHERE 입고일>='" & dStart & "' AND  입고일<='" & dEnd & "'  "
strSQL = strSQL & "GROUP BY  품명 "

sSQL = "INSERT INTO `info` VALUES (" & i & ",'" & Text1.Text & "','" & Text2.Text & "');"





profile