You could use this code. This use ODBC to connect to an Oracle database and copies the entire recordset into the worksheet.
Dim db as database
Dim rs as recordset
Dim SQLSt as string
Set db = OpenDatabase(“ODBCDSNName”, False, False, “ODBC;DSN=ODBCDSNName;ConnectString=OracleHostName;UID=OracleUserName;PWD=OraclePassword”)
sqlSt = “SELECT col1, col2, … FROM Table1 WHERE Col1 = ‘” & SomeVariable & “‘ ORDER BY Col1, Col2, …;”
Set rs = db.OpenRecordset(sqlSt)
Range(“WorkSheetName”).Cells(1, 1).CopyFromRecordset rs
If you need to “push” it from Oracle (as a result of a nightly job, for example), the only way I have found is to use the Oracle UTL_FILE package to write the data as CSV records.
Just make sure to “wrap” the individual columns with quotes to avoid embedded commas and other special characters from messing up the import. Also, if your data includes numeric columns with leading zeros, you need to set the Excel column type to “TEXT”, or Excel will happily discard those leading zeros.
UTL_FILE is a pretty simple package to use. Not as simple as connecting from Excel as in the above example, but it is easy to write CSV files from Oracle with UTL_FILE.
I ran across a good PL/SQL based tool that allows you to take a cursor and feed it into a stored procedure, and get a CLOB back as a valid OOXML Excel Spreadsheet. It seems really flexible. You can specify styles, sheet header and footer, and even formulas.
We are using a trial version of it on a pilot project right now, and really like where it is taking our business solutions. The business people seem to like it too, because we have shown them it becomes part of the software solution, and they get back native Excel files that they can use right away for further presentation and/or manipulation.
The site is <a href=”http://www.peak42solutions.com”>www.peak42solutions.com</a>, and the product name is Excellant. It is a commercial product, but I’ve seen others that are MUCH more expensive.