VBA常用自定义函数(3) - 从MySQL数据库取数
需要安装对应驱动程序: mysql-connector-odbc
输出的数组首行为表头
'从Mysql中取数并转换为二维数组
Public Function GetMySQlArr(ByVal SQLStr As String)
Dim TempArr, AimArr
Dim i As Long, r As Long
Dim CN As ADODB.Connection '定义数据链接对象 ,保存连接数据库信息;请先添加ADO引用
Dim rs As ADODB.Recordset '定义记录集对象,保存数据表
Dim Cols As Long, Rows As Long
'下面的语句将读取数据表数据
Set CN = New ADODB.Connection
Set rs = New ADODB.Recordset
CN.ConnectionString = "Driver={MySQL ODBC 8.0 Unicode Driver};Server=改成服务器IP地址;Port=改成端口号;Database=改成数据库名;Uid=改成用户名;Pwd=改成用户密码;OPTION=3;"
CN.Open
'定义SQL查询命令字符串
rs.Open SQLStr, CN
If rs.EOF Then Exit Function
TempArr = rs.GetRows
Rows = UBound(TempArr, 2) + 2
Cols = UBound(TempArr, 1) + 1
ReDim AimArr(1 To Rows, 1 To Cols)
For i = 1 To Cols
AimArr(1, i) = rs.Fields(i - 1).Name
Next
For i = 2 To Rows
For j = 1 To Cols
AimArr(i, j) = TempArr(j - 1, i - 2)
Next
Next
rs.Close
CN.Close
GetMySQlArr = AimArr
End Function