excel cell data to SQLEXPRESS - SQL Server Forums

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.

All Forums

SQL Server 2005 Forums

Express Edition and Compact Edition (2005)

excel cell data to SQLEXPRESS

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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources