用VBA将EXCEL内容一次性导入SQL
发布网友
发布时间：2022-04-26 17:56
共1个回答
热心网友
时间：2023-10-19 10:40
'工具->引用->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=<a href="https://www.baidu.com/s?wd=%E6%9C%8D%E5%8A%A1%E5%99%A8%E5%90%8D%E7%A7%B0&tn=44039180_cpr&fenlei=mv6quAkxTZn0IZRqIHckPjm4nH00T1d9uHcvryRLuW99uHb1uHK90ZwV5Hcvrjm3rH6sPfKWUMw85HfYnjn4nH6sgvPsT6KdThsqpZwYTjCEQLGCpyw9Uz4Bmy-bIi4WUvYETgN-TLwGUv3EnHnvP1RdPjTsn1R3P1Dkn1DLrf" target="_blank" class="-highlight">服务器名称</a>;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$]追问不要用循环指令插入，用循环指令插入这个代码我知道的，而且比你这个还简洁，谢谢
