Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
dotinf
Starting Member
6 Posts |
 Posted - 2008-02-22 : 20:02:23
|
Hello, I am haing a little trouble with send cell data from an Excel sheet to SQLEXPRESS and I'm sure that it is not that hard. Has anybody else come accross this. I have the code below, but it is not complete as I'm not sure what to do to complete it:
Private Sub cmdSend_Click()
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim strConn As String
strConn = "PROVIDER=SQLOLEDB;"
strConn = strConn & "DATA SOURCE=(local)\SQLEXPRESS;INITIAL CATALOG=test;"
strConn = strConn & " INTEGRATED SECURITY=sspi;"
cn.ConnectionString = strConn
cn.Open
Set rs = New Recordset
With rs
' Assign the Connection object.
.ActiveConnection = cn
' Extract the required records.
.Open "UPDATE tblData Set ID = ID, Name = Name"
' Copy the records into cell A1 on Sheet1.
Sheet1.Range("A2, B2").CopyFromRecordset rs
' Tidy up
.Close
End With
cn.Close
End Sub
I know that it is not correct, but I am trying to convert a recordset query from SQL to Excel to Update Sql from Excel.
The cells from A2 down have an ID and cells from B2 down have a name.
Thanks for your time |
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2008-02-23 : 14:23:43
|
You don't need to pull the data into excel that way...you can use MS Query..
but if you instead on that method....you have to assign something to the recordset to begin with via a SQL Statement
Private Sub cmdSend_Click()
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim SQL as string
Dim strConn As String
Dim x as integer
Dim ws as Worksheet
Set ws = ActiveWorksheet 'or Sheets("Sheet1")
x = 2 'set the initial row number
strConn = "PROVIDER=SQLOLEDB;DATA SOURCE=(local)\SQLEXPRESS;INITIAL CATALOG=test;INTEGRATED SECURITY=sspi;"
SQL = "Select ID, Name FROM Table order by ID"
cn.ConnectionString = strConn
cn.Open
Set rs = New Recordset
rs.Open SQL, cn, adOpenForwardOnly, adLockReadOnly, adCmdText
Do While not rs.EOF
With ws
.Range(cells(x,1),cells(x,2) = Array(rs![ID],rs![Name])
End with
x = x+1
Loop
cn.Close
Set rs = NOTHING
set cn = NOTHING
End Sub
EDIT: Your last statement makes no sense...are you trying to get the data from SQL into excel, or from excel into SQL
Poor planning on your part does not constitute an emergency on my part.
|
 |
|
|
|
|
|
excel cell data to SQLEXPRESS