We help IT Professionals succeed at work.

Create Excel data table from ADO recordset

172 Views
Last Modified: 2018-09-14
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?
Comment
Watch Question

Analyst Assistant
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.