extract data from sqlplus into excel sheet
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=DATABASEN AME;"
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
"StevenM" wrote in message
...
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
|