Transferring multi-column query result into MS Excel

Discussion in 'Oracle' started by Rabi, Mar 15, 2007.

  1. Rabi

    Rabi New Member

    Joined:
    Mar 15, 2007
    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    0
    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
     
  2. SIKO

    SIKO New Member

    Joined:
    Oct 26, 2007
    Messages:
    8
    Likes Received:
    0
    Trophy Points:
    0
    Occupation:
    Web Application Developper
    Location:
    Mars
    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")
     
    Last edited by a moderator: Nov 2, 2007
  3. Das2008

    Das2008 New Member

    Joined:
    Jun 12, 2008
    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    0
    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
     

Share This Page

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice