Simone
'在使用前需要先安装MySql的驱动，进行正确配置'行拍注意：必须给出正确的服务器名、数据库名、表名、数据库连接的用户名、密码Option ExplicitDim Cnn As ADODB.Connection '定义ADO连接对象Dim Records As ADODB.Recordset '定义ADO记录集对象'连接到数据库Function CnnOpen(ByVal ServerName As String, ByVal DBName As String, ByVal TblName As String, ByVal User As String, ByVal PWD As String) '服务器名或IP、数据库名、登录用户、密码Dim CnnStr As String '定义连接字符串Set Cnn = CreateObject("ADODB.Connection") '创建ADO连接对象Cnn.CommandTimeout = 15 '设置超时时间CnnStr = "DRIVER={MySql ODBC 5.1 Driver};SERVER=" & ServerName & ";Database=" & DBName & ";Uid=" & User & ";Pwd=" & PWD & ";Stmt=set names GBK" 'Cnn.ConnectionString = CnnStrCnn.OpenEnd Function'关闭连接Function CnnClose()If Cnn.State = 1 ThenCnn.CloseEnd IfEnd Function'把Excel写入MySql中的数据库Function InsertToMySql(ByVal SheetName As String, ByVal TblName As String)Dim SqlStr As StringDim i, j As IntegerDim Columns, Rows As IntegerColumns = VBAProject.func_public.GetTotalColumns(SheetName)Rows = VBAProject.func_public.GetTotalRows(SheetName)Set Records = CreateObject("ADODB.recordset")'档皮羡取得结果集并插入数据到数据库Set Records = CreateObject("ADODB.Recordset")'以下语句提供了插入思路，我只是握升把单条记录的插入方式改为循环，以把所有的记录添加到表中'rs.Open "insert into newtable values('" & ActiveSheet.Cells(i, 1).Value & "'," & "'" & ActiveSheet.Cells(i, 2).Value & "')", cnn, 0For i = 2 To Rows SqlStr = "INSERT INTO " & TblName & " values('" & Sheets(SheetName).Cells(i, 1).Value & "'" '注意：" values('"，字母“v”之前是有空格的！！！ For j = 2 To Columns SqlStr = SqlStr & ",'" & Sheets(SheetName).Cells(i, j).Value & "'" Next SqlStr = SqlStr & ")" Set Records = Cnn.Execute(SqlStr) 'rs.Open SqlStr, cnn, 0 不能用这条语句实现！！！NextMsgBox "Insert!", vbOKOnly, "Excel To MySql"End Function'清除对象Function ClearObj()Set Cnn = NothingSet Records = NothingEnd Function
