Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Extract data to new sheet | Excel Programming | |||
Extract Data by keyword and export to new Excel sheet | Excel Worksheet Functions | |||
Help me, compare 2 sheet and extract the match data into the new sheet. | Excel Discussion (Misc queries) | |||
how to extract data in .txt format from the excel sheet in a fixed length ascii forma | Excel Programming | |||
Best way to extract data on one sheet & populate it on another sheet?????? | Excel Programming |