1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

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