Excel VBA操作MySQL

1、 作者Email：858845818目录1. ExcelVBA操作MySQL之一准备工作2. ExcelVBA操作MySQL之二链接MySQL3. ExcelVBA操作MySQL之三有关用户名和密码的操作4. ExcelVBA操作MySQL之四创建数据库和表5. ExcelVBA操作MySQL之五添加和导入数据6. ExcelVBA操作MySQL之六查询记录7. ExcelVBA操作MySQL之七从文本文件导入数据和数据导出至文本文件8. ExcelVBA操作MySQL之八修改记录9. ExcelVBA操作MySQL之九删除记录10. ExcelVBA操作MySQL之十查询示例11

2、. ExcelVBA操作MySQL之十一有关表的操作12. ExcelVBA操作MySQL之十二获取数据库模式信息13. ExcelVBA操作MySQL之十三生成数据透视表14. ExcelVBA操作MySQL之十四AppendChunk、 GetChunk存取文本和图片二进制数据15. ExcelVBA操作MySQL之十五使用ADODB.Stream ExcelVBA操作MySQL之一准备工作前段时间学习MySQL,发现网上关于VBA+MySQL的文章不多。现在分享ExcelVBA操作MySQL的代码。欢迎各位网友指正，提供更好的解决方法。从这系列文章可以了解到：l ExcelVBA操作数据

3、库的方法l ADO的用法l MySQL的SQL语句的用法1. MySQL的简介 不多说。看维基/wiki/MySQL看百度/view/24816.htm2. 安装MySQL和myODBC for MySQL。MySQL和myODBC for mysql的下载地址：/f/16594651.html内含MySQL-5.5.12-win32 + myODBC for MySQL+MySQL 5.0中文版官方说明。当然,可以到官网下载。http:/downlo

4、//downloads/connector/odbc/嫌官网下载麻烦的自己百度谷歌吧。关于myODBC for mysql的安装,请看这里/doc/refman/5.1/zh/connectors.html#dsn-on-windows关于MySQL的安装,请看这里/tech/1/2430_1.htmlMySQL的安装的教程网络上到处有。实在嫌麻烦的到此网址下载MySQL和myODBC for MySQL：.

5、cn/f/17320163.html3. MySQL数据库图形化管理工具不习惯命令行界面的,可以使用Mysql的图形化工具,如MySQL Workbench、Navicat、SQLLog、EMS.SQL.Manager和HeidiSQL等。个人习惯用Navicat,下载地址：/f/16595122.html4. 引用ADO对象。引用方法：VBE中工具菜单引用选择Microsoft Activex Data Objects 2.8 Library和Microsoft ActiveX Data Object recordset 2.8 Li

6、brary好了,准备到此完毕。总结一下本人的软件环境：l WinXP with SP3l MySQL 5.5.12l MyODBC-3.51.11-2-winl Microsoft Excel 2007（兼容模式）l Microsoft ActiveX Data Object 2.8 Library和Microsoft ActiveX Data Object Recordset 2.8 Library。+-+-+-+-+-+-+-+-+-ExcelVBA操作MySQL之二链接MySQL毫无疑问,操作数据库的第一步是链接。链接字符串主要是给出Driver、Server、DataBase、UID、

7、PWD和Option。下面是简单的链接例子,此处省略了DataBase,根用户root账号的登录密码是123456。Public Sub LinkMySQL() Dim Con As ADODB.Connection Set Con = New ADODB.Connection Con.ConnectionString = Driver=MySQL ODBC 3.51 Driver; + _ Server=localhost; + _ UID=root; + _ PWD=123456; + _ OPTION=3; Con.Open If Con.State = adStateOpen Then

8、 MsgBox 链接状态： & Con.State & vbCrLf & ADO版本： & Con.Version, vbInformation, End If Con.Close: Set Con = NothingEnd Sub*如果想写得简单点，也可以写成：Public Sub LinkMySQL() Dim Con As ADODB.Connection Set Con = New ADODB.Connection Con.Open Driver=MySQL ODBC 3.51 Driver;Server=localhost;OPTION=3, root, 123456 If Con.

9、State = adStateOpen Then MsgBox 链接状态： & Con.State & vbCrLf & ADO版本： & Con.Version, vbInformation, End If Con.Close: Set Con = NothingEnd Sub*运行的结果如图：提示：1) 链接成功后,Connection的State属性为adStateOpen（值1）。2) ConnectionString有关参数参数默认值注释userODBC (on Windows)用于链接至MySQL的用户名。serverlocalhostMySQL服务器的主机名。database默认

10、数据库。option0指定MyODBC工作方式的选项。参见下面。port3306如果服务器不是本地主机将要使用的TCP/IP端口。stmt连接至MySQL时将要执行的语句。password服务器上用户账户的密码。socket当服务器是本地主机是将要连接的Unix套接字文件或Windows命名管道。要想选择多个选项，可将它们的值加在一起。例如，将选项设置为12（48），就能获得调试功能，但没有信息包限制。其中，UID,用户名,可以写作user ID或者user。PWD,用户名的登录的密码,可以写作PassWord。当使用简称时，等号后面的字符不加引号。3) Server数据库服务器的计算机名字,

11、或者IP。如果本机是服务器,设置为Localhost或。4) OPTION,指定MyODBC工作方式的选项。以下为摘录文字：在Windows平台下,正常情况下,应通过切换连接屏幕上的复选框选择选项,但也能在选项参量中选择它们。下述选项是按照它们在MyODBC连接屏幕上显示的顺序排列的：值描述1客户端无法处理,MyODBC返回列的实际宽度。2客户端无法处理,MyODBC返回受影响行的真值。如果设置了该标志,MySQL将返回“发现的行”取而代之。MySQL的版本必须是3.21.14或更高版本,该功能才能生效。4在c:myodbc.log中生成调试日志。它与将MYSQL_DEBUG

12、=d:t:O,c:myodbc.log放到AUTOEXEC.BAT中的效果相同（在Unix平台下,该文件是/tmp/myodbc.log）。8不为结果和参数设置任何信息报限制。16即使驱动程序可能会给出提示,对出现的问题不予提示。32允许动态光标支持。（在MyODBC 2.50中不允许）。64在db_name.tbl_name.col_name中忽略数据库名的使用。128强制使用ODBC管理器光标（实验性）。256禁止使用扩展取数据（实验性）。512将CHAR列填充为全列宽。1024SQLDescribeCol()返回完全合格的列名。2048使用压缩客户端服务器协议。4096通知服务器忽略函数

13、名之后和“(”之前的空格（PowerBuilder要求这样）。这会使所有的函数名成为关键字。8192用命名管道链接至运行在NT环境下的mysqld服务器。16384将LONGLONG列更改为INT列（某些应用程序不能处理LONGLONG列）。32768从SQLTables返回作为Table_qualifier和Table_owner的用户（实验性）。65536从f的client和odbc组读取参数。131072增加一些额外检查（不应需要之,但）。262144禁止事务。524288允许将查询记录到c:myodbc.sql(/tmp/myodbc.sql)文件。（仅在调试模式下才能启用）

14、。1048576不要驱动中的结果进行缓冲处理,而应从服务器读取“mysql_use_result()”。仅对正向光标才能起作用。当你不希望缓冲处理整个结果集时,对于大表处理,该选项十分重要。2097152强制使用正向光标类型。在应用程序设置了默认静态动态光标类型的情况下,如果希望驱动程序使用非缓冲结果集,那么该选项能够保证正向光标的行为。在下面的表各中,给出了针对各种配置的推荐选项值：配置选项值Microsoft Access3Microsoft Visual Basic3具有很多行的大表2049驱动跟踪生成（调试模式）4查询日志生成（调试模式）524288生成驱动跟踪和查询日志（调试模式）5

15、24292具有非缓冲结果的大表3145731提醒：在配置Connector/ODBC时,Advanced的某些选项等效于设置OPTION。5) 个人认为其他比较重要的提示。以下为摘录文字：为什么SELECT COUNT(*) FROM tbl_name返回错误？这是因为COUNT(*)表达式返回了1个BIGINT, ADO不理解这个大值的含义。选择“将BIGINT列更改为INT”选项（选项值16384）。MyODBC支持动态光标类型吗？是。MyODBC 3.51支持动态光标类型以及正向和静态特性。由于性能方面的原因,在默认情况下,驱动程序不支持该特性。你可以启用该特性,方法是,将连接选项标志指

16、定为“OPTION=32”,或在DSN配置中选中“启用动态光标”选项。MyODBC的性能为什么很差,对于相对较小的查询也会导致大量的磁盘动作？MyODBC比其他ODBC驱动程序快很多。缓慢可能是因未使用下述选项造成的：打开“ODBC跟踪”选项。遵循这里给出的指示说明,交叉检查是否未启用该选项。如上图所示,“ODBC数据源管理器”“跟踪”选项卡的“何时跟踪”选项应始终指向“现在开始跟踪”,而不是“现在停止跟踪”。使用了驱动程序的调试版本。如果你正在使用驱动DLL的调试版本,也会使查询处理变慢。你可以执行交叉检查,通过驱动DLL属性（在系统目录下,右击驱动DLL并点击“属性”）的“注释”区,检查D

17、LL是否是调试版或发布版,如下图所示：启用了“驱动跟踪和查询日志”。即使你打算使用驱动程序的调试版（在生产环境下总应使用发布版）,也应确保禁止了“驱动跟踪和查询日志”选项（OPTION=4,524288）,如下图所示：摘录自：/doc/refman/5.1/zh/connectors.html#connection-parameters如果想连接字符串了解更多，可以到/ 。+-+-+-+-+-+-+-+-+-ExcelVBA操作MySQL之三有关用户名和密码的操作1. 更改根用户名和密码。使用默认的ro

18、ot作为用户名是不太安全的，下面的代码把root用户名改为VBA，密码为excelmysql。Public Sub Changeroot() Dim Con As ADODB.Connection Set Con = New ADODB.Connection Con.ConnectionString = Driver=MySQL ODBC 3.51 Driver; + _ Server=localhost; + _ DB=mysql; + _ user=root; + _ PassWord=123456; + _ OPTION=3; Con.Open Con.Execute update us

19、er set user=VBA,password=password(excelmysql) where user=root;, , adCmdText 更改mysql数据库的user列值为root的记录。 Con.Execute flush privileges; 刷新权限表。 Con.Close: Set Con = NothingEnd Sub*提示：1) DB 链接后进入的数据库名字，也可以写作Database。2) 直接更改权限表的方法改变权限，必须运行Flush privileges刷新后才生效。而且，设置密码需用password函数加密。3) Excute的options参数设置为

20、adCmdText，指示commandtext的字符串应看作命令文本。预先指定options参数而不自动辨别，有助于加快运行。以下摘自ADO参考：Options 可选，长整型值，指示提供者应如何计算 CommandText 参数，可为下列值：常量说明AdCmdText指示提供者应按命令的文本定义计算 CommandText。AdCmdTable指示 ADO 应生成 SQL 查询以便从 CommandText 命名的表中返回所有行。AdCmdTableDirect指示提供者应从 CommandText 命名的表中返回所有行。AdCmdTable指示提供者应按表名计算 CommandText。Ad

21、CmdStoredProc指示提供者应按存储过程计算 CommandText。AdCmdUnknown指示 CommandText 参数中的命令类型未知。adAsyncExecute指示命令应该异步执行。adAsyncFetch指示对在 CacheSize 属性指定的初始数量之后的剩余行使用异步提取。2. 更改密码。也许原来的密码有点长的，现在把密码改为“excel“。Public Sub Resetpwd() Dim Con As ADODB.Connection Set Con = New ADODB.Connection Con.ConnectionString = Driver=MyS

22、QL ODBC 3.51 Driver; + _ Server=localhost; + _ user=VBA; + _ PassWord=excelmysql; + _ OPTION=3; Con.Open Con.Execute set password=password(excel);, , adCmdText Con.Close: Set Con = NothingEnd Sub*提示：1) 用set password语句更改密码是不需要运行Flush privileges的。3. 新建用户。新建用户比较好的方法是使用grant。此方法无需运行Flush privileges。下面的代

23、码新建本地用户monty，密码mysql。它的权限仅仅限于select，即检索数据。Public Sub Newuser() Dim Con As ADODB.Connection Set Con = New ADODB.Connection Con.ConnectionString = Driver=MySQL ODBC 3.51 Driver; + _ Server=localhost; + _ user=VBA; + _ PassWord=excel; + _ OPTION=3; Con.Open Con.Execute Grant select on *.* to monty127.0

24、.0.1 identified by mysql;, , adCmdText Con.Close: Set Con = NothingEnd Sub4. 为帮助理解mysql的访问权限处理，从MySQL入门到精通（美吉尔费伦）摘录：MySQL如何检查允许访问的权限 当一个用户试图连接时，MySQL首先检查用户表，以确定所列出的特定用户、主机和密码的组合。如果没有，则用户被拒绝访问。如果用户试图直接连接数据库，即便通过了其他检查，db表还是要被检查的。如果用户没有连接数据库的权限，访问将被拒绝。 当已完成连接的用户试图执行管理操作(比如说，mysqladmin shutdown)，MySQL会检

25、查用户表中与操作有关的列。如果所要求的操作被授予了权限，操作就能继续进行;如果没有，则操作会失败。如果已完成连接的用户试图执行与数据库有关的操作（如select, update,等等），MySQL将从用户表中检查相关的字段，如果所要求的操作(select,update等)被授予了权限，操作就会被允许。如果没有，MySQL就会进人下一个步骤。 下一步是检查db表。MySQL查找用户正在其上执行操作的的数据库。如果这个数据库不存在，则许可权被禁止，操作失败。如果数据库存在，主机和用户匹配，则与操作有关的字段会被检查。如果所要求的操作被授予了权限，操作会成功。如果没有被授予权限，MySQL接着进入下

26、一步。如果数据库和用户的组合存在，且主机的字段为空，MySQL就会检查主机表，看看主机是否能执行所要求的操作。如果在主机表中找到了主机和数据库，则在主机和db表上都相关的字段决定操作能否成功。如果两个表都授予了许可权，操作就会成功，如果没有，MySQL进人下一步。 MySQL检查tables-priv表，考虑要执行操作的表的情况。如果主机、用户、db与表的组合不存在，操作就会失败。如果存在，就会检查相关的字段。如果权限没有被授予，MySQL进入下一步。如果权限被授予，操作则会成功。 最后，MYSQL检查columns_priv表，考虑操作中所用的列的情况。如果所要求的操作被授予了权限，则操作成

27、功，否则，操作失败。MySQL许可权表的优先权顺序参见图（摘录完）另外两篇很不错的讲解权限的文章/2011/07/08/mysql-privilege-architecture.html/2011/07/22/mysql-account-privileges-manager.html+-+-+-+-+-+-+-+-+-ExcelVBA操作MySQL之四创建数据库和表1) 创建数据库。下面使用Create Database语句创建名为vbadb的数据库。Public Sub NewDatabase() Dim

28、 Con As ADODB.Connection Set Con = New ADODB.Connection Con.ConnectionString = Driver=MySQL ODBC 3.51 Driver; + _ Server=localhost; + _ user=VBA; + _ PassWord=excel; + _ OPTION=3; Con.Open Con.Execute create database if not exists vbadb;, , adCmdText 加if not exists表示如果不存在vbadb数据库时才创建 Con.Close: Set

29、Con = NothingEnd Sub*2) 创建表。下面将创建的表的字段属性如下：列名类型长度是否允许空值是否主键IDMediumint3否。自动递增数列。是chnamechar10否否ennamechar30否否sexEnum否。“男”或“女”否agetinyint1是否countrychar20是否commentsvarchar400是否代码：Public Sub NewTable() Dim Con As ADODB.Connection Set Con = New ADODB.Connection Con.ConnectionString = Driver=MySQL ODBC 3

30、.51 Driver; + _ Server=localhost; + _ DB=vbadb; + _ user=VBA; + _ PassWord=excel; + _ OPTION=3; + _ Stmt=Set Names GBK; Con.Open Con.Execute create table if not exists student ( + _ ID mediumint(3) NOT NULL AUTO_INCREMENT , + _ chname char(10) NOT NULL , + _ enname char(30) NOT NULL , + _ sex enum(男

31、,女) NOT NULL , + _ age tinyint(1) NULL DEFAULT NULL , + _ country char(20) NULL DEFAULT NULL , + _ comments varchar(400) NULL DEFAULT NULL , + _ PRIMARY KEY (ID) ENGINE=InnoDB;, , adCmdText Con.Close: Set Con = NothingEnd Sub*提示：1) 注意，这次的链接字符串多了一句“Stmt=Set Names GBK”。Stmt用来设置连接至MySQL时将要执行的语句。而Set Na

32、mes GBK语句指示链接使用的GBK字符集。如果不使用Stmt，也可以在打开链接后通过Excute方法运行Set Names GBK，即代码写为：Public Sub NewTable() Dim Con As ADODB.Connection Set Con = New ADODB.Connection Con.ConnectionString = Driver=MySQL ODBC 3.51 Driver; + _ Server=localhost; + _ DB=vbadb; + _ user=VBA; + _ PassWord=excel; + _ OPTION=3; Con.Ope

33、n Con.Execute Set Names GBK这句和在链接字符串加Stmt=Set Names GBK相同。 Con.Execute create table“End sub*2) 运行Set Names GBK的作用是指示使用GBK字符集，以便正常支持中文。不首先指定字符集，则创建表时不能识别中文，将报错：“Column sex has duplicated value ? in ENUM”。3) “”（char(32)）并非单引号。此符号由Esc键下方的“”输入。作用是指示在它之间的字符不属于关键字，而是表名或字段名。4) ENGINE=InnoDB，指定新建表的类型或称作存储引擎

34、类型。Mysql5.1支持提供了包括DBD、HEAP、ISAM、MERGE、MyIAS、InnoDB以及Gemeni表类型。其中DBD、InnoDB属于事务安全类表，而其他属于事务非安全类表。具体可参考手册：/doc/refman/5.1/zh/pluggable-storage.html#pluggable-storage-choosing+-+-+-+-+-+-+-+-+-ExcelVBA操作MySQL之五添加和导入数据之前创建了vbadb数据库和student表。现在将添加数据到student表里。下面分几种情形介绍。1. 从Excel工作表导入数据

35、。需导入的数据在工作表import里，如图：（1） 使用insert语句。为了方便写insert语句中的指定字段值部分，先自定义函数JOINFI。此函数的参数为arr（连接成字符串的数组），delimiter（分隔符），quotes（引号符），比如：JOINFI(Array(a, b, c), /, |)将返回字符串|a|/|b|/|c| 。JOINFI函数代码：Public Function JOINFI(arr As Variant, delimiter As String, Optional quotes As String = ) As String Dim i As Integer,

36、 el For Each el In arr i = 1 + i If i = 1 Then JOINFI = quotes & el & quotes Else JOINFI = JOINFI & delimiter & quotes & el & quotes End If Next elEnd Function*用insert语句插入记录的代码：Public Sub Import() Dim rngCur As Range, Cell As Range, i As Integer Dim sInsert As String, iRowscount As Integer Dim Con A

37、s ADODB.Connection With Worksheets(import) Set rngCur = .Range(.Range(a2), .Range(a2).End(xlDown) 获取数据区域 End With For Each Cell In rngCur i = 1 + i If i = 1 Then sInsert = ( + JOINFI(Cell.Offset(0, 1).Resize(1, 6).Value, , ) + ) Else sInsert = sInsert + , + ( + JOINFI(Cell.Offset(0, 1).Resize(1, 6).

38、Value, , ) + ) End If Next Cell 用for each 把数据添加到SQL命令字符串sInsert sInsert = insert student(chname,enname,sex,age,country,comments) value + sInsert 构造插入记录的SQL命令字符串sInsert Set Con = New ADODB.Connection Con.ConnectionString = Driver=MySQL ODBC 3.51 Driver; + _ Server=localhost; + _ DB=vbadb; + _ user=VB

39、A; + _ PassWord=excel; + _ OPTION=3; + _ Stmt=Set Names GBK; Con.Open Con.Execute sInsert, iRowscount, adCmdText Con.Close: Set Con = Nothing MsgBox 导入 & iRowscount & 行, vbOKOnly, End Sub*1) Insert语句的value后用多个括号和逗号分隔多条记录，一次性插入多行的用法是MySQL特有的语法。这种方法比使用多条Insert语句逐行插入更有效率。2) ID字段设置为自动递增，插入记录后自动编号，无须指定。2

40、. 使用AddNew方法。AddNew直接使用Insert语句效率较差。但代码看起来似乎简易。用Addnew方法的代码：Public Sub Addnew_Import() Dim rngCur As Range, Cell As Range Dim iRowscount As Integer Dim Con As ADODB.Connection Dim Rec As ADODB.Recordset Set Con = New ADODB.Connection Con.ConnectionString = Driver=MySQL ODBC 3.51 Driver; + _ Server=l

41、ocalhost; + _ DB=vbadb; + _ user=VBA; + _ PassWord=excel; + _ OPTION=3; + _ Stmt=Set Names GBK; Con.Open With Worksheets(import) Set rngCur = .Range(.Range(a2), .Range(a2).End(xlDown) 获取数据区域 End With Set Rec = New ADODB.Recordset Rec.Open student, Con, adOpenStatic, adLockOptimistic, adCmdTable For

42、Each Cell In rngCur Rec.AddNew _ Array(chname, enname, sex, age, country, comments), _ WorksheetFunction.Transpose(WorksheetFunction.Transpose _ (Cell.Offset(0, 1).Resize(1, 6).Value) 用两次transpose把区域的内容转为一维数组 iRowscount = 1 + iRowscount Next Cell Rec.Close: Set Rec = Nothing Con.Close: Set Con = Not

43、hing MsgBox 导入 & iRowscount & 行, vbOKOnly, End Sub*1) AddNew方法第二个参数values只接受一维数组。用两次transpose可以非常方便地把单元格区域转为一维数组。2) AddNew方法比使用Insert语句速度慢。3. 使用Load Data方法。Load Data是非常有效率的导入数据的方法，尤其适合于大量数据。使用Load Data 的用户须拥有 File 权限。先把上面的数据保存到文本文件C:import.txt。运行代码：Sub Saveastxt() Application.DisplayAlerts = False Dim tm As Worksheet Worksheets