extract data from sqlplus into excel sheet
 

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

extract data from sqlplus into excel sheet
 

These might give you a start:
http://www.experts-exchange.com/Prog..._21751278.html
http://msdn.microsoft.com/en-us/library/ms524771.aspx

In the Excel VB Editor add a reference to "Microsoft ActiveX Data
Objects...." (whichever version is on your machine)

Tim


"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

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

extract data from sqlplus into excel sheet
 

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

"Patrick Molloy" wrote in message
...
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

extract data from sqlplus into excel sheet
 

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.


"StevenM" wrote:

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