Excel数据批量导入到SqlServer的方法

Subtest()
    Dim cn AsADODB.Connection
    Dim strSQL As String
    Dim lngRecsAff As Long
    Dim Headers As Boolean
    Dim strConn As String
    Dim path As String
    On Error GoTotest_Error
    Headers = True
    path = "c:20131212.xls"
    strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" &_
              "Data Source=" & path & ";" &_
              "Extended Properties=""Excel 8.0; IMEX=1;HDR=YES"""
    Debug.Print strConn
    Set cn = NewADODB.Connection
    cn.Open strConn
     'Import by using Jet Provider.
    strSQL = "Insert INTO [odbc;Driver={SQL Server};" &_
             "Server=192.168.6.111;Database=answer;" &_
             "UID=sa;PWD=password].test1 " &_
             "Select * FROM [Sheet1$]"
    Debug.Print strSQL
    cn.Execute strSQL, lngRecsAff
    Debug.Print "Records affected: " &lngRecsAff
    cn.Close
    Set cn = Nothing
    On Error GoTo 0
    Exit Sub
test_Error:
    MsgBox "Error " & Err.Number & "(" & Err.Description & ") in procedure test of VBA Document ThisWorkbook"
End Sub