USE SQL IN MANIPULATING DATA IN EXCEL

G

Guest

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!
 
B

Bob Phillips

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)
 
G

Guest

Bob Phillips said:
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)






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.
Click to expand...
 
M

michael.beckinsale

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
 
B

Bob Phillips

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)
 
G

Guest

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!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Using ODBC & ADO in manipulating data from worksheet. HELP! plssss 1
Excel Creation/Manipulation Programmatically in .NET 1
Huge Memory Leaks using ODBC Drivers from Excel to retrieve data 7
recordset vs collection 2
USing SQL in Excel using VBE 6
Manipulate cells from UDF 5
Running a SQL query off an excel table of data 1
vba array logic 3

Top