troubleshooting Question

Create Excel data table from ADO recordset

Avatar of bassman592
bassman592Flag for United States of America asked on
* ADOVBAMicrosoft ExcelMicrosoft Office
1 Comment1 Solution191 ViewsLast Modified:
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?
NorieAnalyst Assistant
Join our community to see this answer!
Unlock 1 Answer and 1 Comment.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 1 Comment.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros