Export Query As Text File Question

[Wayne]

I'm trying to automate the export of a query to a text file using
code. If I export the query manually I get the exact result that I
want i.e. If I select the query then choose File/Export from the menu
bar, choose "Text" as the save format, and then select "fixed width"
as the export format, I end up with a fixed width text file with no
column headers.

I've tried to automate the process using:

DoCmd.OutputTo acOutputQuery, "qry_Test", acFormatTXT, "Test.txt",
True

This gives me the following unwanted issues. The column headers are
exported, the fixed width columns are separated by a vertical line,
and the rows are separated by dashes. Also the resulting text file
appears on the screen. I just want it to save and not appear. What do
I need to do to automate the process and end up with a text file that
is formatted the same as when I use the manual method? i.e no headers,
no separaters and a silent save to the text file.

[banem2]

On Jul 2, 8:44*am, Wayne <[e-mail]:

I'm trying to automate the export of a query to a text file using
code. *If I export the query manually I get the exact result that I
want i.e. If I select the query then choose File/Export from the menu
bar, choose "Text" as the save format, and then select "fixed width"
as the export format, I end up with a fixed width text file with no
column headers.

I've tried to automate the process using:

DoCmd.OutputTo acOutputQuery, "qry_Test", acFormatTXT, "Test.txt",
True

This gives me the following unwanted issues. *The column headers are
exported, the fixed width columns are separated by a vertical line,
and the rows are separated by dashes. *Also the resulting text file
appears on the screen. *I just want it to save and not appear. What do
I need to do to automate the process and end up with a text file that
is formatted the same as when I use the manual method? i.e no headers,
no separaters and a silent save to the text file.

Hi,

You can use following function (modified function of Joe Fellon, MVP)
to export query in fixed width format without headers. Call the
function with: ExportData ("qry_temp"). In my sample I have used table
with 3 fields.

Function ExportData(strExportFile As String)
Dim rs As Recordset
Dim strData As String
Dim intFileNum As Integer

'get file handle and open for output
intFileNum = FreeFile()

'opens the disk file
Open strExportFile For Output As #intFileNum

'open the recordset
Set rs = CurrentDb.OpenRecordset("qry_Test", dbOpenSnapshot)
'the numbered comments show the fixed width positions
With rs
Do Until .EOF
'this will pad the end of the field with spaces
strData = ![text1] & Space(20 - Len(![text1])) '1-20
strData = strData & ![text2] & Space(20 - Len(![text2]))
'21-40
strData = strData & ![text3] & Space(20 - Len(![text3]))
'41-60

'write out to file
Print #intFileNum, strData
.MoveNext
Loop
End With

Close #intFileNum
rs.Close
Set rs = Nothing
End Function

Regards,
Branislav Mihaljev
Microsoft Access MVP

[Salad]

Wayne wrote:

I'm trying to automate the export of a query to a text file using
code. If I export the query manually I get the exact result that I
want i.e. If I select the query then choose File/Export from the menu
bar, choose "Text" as the save format, and then select "fixed width"
as the export format, I end up with a fixed width text file with no
column headers.

I've tried to automate the process using:

DoCmd.OutputTo acOutputQuery, "qry_Test", acFormatTXT, "Test.txt",
True

This gives me the following unwanted issues. The column headers are
exported, the fixed width columns are separated by a vertical line,
and the rows are separated by dashes. Also the resulting text file
appears on the screen. I just want it to save and not appear. What do
I need to do to automate the process and end up with a text file that
is formatted the same as when I use the manual method? i.e no headers,
no separaters and a silent save to the text file.

Instead of OutputTo you might want to consider TransferText. You can
specify whether or not it has column headers and the type of delimiters.

First, click on your query and select from the menu File/SaveAs and
select external file and select Txt as the type. You will then be able
to set the specification for the output. Be sure to click the Advanced
button and save the specification. Once you have your specification
designed you can use it in TransferText.

Lambada
http://www.youtube.com/watch?v=5AfTl5Vg73A

[Wayne]

Thanks to both Branislav and Salad for your replies. For my
particular situation at the moment, I'm going to use Salad's solution
because for me it's simpler.