I am working in Office 2003, and have created a form and procedures for a
user to create a "Data dump" from a report in Access 2003 to Excel. The
"data dump" is based on a 2 table SQL statement.
I WANT a flat file but I am getting an "outline" file; One level of the
outline is from 1 table and the other is from the second table (This used to
produce what I want in Office 2000.) I cannot figure out how to create the
"flat file" within Excel.

1) How can I create the "flat file" once the data gets to Excel? IS there a
setting I can change?
2) Is there an argument I can add to my VBA code in Access that will
produce a real "flat file"?

Thanks in advance-

Loralee

--
Loralee

In your VBA code in Access, you can use a TransferText command to export the
output of the query that provides data for the report to a text file. For
example,

'Export as delimited text file
DoCmd.TransferText TransferType:=acExportDelim, _
TableName:="MyQuery", FileName:="D:\Data\MyFile.txt", _
HasFieldnames:=True

You can export to a fixed-width text file but you will have to create an
export specification. For example,

DoCmd.TransferText TransferType:=acExportFixed, _
SpecificationName:="MyQuery Export Spec", _
TableName:="MyQuery", _
FileName:="D:\Data\MyFile1.txt", _
HasFieldnames:=True

Hope this helps,

Hutch

"Loralee" wrote:

I am working in Office 2003, and have created a form and procedures for a
user to create a "Data dump" from a report in Access 2003 to Excel. The
"data dump" is based on a 2 table SQL statement.
I WANT a flat file but I am getting an "outline" file; One level of the
outline is from 1 table and the other is from the second table (This used to
produce what I want in Office 2000.) I cannot figure out how to create the
"flat file" within Excel.

1) How can I create the "flat file" once the data gets to Excel? IS there a
setting I can change?
2) Is there an argument I can add to my VBA code in Access that will
produce a real "flat file"?

Thanks in advance-

Loralee

--
Loralee

Tom-

Thanks! I'll be able to make use of this (creating delimited files and
fixed space txt files for other portions of this project. (It works great- )

And you got me to look at some of the other export methods. In this
situation, the users who have to do this will go "blurry eyed" if they are
faced with a text file. This creates the excel file and even opens Excel-
(so they have their info in a format they can use).

DoCmd.OutputTo acOutputQuery, "qryHealthReferralLine2", acFormatXLS, , True

Thank you so much!



--
Loralee


"Tom Hutchins" wrote:

In your VBA code in Access, you can use a TransferText command to export the
output of the query that provides data for the report to a text file. For
example,

'Export as delimited text file
DoCmd.TransferText TransferType:=acExportDelim, _
TableName:="MyQuery", FileName:="D:\Data\MyFile.txt", _
HasFieldnames:=True

You can export to a fixed-width text file but you will have to create an
export specification. For example,

DoCmd.TransferText TransferType:=acExportFixed, _
SpecificationName:="MyQuery Export Spec", _
TableName:="MyQuery", _
FileName:="D:\Data\MyFile1.txt", _
HasFieldnames:=True

Hope this helps,

Hutch

"Loralee" wrote:

I am working in Office 2003, and have created a form and procedures for a
user to create a "Data dump" from a report in Access 2003 to Excel. The
"data dump" is based on a 2 table SQL statement.
I WANT a flat file but I am getting an "outline" file; One level of the
outline is from 1 table and the other is from the second table (This used to
produce what I want in Office 2000.) I cannot figure out how to create the
"flat file" within Excel.

1) How can I create the "flat file" once the data gets to Excel? IS there a
setting I can change?
2) Is there an argument I can add to my VBA code in Access that will
produce a real "flat file"?

Thanks in advance-

Loralee

--
Loralee