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

Answer 2

There is another way to do this. You can add your SQL connection and table data to a sheet. (Excel data connection wizard)

Then use your VBA to delete the data in that Table, and insert your data via code (create SQL connection and query using VBA).

Once your code is successfully deleting the data and requerying your SQL data into that table, you can delete the connection created.

You will be left with a Table with Column headers and your data will now dynamically be brought in and dumped into the table. No column heading VBA required. :)

Answer 3

I am still hoping to come up with a better solution. Not sure why this isn't easier.

'Get Header into an array:

    Dim HeadString As String
    Dim HeadArr as Variant 

    For iCols = 0 To mrs.Fields.Count - 1
    If HeadString = "" Then
        HeadString = mrs.Fields(iCols).Name
    Else
        HeadString = HeadString & "," & mrs.Fields(iCols).Name
    End If

        Next f
 Debug.Print HeadString 
  HeadArr  = Split(HeadString, ",")
 Debug.Print HeadArr (0)