extract data from sqlplus into excel sheet

S

StevenM

I want to be able to run an sqlplus query and take the rows that come back
and put them in some specified spot in the current worksheet. I have
absolutely no idea how to either make the call to sql or how to handle the
returned rows. I would appreciate someone pointing me to somewhere so I can
read and take a shot at doing it. Any suggestions or directions to do this
would be very gratefully received. Thanks in advance,
StevenM
 
P

Patrick Molloy

basically three things
1) open an ADO connection to your database
2) populate a recordset (like a results table in memory) from running a
query against the connection
3) drop the results into a spreadsheet.

1) in the devlopement environment (Alt+F11) set a Reference to Microsoft
ActiveData Objects 2.7 Library

2) in a standard module add the code:-

OPTION EXPLICIT
Sub LoadFromSQL()
Dim db As ADODB.Connection
Dim rst As ADODB.Recordset
Dim SQL As String

ActiveSheet.Cells.Clear

' CREATE AND OPEN A CONNECTION TO THE DATABASE
Set db = New Connection
With db
.CursorLocation = adUseClient
.Open "PROVIDER=MSDASQL;driver={SQL
Server};server=SERVERNAME=;pwd=;database=DATABASENAME;"
End With

'CREATE AND POPULATE THE RECORDSET
Set rst = New Recordset
SQL = "select * from products"
rst.Open SQL, db, adOpenStatic, adLockOptimistic

'DROP RESULTS INTO A SPREADSHEET
Range("B4").CopyFromRecirdset rst

'CLEAN UP
rst.Close
db.Close
Set rst = Nothing
Set db = Nothing

End Sub

this is meant to be simplistic. you could add a loop to gather the fields
named from the recordset and use them as column headings. You can filter the
recordset object. you could set it as the source for a pivot cache and so
on...

I hope this does get you going though
 
P

Patrick Molloy

spotted typo
Range("B4").CopyFromRecirdset rst
should read
Range("B4").CopyFromRecordset rst
 
S

StevenM

Tim and Patrick -
I want to thank you both. You've given me some excellent places and
examples to begin my quest. I appreciate your help.
 
Top