Worksheets and SQL
 

How does one use SQL with worksheets? Please give an example of how
you can use a select statement on a database in a worksheet and then
display the result in a another worksheet.

Than you

Worksheets and SQL
 

You can use ADO to retrieve from a DB (the Provider would differ depending
on the typew of DB. For a list of providers for ADO , go to:
www.connectionstrings.com:

Sub GetProjects()
Dim src As String
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim col As Integer
Dim mydate As Date

'On Error Resume Next
Application.ScreenUpdating = False
Workbooks("WORKBOOK NAME HERE").Worksheets("WORKSHEET NAME").Select

'Open a connection to the Orb DB view in POSTGRESQL using ODBC DSN
Set cnn = New ADODB.Connection
cnn.ConnectionString = "DSN=Postgresql30;UID=PUT USERID HERE;PWD=PUT
PASSWORD HERE;"
cnn.Open

'Set recordset as ADODB recordset
Set rs = New ADODB.Recordset

'Filter
src = "SELECT LIST FIELD NAMES HERE"
src = src & "FROM TABLENAME"
src = src & "WHERE project_name IN('" & TheseProjects & "') "
src = src & " ORDER BY- LIST FIELD NAMES HERE"
rs.Open Source:=src, ActiveConnection:=cnn,
CursorType:=adOpenForwardOnly, Options:=adCmdText

'Write the field names
For col = 0 To rs.Fields.Count - 1
Workbooks("WORKBOOK NAME").Worksheets("WORKSHEET
NAME").Range("N1").Offset(0, col).Value = _
rs.Fields(col).Name
Next
'Write the recordset
Workbooks("WORKBOOK NAME").Worksheets("WORKSHEET
NAME").Range("N1").Offset(1, 0).CopyFromRecordset rs

'Clear the connection
Set rs = Nothing
'Close the Recordset
cnn.Close
Set cnn = Nothing

End Sub

NOTE: TheseProjects is a variable created in another procedure
"John" wrote in message
om...
How does one use SQL with worksheets? Please give an example of how
you can use a select statement on a database in a worksheet and then
display the result in a another worksheet.

Than you