Excel add-in transferring data from sheets to database: file path problem

Question

I wish to create an addin for excel which can read the current sheets data and then insert it into database. The database connection requires the file location. Therefore that approach cant be applied as I have to send the file that I have currently opened and can't specify the file path.

And moreover I need the data of each column to be inserted into separate column of table in SQL Server database. Any suggestions and code to help?

Answer 1

Does it have to be done within Excel? You can do this with a simple ADO.NET app, specifying an OleDb provider for Excel, and another for SQL.

Example

There are lots of articles on the technique. You can do the same thing with minimal code in SQL Server Integration Services (SSIS, previously known as DTS). HEre's an old article on that.

You can also do this from within Excel. With VS2005 you would use VSTO - Visual Studio Tools for Office; I think the function of VSTO is included in VS2008 Professional. VS+VSTO (or VS2008) allows you to create AddIns for Office apps includin Excel. Within the AddIn you can do whatever you like in code, including using a System.Data.SqlClient to insert data into SQL Server.

Answer 2

You could create an Excel add-in with Excel using VBA. You need to add a reference to Microsoft ActiveX Data Objects (ADO) in the VBA editor (using Tools -> References), and then you'll have full access to the Connection, Command, RecordSet, etc objects.

If I was doing this, I'd have the user specify the database/table/columns for each import batch using a form. Then I'd create a Connection object and loop through each row of the data to create and execute an insert Command based on the data in the row.

When you are done, you have the option of saving the finished workbook as an Excel Addin file (xla) which can be distributed to others.

Here is a sample of what the insert code may look like:

Dim conn As New Connection
Dim comm As New Command
conn.Open YourConnectionString
Set comm.ActiveConnection = conn
comm.CommandText = "insert <table> (<column1>, <column2>, <column3>, ..., <columnN>)  values (" + <value1> + ", '" + <value2> + "', " + <value3> + ", '" + <valueN>+ "')"
comm.Execute
conn.Close