Link to home
Start Free TrialLog in
Avatar of John Wilkinson
John WilkinsonFlag for United States of America

asked on

Create Excel data table from ADO recordset

I'm using the following code to write the results of an Oracle query to an Excel worksheet:

Sub GetOracleData()
    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
End Sub

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?
Avatar of Norie

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial