CODEWITHSUNDEEP
ms-accessms-access-2007vba
user793468
user793468

Reputation: 4966

exporting a query output to a text file

I have an access query which I am trying to export to a text file using the following code:

DoCmd.TransferText acExportFixed, "Export Specification", _
    "Test Query", "C:\Users\Documents\TestOutput.txt", True

The issue I am having is: The output file "TestOutput.txt" has the data displayed with fixed width but the column headers are comma delimited. I want the column headers to be fixed width too.

What would column headers not be displayed same as the rest of the data?

Upvotes: 1

Views: 9773

Answers (1)

HansUp
HansUp

Reputation: 97101

AFAICT, that is an unavoidable "feature" of TransferText. It seems to lack any kind of built-in intelligence to say "OK, we're exporting as acExportFixed, so let's examine the column widths defined in Export Specification and output the column headers using those same widths". Instead it just gives the column names as a comma-separated list.

As with everything else in Access, when its default behaviors are unsatisfactory, you can write VBA code to do it your way. 

Const VB_FORREADING = 1
Const VB_FORWRITING = 2
Const cstrFile As String = "C:\Users\Documents\TestOutput.txt"
Const cstrHeaderRow As String = "col1    col2    etc..."
Dim oFSO As Object
Dim oFile As Object
Dim strContents As String

' do TransferText without the field names '
' (HasFieldNames default = False) '
DoCmd.TransferText acExportFixed, "Export Specification", _
    "Test Query", cstrFile 

Set oFSO = CreateObject("Scripting.FileSystemObject")
' read file content into strContents string variable '
Set oFile = oFSO.OpenTextFile(cstrFile, VB_FORREADING)
strContents = oFile.ReadAll
oFile.Close
' re-write file using cstrHeaderRow plus strContents '
Set oFile = oFSO.OpenTextFile(cstrFile, VB_FORWRITING)
oFile.write cstrHeaderRow & vbCrLf & strContents
oFile.Close

Set oFile = Nothing
Set oFSO = Nothing

Upvotes: 2

Related Questions