엑셀과 MySQL 연동처리

엑셀과 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 & "');"