Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help with manipulating data in excel? | Excel Discussion (Misc queries) | |||
Reading & manipulating the Excel data programatically in C# progra | Excel Programming | |||
manipulating SQL data through Excel | Excel Programming | |||
Need help using VBA to compare data in Excel columns, then manipulating data | Excel Programming | |||
Training on Manipulating Data in Excel | Excel Discussion (Misc queries) |