[SOLVED] Access - Export pipe delim CSV with headers using VBA

Access - Export pipe delim CSV with headers using VBA

this is the first question I've posted so hopefully don't break any site rules! I have looked for the answer but can't seem to find quite what I'm looking for.

I have a piece of VB code which is exporting data but it's not including the column headers which I need.

I'm using Access 2010, and I'm exporting a pipe delimited CSV file to a folder location of my choice. I'm exporting from a table in access which has been created from a parent table using several queries, so it's basically just a table of results that I'm trying to export in CSV.

Here is the code I'm using that IS exporting as pipe delim CSV but not adding headers;

Private Sub BtnExportCSV_Click()

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim intFile As Integer
Dim strFilePath As String
Dim intCount As Integer
Dim strHold

strFilePath = "I:\Data\test.csv"

Set db = CurrentDb

Set rst = db.OpenRecordset("T_Export_CSV", dbOpenForwardOnly)

intFile = FreeFile


Open strFilePath For Output As #intFile

Do Until rst.EOF
   For intCount = 0 To rst.Fields.Count - 1
    strHold = strHold & rst(intCount).Value & "|"
   Next
   If Right(strHold, 1) = "|" Then
      strHold = Left(strHold, Len(strHold) - 1)
   End If
   Print #intFile, strHold
   rst.MoveNext
   strHold = vbNullString
Loop

Close intFile
rst.Close
Set rst = Nothing

MsgBox ("Export Completed Successfully")

End Sub

Thanks in advance for the help!

Scott

Solution

You need to write the Name-Property of the recordset fields to the file as well.

Insert this into your code to achieve that:

[...]
Open strFilePath For Output As #intFile

If Not rst.EOF Then
   For intCount = 0 To rst.Fields.Count - 1
      strHold = strHold & rst.Fields(intCount).Name & "|"
   Next
   If Right(strHold, 1) = "|" Then
      strHold = Left(strHold, Len(strHold) - 1)
   End If
   Print #intFile, strHold
   strHold = vbNullString
End If

Do Until rst.EOF
[...]