Dinesh

Your example is interesting. Do you want to use Excel as

an order entry form? In that case you have to secure that

the user doesn't insert incorrect data. That can be accomplished with

dynamic lists of values for each columns Customer name etc. The values

will be fetched from tables in the database. Also all not used cells should

be locked.

Do you have examples in SSIS which will insert and extract data from the same Excel sheet?

Also I recommend named cells as you can change the layout of the sheet

without changing the code.

I include a small code example.

The database can be hosted on a remote server and the user runs Excel on a local computer. The example is not aimed for have multi user situations.

The simple code is a part of a Module in Excel (VBA).

You need a reference to an ActiveX Data Object.

Sub test()

Dim conn As New ADODB.Connection

Dim cmd As New ADODB.Command

'Remote server

conn.Provider = "sqloledb"

conn.Properties("Network Library").Value = "DBMSSOCN"

conn.Open "Data source=name_of_server;User id=something;Database=something;Password=something"

Sheets("Order").Select 'Sheet name in the workbook

Set cmd.ActiveConnection = conn

cmd.CommandText = "Insert into orders " & _

"values ('" & Range("CustomerName").Value & "','" & Range("Address").Value & "','" & _

Range("SalesRep").Value & "'," & _

Range("Ordernumber").Value & ",'" & Range("OrderDate").Value & "')"

cmd.Execute

conn.Close

Set cmd = Nothing

Set conn = Nothing

End Sub

//Gosta