学步园

解决工作过程中，经常性的从excel中导入数据库(sql数据库),创建宏

一般宏都是这种样子的

sub aaa()

 

end sub

 把下列代码放入宏中

    Dim i, k As Integer

        Dim conpon, groupbugcode As String
        Dim strSql As Variant
        Dim connctiong As String
        Dim cn As New ADODB.Connection
        Dim isUsed, userid, orderid As Integer
        isUsed = 0
        userid = 0
        orderid = 0
        Dim s As Integer
        Dim o As Long
'dsn数据库连接，需要在控制面板--管理工具--odbc中配置,配置为要导入的目标数据库
    cn.Open "DSN=wcj;uid=sa;pwd=sasa"
   With Sheet1
        For k = 2 To 30000
            conpon = .Cells(k, 1)'1为列数，列数从1开始计数
            If conpon <> "" Then
            
                groupbugcode = .Cells(k, 2)
                
                strSql = " insert into GB_Coupons(Coupon,GroupBuyCode,IsUsed,UserID ,OrderID,UpdateDate) values('" & conpon & "','" & groupbugcode & "'," & isUsed & "," & userid & "," & orderid & ",getdate()) "

                cn.Execute strSql
                s = s + 1
           End If

        Next k

  End With
    cn.Close
    Set cn = Nothing

     MsgBox "导入" & s & "条数据成功！"

 

能实现每分钟导入10000条数据