Losing header row when importing to Excel using ADO

matt_d

New Member
Joined
Feb 16, 2005
Messages
9
Hi all,

I'm trying to use ADO to import the results of an SQL query into and Excel spreadsheet. All works fine except it's not pulling the header row back. From browsing the the net it seems I need a 'HDR=Yes' somewhere but I can't suss out where. Can anyone help? Code below:

Part one
Code:
Sub GetOrderDetail()
    
    Dim userid As String
    Dim password As String
    Dim SQLS As String
    Dim destination As String
    
    Worksheets("Order Sheet DETAIL").Range("B8:S20009").ClearContents
              
    userid = "NONFOODFIN"
    password = "nonfoodfin"
        SQLS = " exec DXWI_PROD_NONFOODFIN_PLAY_PEN.UR77_FWK06_Order_Detail; "
    destination = [DownloadOrderDetail].Address
            
    Call ADOExecDataGet(userid, password, SQLS, destination)
           
End Sub

Part two (the ADO bit)
Code:
Sub ADOExecDataGet(userid, password, SQLS, destination)

  Dim Rs As New ADODB.Recordset
  Dim TerConnection As New ADODB.Connection
    
    'ensure RecordSet and Connection are closed
    On Error Resume Next
    Rs.Close
    
    On Error Resume Next
    TerConnection.Close

    'ODBC Driver for Teradata
    myUsername = userid
    myPassword = password
    TerConnection.Open "DSN=DW Master;" & _
                       "Uid=" & userid & ";" & _
                       "Pwd=" & password & ";"

    TerConnection.CommandTimeout = 0
    Set Rs = TerConnection.Execute(SQLS)
    'Rs.Close
    'TerConnection.Close
    
    'Import data to destination cell
    Range(destination).CopyFromRecordset Rs
         
End Sub

Thanks in advance,
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
Hi Matt

I can think of a way of doing it - is 'destination' a single cell? Eg like B1 or something?

Best regards

Richard
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
Try this amended code (only the second sub has changed):

Code:
Sub ADOExecDataGet(userid, password, SQLS, destination) 

  Dim Rs As New ADODB.Recordset 
  Dim TerConnection As New ADODB.Connection 
  Dim i as Integer
    
    'ensure RecordSet and Connection are closed 
    On Error Resume Next 
    Rs.Close 
    
    On Error Resume Next 
    TerConnection.Close 

    'ODBC Driver for Teradata 
    myUsername = userid 
    myPassword = password 
    TerConnection.Open "DSN=DW Master;" & _ 
                       "Uid=" & userid & ";" & _ 
                       "Pwd=" & password & ";" 

    TerConnection.CommandTimeout = 0 
    Set Rs = TerConnection.Execute(SQLS) 
    'Rs.Close 
    'TerConnection.Close 
    
'Import Headers
For i = 0 to Rs.Fields.Count-1
   Range(destination).Offset(0,i) = Rs.Fields(i).Name
Next i
    'Import data to destination cell 
    Range(destination).Offset(1,0).CopyFromRecordset Rs 
          
End Sub

Note this does rely on Destination being a single-celled reference such as $A$2 (not $A$2:$Z$1000).

Best regards

Richard
 

Forum statistics

Threads
1,136,354
Messages
5,675,291
Members
419,559
Latest member
BraytonM

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top