Mikel
来源： Excel数据批量导入到SqlServer的方法 – xiashengwang – 博客园
1，以Excel为数据源建立连接导入。
关键点在于Excel的数据要有表头，表头要和数据库表的列名一样。连接字符串中HDR=YES不能省略，也就是第一行是表头的意思。IMEX=1;是把数据都当作字符串读取。
Sub test()
Dim cn As ADODB.Connection
Dim strSQL As String
Dim lngRecsAff As Long
Dim Headers As Boolean
Dim strConn As String
Dim path As String
On Error GoTo test_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 = New ADODB.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
2，还有一种方案，是以SQLServer为数据源，写法大致如下
“INSERT INTO [档案1] SELECT * FROM [Excel 8.0;Database=” & ThisWorkbook.FullName & “;HDR=YES].[sheet1$” & addr & “];”
