Export sql query results to excel using vba with headers

Question

I need to export a simple SQL query result to excel. I am able to get the results, however the headers are missing, how can I export the headers as well?

This is what i have so far:

Sub Conn2SQL()

Dim cnn1 As New ADODB.Connection
Dim mrs As New ADODB.Recordset

Set cnn1 = New ADODB.Connection
  cnn1.ConnectionString = "driver={SQL Server};server=MyDBServer;uid=MyuserID;pwd=mypassword;database=MyDB"
  cnn1.ConnectionTimeout = 30
  cnn1.Open  

SQry = "use MyDB select * from TableName"

mrs.Open SQry, cnn1

Sheet2.Range("A2").CopyFromRecordset mrs

mrs.Close
cnn1.Close

End Sub

Answer 1

You need to loop through the field names to include the headers. An example from the Microsoft site is below:

For iCols = 0 To oRS.Fields.Count - 1
 Sheet(1).Cells(1, iCols + 1).Value = oRS.Fields(iCols).Name
Next

So to implement in your code, it would be this:

Sub Conn2SQL()

    Dim cnn1 As New ADODB.Connection
    Dim mrs As New ADODB.Recordset
    Dim iCols As Integer

    Set cnn1 = New ADODB.Connection
      cnn1.ConnectionString = "driver={SQL Server};server=MyDBServer;uid=MyuserID;pwd=mypassword;database=MyDB"
      cnn1.ConnectionTimeout = 30
      cnn1.Open

    SQry = "use MyDB select * from TableName"

    mrs.Open SQry, cnn1

    For iCols = 0 To mrs.Fields.Count - 1
        Worksheets("Sheet2").Cells(1, iCols + 1).Value = mrs.Fields(iCols).Name
    Next

    Sheet2.Range("A2").CopyFromRecordset mrs

    mrs.Close
    cnn1.Close

End Sub