vba 将excel插入sql数据库

最新推荐文章于 2024-08-30 09:33:36 发布

maocheng82

最新推荐文章于 2024-08-30 09:33:36 发布

阅读量1.8k

点赞数

文章标签： vba excel sql insert security delete

本文链接：https://blog.youkuaiyun.com/maocheng82/article/details/4929829

版权

Private Sub insert1_click()
Dim iCount As Integer
Dim cn
Set cn = CreateObject("ADODB.Connection")

cn.ConnectionString = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Password=8233;Initial Catalog=hskmis;Data Source=127.0.0.1"
cn.Open
cn.Execute ("delete temp9")
'计算该表有多少行
RowCount = Worksheets("FC").UsedRange.Rows.Count
'行循环
For irow = 1 To RowCount
strRecord = ""
'列循环,假设有五列,从第一列开始
For icol = 1 To 5
If icol <> 5 Then
strRecord = strRecord & "'" & Worksheets("FC").Cells(irow, icol).Value & "'" & ","
Else
strRecord = strRecord & "'" & Worksheets("FC").Cells(irow, icol).Value & "'"
End If
Next
MsgBox strRecord
Sql = cn.Execute("insert into temp9 values(" & strRecord & ")")
Next
MsgBox "插入完成"
End Sub