I'm using the following code to write the results of an Oracle query to an Excel worksheet:
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim OracleData As Variant
ActiveSheet.ListObjects("AgeGroup").DataBodyRange.Value = ""
Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
cn.Open ("User ID=USER; Password=PASSWORD; Data Source=DS; Provider=OraOLEDB.Oracle")
rs.CursorType = adOpenForwardOnly
rs.Open ("select * from age_group"), cn
OracleData = Application.Transpose(rs.GetRows)
ActiveSheet.ListObjects("AgeGroup").DataBodyRange.Resize(UBound(OracleData, 1) - LBound(OracleData, 1) + 1, UBound(OracleData, 2) - LBound(OracleData, 2) + 1) = OracleData
Set rs = Nothing
Set cn = Nothing
It works great. However, I had to create the Excel data table "AgeGroup" manually - put in the correct headers and create the data table with the appropriate number of rows. What I would like to do, is create the data table - with column headers - in the procedure, from the recordset, so that if I change the query it will automatically create the appropriate data table. This particular query results in 6 columns and a max of 20 records. If I changed the query to go to a different table - or a join - that results in, say, 100 columns and 4,000 records, I want the data table created to handle that. Can that be done?