将Excel中的数据插入SQL数据库

使用ADO可以做很多事情:

Dim cn As New ADODB.Connection

''You should probably change Activeworkbook.Fullname to the
''name of your workbook
strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" _
& ActiveWorkbook.FullName _
 & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"

cn.Open strCon

s = "INSERT INTO [ODBC;Description=TEST;DRIVER=SQL Server;" _
& "SERVER=Server;Trusted_Connection=Yes;" _
& "DATABASE=test].SomeTable ( Col1, Col2, Col3, Col4 ) " _
& "SELECT  a.Col1, a.Col2, a.Col3, a.Col4 " _
& "FROM [Sheet2$] a " _
& "LEFT JOIN [ODBC;Description=TEST;DRIVER=SQL Server;" _
& "SERVER=Server;Trusted_Connection=Yes;" _
& "DATABASE=test].SomeTable b ON a.Col1 = b.Col1 " _
& "WHERE b.Col1 Is Null"
cn.Execute s

您还可以使用ACE连接: http://www.connectionstrings.com/ http://forum.lessthandot.com/viewtopic.php?f=17&t=12043&p=59669&hilit=excel#p59669 )

我发现在宏中,可以通过添加对“MicrosoftActiveX数据对象6.0库”的引用来创建ADO连接。一旦在宏中打开了连接,就可以创建insert语句并使用连接。执行(报表)方法:

Dim item as String = "Insert Into MyTable(ColA,ColB) VALUES('Foo', 'Bar')"
Dim thisCon As New ADODB.Connection
thiscon.Open("ConnectionString")
thisCon.Execute (item)

在excel中修改数据后,需要生成Update语句,按“Update”按钮执行。因此,将执行Update和Insert语句。然后必须发送查询以刷新Excel中的数据