Hi Mikael,
If the heading needs to contain something other than a list of field
names, the simplest way is usually to write the header to a temporary
file, export the data to another, and then concatenate the two.
The function at the end of this message will write a string to a file,
and you can use something like this air code:
Dim strFolder As String
Dim strHdrFile As String
Dim strTempFile As String
Dim strTextFile As String
Dim strHeader As String
Dim lngRetVal As Long
strFolder = "D:\Folder\"
strHdrFile = "HDRTEMP.$$$"
strTempFile = "TXTTEMP.$$$"
strTextFile = "My Export.txt"
'write header
strHeader = "This is the Header"
lngRetVal = WriteToFile(strHeader, _
strFolder & strHdrFile)
'export text
DoCmd.TransferText blah blah, _
strFolder & strTempFile, blah
'concatenate
Shell "COPY """ & strFolder & strHdrFile & """ + """ _
& strFolder & strTempFile & """ """ _
& strFolder & strTextFile & """"
'delete temp files
Kill strFolder & strHdrFile
Kill strFolder & strTempFile
Function WriteToFile(Var As Variant, _
FileSpec As String, _
Optional Overwrite As Long = True) _
As Long
'Writes Var to a textfile as a string.
'Returns 0 if successful, an errorcode if not.
'Overwrite argument controls what happens
'if the target file already exists:
' -1 or True (default): overwrite it.
' 0 or False: append to it
' Any other value: abort.
Dim lngFN As Long
On Error GoTo Err_WriteToFile
lngFN = FreeFile()
'Change Output in next line to Append to
'append to existing file instead of overwriting
Select Case Overwrite
Case True
Open FileSpec For Output As #lngFN
Case False
Open FileSpec For Append As #lngFN
Case Else
If Len(Dir(FileSpec)) > 0 Then
Err.Raise 58 'File already exists
Else
Open FileSpec For Output As #lngFN
End If
End Select
Print #lngFN, CStr(Nz(Var, ""));
Close #lngFN
WriteToFile = 0
Exit Function
Err_WriteToFile:
WriteToFile = Err.Number
End Function
