DoCmd.OutputTo acOutputReport, "rptExport", acFormatTXT - HELP

K

Kelvin

Hi, when I open the report 'rptExport' the data is formatted here exactily
how I require it to be. The formatting is nothing complex, just a CRLF at
the end of each row and there are only 20 rows.

The command in the subject line above works fine and creates a text file of
the report. My problem is that extra CRLF are added every 3rd line and I
cannot see how this is happening. I have tried exporting the report to
different targets such as Excel and HTML and these work fine, it would seem
my problem is limited to txt file exports only.

Has anyone come across this previously or know of a solution.

TIA,

Kelvin
 
N

Nikos Yannacopoulos

Kelvin,

I would say this is a bug; it has to do with font size, control height
and detail section height, and can be fixed by playing around with
those, but only through trial and error... and is definitely not a
robust solution! You would be much better off exporting the query that
is the table's recordsource, which doesn't suffer from this problem.

As a last resort, if grouping, subtotals etc are required, you could use
a VBA sub to open the query as a recordset, read the data row by row and
construct the text file line by line.

HTH,
Nikos
 
K

Kelvin

Thanks for the reply Nikos, I figured this might be a bug. I understand you
comments about exporting it from a query and I would prefer this but I need
the data to have a specific header and footer. The problem is that the data
changes every 30 minutes which is when I need to export it.

I think my next step should be to write the VBA code to build the text file
line by line as you suggest however it has been some years since I've done
this, do you know of any good resources detailing some examples? I will of
course do some searches myself.

I appreciate the help,

Kelvin
 
N

Nikos Yannacopoulos

I'm afraid I can't recommend any resource, but the whole thing is fairly
simple; just open the query as a recordset, and loop through the records
exporting one at a time. You can add your header and footer before and
after the loop, respectively. The commands to open/close the file and
add lines of text to it are simply:

Open "C:\SomeFile.txt" For Output As #1

strLine = ......
Print #1, strLine

Close #1

Beware of the file numbering if you open several files simultaneously!

HTH,
Nikos
 
Top