INSERT data from Excel into SQL DB

I have created an Excel Sheet that does some lookups to format data that needs to be inserted into another table. This Excel Workbook needs to be given to some users that are free to add some new rows, and then need to be able to hit an "Insert Into Database" button and have the records transformed and inserted as new records i开发者_开发百科nto a SQL Table. I am using Excel 2010 and SQL Server 2008. I have a connection to the DB as I am using it to pull some data back in order to verify the new rows being added, but I'm not sure how to then insert the data back.

You can do a lot with 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

You can also use the ACE connection: http://www.connectionstrings.com/ or OPENROWSET and an SQL Server connection. In all cases, you may have problems with mixed data types in columns, depending on your registry settings (http://forum.lessthandot.com/viewtopic.php?f=17&t=12043&p=59669&hilit=excel#p59669)

I have found out that within a macro, you can create an ADO connection by adding a reference to the "Microsoft ActiveX Data Objects 6.0 Library". Once you have opened a connection within the Macro, you can create your insert statement and execute it via using the connection.Execute(statement) method:

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

After modifying the data in excel, need to generate Update statements, which will be executed by pressing the button "update". As a result, will be executed Update and Insert statements. Then have to send a query to refresh the data in Excel.(imho)