export plain data to Text file

[DrAvi via AccessMonster.com]

Hello,

I'm trying to perform a simple task of transferring data from a Query to a
text file (Access 2007 Ver).

As long as I'm using the manual export option (External date-Export-Text file)
, I can specify the export option, among them NOT to export the data with
formatting and layout. The result is exactly the content of the Query,
without headers or frames.

I want to perform this task with VBA automation. I tried to use the following
code:
DoCmd.OutputTo acOutputQuery, "MSV", "*.txt", "c:\MSV.txt", True

But the result is a text file that contains header and "frame" around the
text. The third party application that read the info from the Text file,
cannot read it properly this way (BTW, it expects to read ASCII code, if that
brings more ideas of exporting the data).

What is the right way to export the plain data, without headers or frames?

Knd Regards,

DrAvi

 

[PieterLinden via AccessMonster.com]

Hello,

I'm trying to perform a simple task of transferring data from a Query to a
text file (Access 2007 Ver).

As long as I'm using the manual export option (External date-Export-Text file)
, I can specify the export option, among them NOT to export the data with
formatting and layout. The result is exactly the content of the Query,
without headers or frames.

I want to perform this task with VBA automation. I tried to use the following
code:
DoCmd.OutputTo acOutputQuery, "MSV", "*.txt", "c:\MSV.txt", True

But the result is a text file that contains header and "frame" around the
text. The third party application that read the info from the Text file,
cannot read it properly this way (BTW, it expects to read ASCII code, if that
brings more ideas of exporting the data).

What is the right way to export the plain data, without headers or frames?

Knd Regards,

DrAvi

DrAvi,

What if you create your export specification, save it, and then use it with
the OutputTo? Then you should get the export formatted the way you like.

 

[DrAvi via AccessMonster.com]

Hello Pieter and thank you for your answer.

I tried to use your idea, and saved the export task. When manually using it
(External data-Saved exports-My task), it works as I want.

Unfortunately, I don’t see how I'm using it with the OutputTo.
When I'm adding the saved export task name as a template file name as follows,
I'm getting error message 2302 (can't save):
DoCmd.OutputTo acOutputQuery, "MSV", "MS-DOSText(*.txt)", "c:\MSVC.txt", True,
"Export-MSV"

Perhaps OutputTo is expecting the full path of the template file, but I
couldn’t find where it is stored.

Was that the solution you have suggesting? – if I followed it correctly than
it doesn’t work.
I can think of other option, to directly call the saved export task (without
OutputTo), can it be done with VBA?

Kind Regards,
DrAvi

 

[Steve Schapel]

DrAvi,

Note that what you are doing is not a macro. It is in a VBA procedure.

Still, since you are here, I'm sure we can help!

If you do your export manually, via the External Data=>Export=>Text File,
you are taken through the Export Text Wizard. When you get to the last
screen of the wizard, click the 'Advanced' button, and then 'Save As' to
give your export specification a name.

Then, you enter that name in the SpecificationName argument in your code.
However, the core problem is that you are using the wrong method in your
code. You should be using TransferText instead of OutputTo. So, if I
understand your requirements correctly, the code should be like this:
DoCmd.TransferText acExportDelim, "Name of your specification", "MSV",
"C:\MSV.txt", False

 

[DrAvi via AccessMonster.com]

Hello,

Thank you for your answer. I tried it and it works almost as I wanted. The
"problem" with it as that it is adding commas to the lines.

In the meanwhile I thought about another option to solve this issue with
"CreateTextFile" coding – and it works!


Thank you all for your assistance.

DrAvi

 

[Steve Schapel]

DrAvi,

There has to be a delimiter between the field values in the text file. The
export wizard puts commas in there by default, but you can change this if
you like, and then this change would be part of the specification.

Anyway, glad to hear you fould another solution which is adequate to your
needs.