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