본문 바로가기
2011. 1. 29. 23:47

Excel에서 MySql연동 테스트

Excel 2007에서 Mysql MyODBC이용해서 Mysql과 연동 하는 방법 정리.


주목적.
회사일도 있고 엑셀로 편집해서 웹사이트 들어가서 엑셀 업로드해서 문자나 음성 예약하는거 불편해보여서
엑셀에서 vba이용 버튼 클릭으로 해당 row가 mysql 디비에 insert되게 하면 편할거 같아서.

테스트 시 환경 
 
1.Excel : Excel 2007 Portable Ko 
2.Mysql : 5.1.41(UTF-8). 
정도.



준비물

1.MyODBC Driver 
   http://dev.mysql.com/downloads/connector/odbc/5.1.html 
   여기가면 있다. 나같은 경우는 32bit에 zip받아서 배치파일로 복사했음.
   다운 눌리면 로그인 하라 하는데 귀찮아서
   링크 밑에 파일명 복사해서 미러링 사이트에서 받았음.
   하나는 설치본, 하나는 압축본
 
mysql-connector-odbc-noinstall-5.1.8-win32.zip
mysql-connector-odbc-5.1.8-win32.msi

2.드라이버 설치 했으면 특별히 할건 없다.
  제어판 -> 관리도구 -> 데이터 원본(ODBC)에 추가 해줄 필요는 없고 추가 시 아래 처럼 목록   에 생겼는가 정도만 확인.
   




   

시작해보자. 
 
1.Excel문서를 하나 만든다. 
2.Alt + F11로 VBA 개발 화면으로 간다. 
3.디비 접속 샘플 코드 입력해 본다.       
  아래는 디비 접속 하는 문장이고 별 문제 없으면 아무 메세지가 뜨지 않는다. 
  문제가 있으면 친절 하게 에러가 뜬다.  
  상콤하게 잡아주자.  
  
Sub getMyData()
    Dim conn As ADODB.Connection
     
    Set conn = New ADODB.Connection
    
    conn.ConnectionString = "DRIVER={MySQL ODBC 3.1 Driver};" & _
                            "SERVER=localhost;" & _
                            "PORT=3306" & _
                            "DATABASE=test;" & _
                            "UID=test;PASSWORD=test;OPTION=3" 
     conn.Open
    
End Sub

 4.문제가 발생 했다. 
    
 
 (ADO Connection Error User type not defined)
 ADODB.Connection에서 짝 걸리는걸로 봐선 저놈을 막쓰면 안되고 import 해줘야 하겠지. 
 VBA화면에서 도구 -> 참조 선택한다. 
  뜨는 목록에서 아래 그림과 같이 Microsoft ActiveX Data Objects 2.8 Library를 선택한다.
 
 
 설정 후 다시 테스트 해본다.(F5) 

 
5.또 문제가 발생했다.  
 
 

 이런 메세지가 나오면 저 빨간박스의 이름이 잘 못 된 것이다. 
 아래의 빨간 박스의 이름과 일치해야 한다. 

 아래처럼 바꿔주고 다시 테스트 해보자 (F5)

 이 이후 부터 에러는 접속시 비번이 틀렸다거나 접속위치가 틀리다거나 
 (mysql에 접근경로를 localhost로 해놓고 외부에서 쎄가 빠지게 접속해바야 안되겠지) 
 뭐 이런 경우다.  그리고 아래와 같이 MySQL 에러 메세지가 나오기 때문에 훨 편해진다.



6. 자 이젠 모두 된거 같으니.  
   mysql 테이블에서 값을 가져워서 워크시트에 박아보자. 
   (참고 : test database에 tsettb table에 2개의 row가 있는것을 가져올 테세다.
   
 
Sub getMyData()
     
    Dim conn As ADODB.Connection
    Dim dbRecset As ADODB.Recordset
    Dim sSQL As String
    Dim l As Long, l2 As Long
     
    '접속할 컨넥션 맹글고
    Set conn = New ADODB.Connection
    '접속할 스트링 맹글고 
    conn.ConnectionString = "DRIVER={MySQL ODBC 5.1 Driver};" & _
                            "SERVER=localhost;" & _
                            "PORT=3306" & _
                            "DATABASE=test;" & _
                            "UID=minsoo;PASSWORD=minsoo;OPTION=3"   
     
    '실제 접속 하고
    conn.Open 
    '테이블에서 SELECT 하자. 
    '참고로 앞쪽에 DB명을 안적어 주면 디비 선택 안됬다고 에러 또 뜬다.
    sSQL = "SELECT * FROM test.tsettb "     
    
    'Create a recordset and set the CursorLocation property for record navigation
    Set dbRecset = New ADODB.Recordset
    dbRecset.CursorLocation = adUseClient
     
    'Fetch data
    dbRecset.Open Source:=sSQL, ActiveConnection:=conn, CursorType:=adOpenForwardOnly, _
                  LockType:=adLockReadOnly, Options:=adCmdText
     
    'Move to the first record
    dbRecset.MoveFirst
    
    'Cycle through all the fields and write name to worksheet
    'You could omit this step if you just want data
    For l = 1 To dbRecset.Fields.Count
        Worksheets(1).Cells(1, l).Value = dbRecset.Fields(l - l).Name
    Next l
     
    'Write all data to your worksheet
    For l2 = 1 To dbRecset.RecordCount
        For l = 1 To dbRecset.Fields.Count
            Worksheets(1).Cells(l2 + 1, l).Value = dbRecset.Fields(l - 1).Value
        Next l
        dbRecset.MoveNext
    Next l2
     
    'Close connection again
    dbRecset.Close
    conn.Close
     
    'Tidy up
    Set dbRecset = Nothing
    Set conn = Nothing
End Sub

 


다시 엑셀로 돌아와서 워크시트를 보면 값이 박혀 있다. 


 
테스트 파일 

test.xlsm

  



참조 SITE : 

//읽어 봄직한거 : 뭐 코딩적으로 ADB를 추가하는 방법 인거 같다.

//위쪽 예제.


추가 알아볼 것
1.INSERT / UPDATE / DELETE : 뭐 별건 없겠지 SELECT가 되니. 
2.SELECT 후 시트에 추가 좀더  
3.시트옆에 버튼 달아두고 눌리면 INSERT 되게  
4.매크로 포함된 엑셀 문서 배포




저작자표시


티스토리툴바