extract data from sqlplus into excel sheet

  • Thread starter Thread starter StevenM
  • Start date Start date
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
 
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
 
spotted typo
Range("B4").CopyFromRecirdset rst
should read
Range("B4").CopyFromRecordset rst
 
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.
 
Back
Top