Heading line in comma separated txt file

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I want to export data as a comma separated txt file. The required output
includes a heading with with a fixed content.

Which is the easiest way to append the heading at the top of the lines of
the database ouput?
 
Use TransferText method. It includes an argument that allows you include
header or not.
 
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
 
Thank you for a very useful answer. My geading is not the same as a field
name list,so your apporach was exactly what I was looking for.
 
Back
Top