用VBA将EXCEL内容一次性导入SQL

用VBA将EXCEL内容一次性导入SQL
'工具->引用->Microsoft ActiveX Date Object 2.0
Public Sub SaveData()
Dim Cnn As ADODB.Connection
Dim SQL As String

Set Cnn = New ADODB.Connection

'建立于数据库的链接
'这里根据你的实际值修改ConnectionString = "Driver=SQL Server;Server=服务器名称;Database=数据库;Uid=账号;Pwd=密码;"
With Cnn
.Provider = "SQLOLEDB"
.ConnectionString = "Driver=SQL Server;Server=mxb\sqlexpress;Database=test;Uid=sa;Pwd=xiaoma;"
.Open
End With

'保存数据
r = Range("A65534").End(xlUp).Row
For i = 1 To r
'拼sql
SQL = "insert into T values('" & Cells(i, 1) & "','" & Cells(i, 2) & "'," & Cells(i, 3) & ")"
Cnn.Execute SQL
Next
Cnn.Close
Set Cnn = Nothing
MsgBox "保存成功"

End Sub

上面是通过VBA,插入数据到数据库,下面是从SQL查询Excel,然后直接insert into到数据库,也可以用数据库导入向导
--查询excel2007
select * from OpenDataSource('Microsoft.ACE.OLEDB.12.0', 'Data Source=D:\2007.xlsx;Extended Properties="Excel 12.0;HDR=Yes;IMEX=1"')...[Sheet1$]
--查询excel2003
select * from OpenDataSource( 'Microsoft.Jet.OLEDB.4.0','Data Source="D:\2003.xls";Extended properties=Excel 5.0')...[Sheet1$]