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
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")
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