vba 读取数据库

1、安装数据库

2、创建数据源

 

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.Row = 1 And Target.Cells.Column = 1 Then
       Call mySQL
    End If
End Sub

Public Function mySQL()
  strconnt = ""
  Set connt = CreateObject("ADODB.Connection")
  Dim sevip, Db, user, pwd As String
  '设服务器地址 所连数据，及登录用户密码
  sevip = "localhost"
  Db = "excelTest"
  user = "root"
  pwd = "900921"
  strconnt = "DRIVER={MySql ODBC 5.3 Unicode Driver};SERVER=" & sevip & ";Database=" & Db & ";Uid=" & user & ";Pwd=" & pwd & ";Stmt=set names GBK"
  connt.ConnectionString = strconnt
  connt.Open
  MsgBox "链接状态：" & connt.State & vbCrLf & "ADO版本：" & connt.Version, vbInformation, ""
   Set Rec = CreateObject("ADODB.Recordset")
   Set Rec = connt.Execute("select * from `uses`", iRowscount, adCmdText)
   Range("a6:c6").Value = Array("id", "name", "password")
   Range("a7").CopyFromRecordset Rec
End Function