How to include headers from recordset

[dboone25]

Good morning all. Having a few issues with including the headers from a SQL query dataset. The dataset is copied from a databse into an activesheet but I cant seem to include the headers..

My code below:

Private Sub CommandButton1_Click()

        'Declare variables'
        Dim objMyConn As ADODB.Connection
        Dim objMyCmd As ADODB.Command
        Dim objMyRecordset As ADODB.Recordset


        Set objMyConn = New ADODB.Connection
        Set objMyCmd = New ADODB.Command
        Set objMyRecordset = New ADODB.Recordset


        'Open Connection'
        objMyConn.ConnectionString = "Provider=SQLOLEDB;Data Source=NP-DATABASE;Initial Catalog=" & ComboBox1.Value & " ;Integrated Security=SSPI;"


        objMyConn.ConnectionTimeout = 0
        objMyConn.CommandTimeout = 0


        objMyConn.Open


        'Set and Excecute SQL Command from TextBox'
        Set objMyCmd.ActiveConnection = objMyConn
        objMyCmd.CommandText = TextBox1.Value  ' query run from TextBox
                              
        objMyCmd.CommandType = adCmdText


       
       'include headers from recordset
       With objMyRecordset
        For i = 1 To .Fields.Count
            ActiveSheet.Cells(1, i) = .Fields(i - 1).Name
        Next i
        
       End With
        
       
       
       


        'Open Recordset'
        Set objMyRecordset.Source = objMyCmd
        objMyRecordset.Open


        'Copy Data to Excel'
        ActiveSheet.Range("A1").CopyFromRecordset objMyRecordset


End Sub

Appreciate any help with this...many thanks

 

[RoryA]

You're trying to output the headers before you open the recordset, and you then start the data output over the top of the headers anyway. Try this instead:

'Open Recordset'
        Set objMyRecordset.Source = objMyCmd
        objMyRecordset.Open

       'include headers from recordset
       With objMyRecordset
        For i = 1 To .Fields.Count
            ActiveSheet.Cells(1, i) = .Fields(i - 1).Name
        Next i
        
       End With
        'Copy Data to Excel'
        ActiveSheet.Range("A2").CopyFromRecordset objMyRecordset


End Sub

 

[dboone25]

Almost had it in the box...haha...brilliant...thank you so much for this!!