Here is a scenario that plays out often in my experience:
Suppose you have a query that you would like to export to Excel, and let’s further suppose the query has, say, 150 columns. You can use the CopyFromRecordset() function (https://msdn.microsoft.com/en-us/library/Aa165427(v=office.10).aspx an awesome built-in function in Excel that takes your data and handily pastes it into a spreadsheet) but it does NOT paste the field names into the column headers, so the result is a table with no defined columns.
You need a method of getting those column headers into the spreadsheet before you paste the data.
It is a relatively simple matter to write a few lines of code to loop through the fields in the RecordSet and write those fields to the spreadsheet, and that will work just fine in most cases – but if it happens that your application performs this task on multiple different data sets? Some applications I have developed have a variety of forms that the users want to be able to export to Excel so they can more easily sort and filter, delete, edit, etc. without affecting live data. WriteFieldsToExcel() is a very simple reusable function that saves me the trouble of writing all that For…Each code every time.
I have broken it down into two functions in my application to accommodate both ADO and DAO recordsets. If you wish to have both, too, simply create a second function WriteFieldsToExcelADO() and reference the appropriate recordset type in the Declarations.
The function is super simple - it just loops through the field names in the recordset and writes them column by column as the loop variable i increases. Enjoy, and happy coding!
' Writes field names from a given recordset to an Excel spreadsheet
Public Sub WriteFieldsToExcel(objXL As Object, rst As DAO.Recordset)
Dim fld As DAO.Field
Dim i As Long
For Each fld In rst.Fields
objXL.Selection.Offset(0, i) = fld.Name
i = i + 1