1. We have moved from vBulletin to XenForo and you are viewing the site in the middle of the move. Though the functional aspect of everything is working fine, we are still working on other changes including the new design on Xenforo.
    Dismiss Notice

Transferring multi-column query result into MS Excel

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

  1. Rabi

    Rabi New Member

    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

    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

    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