CODEWITHSUNDEEP
ms-accessvba
Michał Witkowski
Michał Witkowski

Reputation: 13

Inserting a line of text at the beginning of an Access export

I would like to export Access query results to a text file with some added string in the first line of exported file.

Specifically, I would like to combine a text string:

 *Abc def

with the Access query results (tab delimited): 

DoCmd.TransferText acExportDelim, "Export_spec", "Export", "C:\export.txt", True, ""

and then save it as a text file.

The text string have to be in the first line of the text file, followed by access query results.

The results should looks like:

*Abc def
Header1 Header2 Header3 Header4 ...
Value1 Value2 Value3 Value4 ...
... ... ... ... ...

Upvotes: 1

Views: 704

Answers (1)

Gord Thompson
Gord Thompson

Reputation: 123584

You will need to

  • export the data to a temporary file,
  • create a new text file,
  • write the string to the new text file, and then
  • append the data from the temporary file to the new file.

One way to accomplish that would be to use a FileSystemObject in code like this

Dim tempExportSpec as String
tempExportSpec = "C:\__tmp\tempexport.txt"
DoCmd.TransferText acExportDelim, "Export_spec", "Export", tempExportSpec, True, ""
Dim fso As New FileSystemObject
Dim finalFile As TextStream
Set finalFile = fso.CreateTextFile("C:\folder\export.txt")
finalFile.WriteLine "*Abc def"
Dim tempExport As TextStream
Set tempExport = fso.OpenTextFile(tempExportSpec, ForReading)
finalFile.Write tempExport.ReadAll
finalFile.Close
tempExport.Close
fso.DeleteFile tempExportSpec

Upvotes: 2

Related Questions