Access export query as text with header and footer

Question

I've been researching this issue high and low for an answer or at least a template to go by.

I am using MS Access 2007. I need to export a query as a text file with fixed width specifications (already done). The problem(s) I am running into, is that I must have a specific header and footer appended to the export. Header must have current date and trailer must have total items being exported.

I am admittedly in over my head, but usually can stumble along with some VBA code that does something similar.

Can anyone help?

Answer 1

There isn't any way to define extra lines of text in an export.

I assume you are using the TransferSpreadsheet Method to export your query in fixed-width format. That's typically the right approach for generating the fixed-width content, with or without field headers.

But if you want to add lines to the file before and after the data content, then you'll need to open the existing file, create a new file, append the header lines, then append the data from the existing file to the new file, and then append the footer lines, then close both files.

You could use the built-in VBA functions for working with files, but I find the Scripting.Runtime library offers more intuitive, object-oriented ways of working with files.

You'll need to add a reference to the Microsoft Scripting Runtime library in Tools.. References..

Sub EnhanceExportedFile()

  Const exportedFilePath As String = "C:\Foo.txt"
  Const newFilePath As String = "C:\NewFoo.txt"

  Dim fso As Scripting.FileSystemObject
  Dim exportedFile As TextStream
  Dim newFile As TextStream
  Dim rowCount As Long

  Set fso = New Scripting.FileSystemObject

  Set exportedFile = fso.OpenTextFile(exportedFilePath, ForReading, False)
  Set newFile = fso.CreateTextFile(newFilePath, True)

  'Append the date in ISO format
  newFile.WriteLine Format(Now, "yyyy-mm-dd")

  'Append each line in the exported file
  Do While Not exportedFile.AtEndOfStream
    newFile.WriteLine exportedFile.ReadLine
    rowCount = rowCount + 1
  Loop
  'Append the total exported lines
  newFile.WriteLine rowCount

  'Close both files
  exportedFile.Close
  newFile.Close

End Sub

Answer 2

Use a union query. Suppose your query has fields ID (auto number, long), first name, lastname and your tabelname is tablexx. If you have sequential ids it could be something like this:

Create a query. Select 0 as id, format(date(),"dd/mm/yyyy") as firstname, "" as lastname, "" as nextfield etc etc from tablexx order by id;

and a query
Select 9999999999 (much bigger than your expected id) as id, (select count(id) from tablexx) as firstname, "" as lastname, "" as nextfield etc etc from tablexx order by id;

Now do a union of the three. Even blank lines can be put in (id = 1 etc).