Go4Expert

Go4Expert (http://www.go4expert.com/)
-   Oracle (http://www.go4expert.com/forums/oracle-forum/)
-   -   Transferring multi-column query result into MS Excel (http://www.go4expert.com/forums/transferring-multi-column-query-result-t3416/)

Rabi 15Mar2007 17:41

Transferring multi-column query result into MS Excel
 
Hello everybody!

An ultimate novice in Oracle, with some database concepts, is here.
The very first challenge; which I encoutered is that I want to transfer a Select query result, with multiple columns, into MS Excel sheet in a way that each field occupies a separate column in the sheet.

I hope the forum members will show me a simple way to get around the problem.

Thanks in anticipation.

Rabi

SIKO 2Nov2007 15:40

Re: Transferring multi-column query result into MS Excel
 
you need to use macros script in the Excel. open excel and add a visual basic button in the excel sheet then on the event click of this button u will view the macros where to add the script add the following:
Code:

    Dim oCon As New ADODB.Connection
    Dim rrs As New ADODB.Recordset
    Dim sSql as String
    Dim i as integer
   
    oConn.Open "DatabaseName", "username", "password"
   
                sSql = "Your Select Statement Here"

            rrs.Open sSql, oCon
               
            i = 0
            While rrs.EOF = True
        i = i + 1       
                Sheet1.Cells(i, 1) = rrs("firstcolunmnamefromsql")
                Sheet1.Cells(i, 2) = rrs("secondcolunmnamefromsql")
                Sheet1.Cells(i, 3) = rrs("thirdcolunmnamefromsql")    ' that is if ur slq returns 3 colunms if more then add more colunms like
        Sheet1.cells(i,4)  = rrs("fourthcolunmnamefromsql")
                                       
            Loop
           
            rrs.Close
           
    oCon.Close
    Set oCon = Nothing
    MsgBox "Done."


                ' firstcolunmnamefromsql: that means if ur first colunm name return from ur sql is Name then this will be rrs("Name")


Das2008 12Jun2008 22:59

Re: Transferring multi-column query result into MS Excel
 
Hi Siko,

Can we do this(Transferring multi-column query result into MS Excel) with out using VBA & merely in Excel ?

I need to populate values(from Oracle) in to column B's each cell referencing column A's each cell.

I must use references of all cells of column 'A' & get values to corresponding cells of column 'B'. I am able to create parameter query for one cell at a time!

Using VBA is restricted, thus I'm looking for a way to do it in merely excel.
Please advise me the best way to solve this.

Thanks,
Das


All times are GMT +5.5. The time now is 03:40.