odbc 설정방법-엑셀 adodb mysql 연결
https://link2me.tistory.com/422
ODBC설정 안해도 되네요
Sub GetMysSQLData()
Dim DBConn As ADODB.Connection
Dim RS As ADODB.Recordset
Dim sSQL As String
Dim dbConnStr As String
Dim i As Long, dbRow As Long
Dim rngSheet As Worksheet
Dim DB_host As Variant
Dim DB_User As Variant
Dim DB_pass As Variant
Dim DB_Name As Variant
Application.ScreenUpdating = False '// 화면 업데이트 정지
Set shtName = Worksheets("DB_Setting") '// DB_Setting Sheet 에서 설정한 값을 가져온다
Set DB_host = shtName.Range("A2") '// IP 설정 값
Set DB_port = shtName.Range("B2") '// PORT 설정 값
Set DB_User = shtName.Range("C2") '// User 설정 값
Set DB_pass = shtName.Range("D2") '// PASS 설정 값
Set DB_Name = shtName.Range("E2") '// DB 설정 값
Set DBConn = New ADODB.Connection
dbConnStr = "DRIVER={MySQL ODBC 8.0 Unicode Driver};SERVER=" & DB_host & ";PORT=" & DB_port & ";DATABASE=" & DB_Name & ";USER=" & DB_User & ";PASSWORD=" & DB_pass & ";OPTION=3;"
DBConn.Open dbConnStr '// Open the connection
Set RS = New ADODB.Recordset
RS.CursorLocation = adUseClient
'// SQL 문 작성
'sSQL = "SELECT * FROM sentence"
sSQL = "SELECT * FROM topic"
'Fetch data
RS.Open Source:=sSQL, ActiveConnection:=DBConn, CursorType:=adOpenDynamic, LockType:=adLockReadOnly, Options:=adCmdText
RS.MoveFirst '// Move to the first record
Worksheets("TEST").Select '// 다른 시트에 있어도 TEST 시트로 ActiveSheet 가 전환됨
With Worksheets("TEST") '// Sheet 명을 지정한다
.Range("A1:AZ1048576").ClearContents '// 기존 데이터는 전부 삭제
For i = 1 To RS.Fields.Count '// 필드의 제목 가져다 뿌려준다
.Cells(1, i).Value = RS.Fields(i - 1).Name
Next i
.Range("A2").CopyFromRecordset RS '// A2 열부터 데이터를 뿌려준다
.Range([A1], Cells(1, RS.Fields.Count)).Select
Selection.AutoFilter
Cells(1, 2).Select
.Columns.AutoFit '// 전체열 열너비 자동맞춤
End With
'//Close connection again
RS.Close
DBConn.Close
Set RS = Nothing
Set DBConn = Nothing
End Sub
GetMysSQLData.xlsm