Avatar of bassman592
bassman592
Flag 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
   
    Worksheets("RS").Activate
    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
   
   
    'cleanup
    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?
* ADOVBAMicrosoft ExcelMicrosoft Office

Avatar of undefined
Last Comment
Norie

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Norie

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck