USE SQL IN MANIPULATING DATA IN EXCEL
 

hELLO!

i would like to ask on how to use SQL to manipulate data stored in Excel
Workbooks. Do I have to set-up an ODBC Connection? if yes How? What would be
the best driver to use?
Another thing is how can I update, search and delete record (because the
program i'm doing right now can already add new record) in a worksheet using
userform.

I hope the MVP's here can help me and also to those who know about it.

Thank You in advanced!

USE SQL IN MANIPULATING DATA IN EXCEL
 

Here are examples using ADO.

It is based upon a workbook with a table of data name PeopleData

Option Explicit

Const adOpenForwardOnly As Long = 0
Const adLockReadOnly As Long = 1
Const adCmdText As Long = 1

Dim aryData

Public Sub GetData()
Dim oConn As Object
Dim oRS As Object
Dim sFilename As String
Dim sConnect As String
Dim sSQL As String

sFilename = "c:\TestFolders\Some book 1.xls"
sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & sFilename & ";" & _
"Extended Properties=Excel 8.0;"

Set oRS = CreateObject("ADODB.Recordset")

sSQL = "SELECT * FROM PeopleData"
oRS.Open sSQL, sConnect, adOpenForwardOnly, _
adLockReadOnly, adCmdText

' Check to make sure we received data.
If Not oRS.EOF Then
aryData = oRS.Getrows()
Else
MsgBox "No records returned.", vbCritical
End If

' Clean up our Recordset object.
oRS.Close
Set oRS = Nothing

End Sub

Sub AddData()
Dim oConn As Object
Dim oRS As Object
Dim sSQL As String
Dim sFilename As String

sFilename = "C:\TestFolders\Some book 1.xls"
Set oConn = CreateObject("ADODB.Connection")
oConn.Open = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & sFilename & ";" & _
"Extended Properties=Excel 8.0;"

sSQL = "INSERT INTO PeopleData (FirstName, LastName,Phone, Notes) " & _
" VALUES ('Jim','Brown','01202 345678','Client')"
oConn.Execute sSQL

oConn.Close
Set oConn = Nothing
End Sub


Sub UpdateData()
Dim oConn As Object
Dim oRS As Object
Dim sConnect As String
Dim sSQL As String
Dim ary
Dim sFilename As String

sFilename = "c:\TestFolders\Some book 1.xls"
sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & sFilename & ";" & _
"Extended Properties=Excel 8.0;"

sSQL = "SELECT * From PeopleData"
Set oRS = CreateObject("ADODB.Recordset")
oRS.Open sSQL, sConnect, adOpenForwardOnly, _
adLockReadOnly, adCmdText

' Check to make sure we received data.
If oRS.EOF Then
MsgBox "No records returned.", vbCritical
Else
sSQL = "UPDATE PeopleData " & _
" SET Phone = 'None' " & _
"WHERE FirstName = 'Bob' AND LastName = 'Phillips'"
oRS.ActiveConnection.Execute sSQL

sSQL = "SELECT * From PeopleData"
oRS.ActiveConnection.Execute sSQL
ary = oRS.Getrows()
MsgBox ary(0, 0) & " " & ary(1, 0) & ", " & ary(2, 0)
End If

End Sub


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Chinx21" . wrote in message
...
hELLO!

i would like to ask on how to use SQL to manipulate data stored in Excel
Workbooks. Do I have to set-up an ODBC Connection? if yes How? What would
be
the best driver to use?
Another thing is how can I update, search and delete record (because the
program i'm doing right now can already add new record) in a worksheet
using
userform.

I hope the MVP's here can help me and also to those who know about it.

Thank You in advanced!

USE SQL IN MANIPULATING DATA IN EXCEL
 

"Bob Phillips" wrote:

Here are examples using ADO.

It is based upon a workbook with a table of data name PeopleData

Option Explicit

Const adOpenForwardOnly As Long = 0
Const adLockReadOnly As Long = 1
Const adCmdText As Long = 1

Dim aryData

Public Sub GetData()
Dim oConn As Object
Dim oRS As Object
Dim sFilename As String
Dim sConnect As String
Dim sSQL As String

sFilename = "c:\TestFolders\Some book 1.xls"
sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & sFilename & ";" & _
"Extended Properties=Excel 8.0;"

Set oRS = CreateObject("ADODB.Recordset")

sSQL = "SELECT * FROM PeopleData"
oRS.Open sSQL, sConnect, adOpenForwardOnly, _
adLockReadOnly, adCmdText

' Check to make sure we received data.
If Not oRS.EOF Then
aryData = oRS.Getrows()
Else
MsgBox "No records returned.", vbCritical
End If

' Clean up our Recordset object.
oRS.Close
Set oRS = Nothing

End Sub

Sub AddData()
Dim oConn As Object
Dim oRS As Object
Dim sSQL As String
Dim sFilename As String

sFilename = "C:\TestFolders\Some book 1.xls"
Set oConn = CreateObject("ADODB.Connection")
oConn.Open = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & sFilename & ";" & _
"Extended Properties=Excel 8.0;"

sSQL = "INSERT INTO PeopleData (FirstName, LastName,Phone, Notes) " & _
" VALUES ('Jim','Brown','01202 345678','Client')"
oConn.Execute sSQL

oConn.Close
Set oConn = Nothing
End Sub


Sub UpdateData()
Dim oConn As Object
Dim oRS As Object
Dim sConnect As String
Dim sSQL As String
Dim ary
Dim sFilename As String

sFilename = "c:\TestFolders\Some book 1.xls"
sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & sFilename & ";" & _
"Extended Properties=Excel 8.0;"

sSQL = "SELECT * From PeopleData"
Set oRS = CreateObject("ADODB.Recordset")
oRS.Open sSQL, sConnect, adOpenForwardOnly, _
adLockReadOnly, adCmdText

' Check to make sure we received data.
If oRS.EOF Then
MsgBox "No records returned.", vbCritical
Else
sSQL = "UPDATE PeopleData " & _
" SET Phone = 'None' " & _
"WHERE FirstName = 'Bob' AND LastName = 'Phillips'"
oRS.ActiveConnection.Execute sSQL

sSQL = "SELECT * From PeopleData"
oRS.ActiveConnection.Execute sSQL
ary = oRS.Getrows()
MsgBox ary(0, 0) & " " & ary(1, 0) & ", " & ary(2, 0)
End If

End Sub


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Chinx21" . wrote in message
...
hELLO!

i would like to ask on how to use SQL to manipulate data stored in Excel
Workbooks. Do I have to set-up an ODBC Connection? if yes How? What would
be
the best driver to use?
Another thing is how can I update, search and delete record (because the
program i'm doing right now can already add new record) in a worksheet
using
userform.

I hope the MVP's here can help me and also to those who know about it.

Thank You in advanced!



wooh! I've just post my message about i think less than 30 mins. when I 'm still in the office and right now I'm at home reading the reply of what I've posted! Thumbs Up for you Sir Bob! Thank You again.

USE SQL IN MANIPULATING DATA IN EXCEL
 

Hi Bob,

Sorry, missed this post earlier and it sort of answers my more recent
post.

Can you just clarify a couple of things:

1) Using SQL within the Excel environment requires a connection?
2) In your examples you use a table called 'PeopleData'. How does this
equate to Excel? Is it a named range, worksheet or what?
3) The FirstName, Surname, PhoneNo ete are column headings ie fields

TIA

Regards

Michael beckinsale

USE SQL IN MANIPULATING DATA IN EXCEL
 

That's odd Michael, I saw your post and thought it was asking something
different <G.

To use ADO, you do need a connection, and in the example I gave you manually
define and make the connection. SQL is the way to query the data source
using the active connection.

PeopleData is a named range, yes. But you could use a worksheet, or a
specified range.

Those are column heading, yes.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"michael.beckinsale" wrote in message
s.com...
Hi Bob,

Sorry, missed this post earlier and it sort of answers my more recent
post.

Can you just clarify a couple of things:

1) Using SQL within the Excel environment requires a connection?
2) In your examples you use a table called 'PeopleData'. How does this
equate to Excel? Is it a named range, worksheet or what?
3) The FirstName, Surname, PhoneNo ete are column headings ie fields

TIA

Regards

Michael beckinsale

USE SQL IN MANIPULATING DATA IN EXCEL
 

Hi Bob! it's me again. well, I'm going to detailed my current situation right
now and what I want to do.
I have a workbook named WBOOK with I think 20 worksheets. What I want is to
have a User form(my main menu) to run whenever I open the workbook (I've
already done that) I've also added forms (for every worksheet there is a
corresponding form).

What I want to do is to be able to edit and delete data from worksheets
through its form. Example: in student table which has a field of id no.,
name, etc. When a user want to edit he must provide an id no. then if found
those information will be displayed in its corresponding control in the form.
how's that?

I think my problem is in setting-up ODBC connection.
I hope you can help me. Thank You for spending time with this. God Bless!