|
闲来无事,写个小过程帮助将excel的内容导入mysql服务器中
这个自定义过程有3个变量
第一个是要插入的表的名称,第二个是包含列名称的区域,第三个是包含值名称的区域
看不懂的请下载例子
模块代码如下:
Function chris_GetMySQLStr(tName As String, a As Range, b As Range)
Dim cList As String
Dim vList As String
Dim i As Integer
Dim tstr As String
If a.Cells.Rows.Count <> 1 Or b.Cells.Rows.Count <> 1 Or a.Cells.Columns.Count <> b.Cells.Columns.Count Then
MsgBox "wrong selection"
chris_GetMySQLStr = ""
Exit Function
Else
For i = 1 To a.Cells.Columns.Count
If a.Cells(1, i) <> "" And b.Cells(1, i) <> "" Then
cList = cList & a.Cells(1, i) & ","
vList = vList & Chr(39) & b.Cells(1, i) & Chr(39) & ","
End If
Next
End If
If Right(cList, 1) = "," Then
cList = Left(cList, Len(cList) - 1)
End If
If Right(vList, 1) = "," Then
vList = Left(vList, Len(vList) - 1)
End If
chris_GetMySQLStr = "insert into " & tName & " (" & cList & ") values (" & vList & ");"
End Function
[ 本帖最后由 citypanther 于 2010-2-13 00:32 编辑 ] |
|