Adding a line to DoCmd.TransferText

[alistercruickshanks]

Hi, I am hoping someone can help as I am struggling with this and my knowledge of VB is limited!

We are using the new Royal Mail DMO system that basically imports tab delimited text files for auto printing tables.

Our database has the data, and indeed I can easily export the data in the right folder. but the specification requires an opening line.

I am using this;

DoCmd.TransferText acExportDelim, "", "RM-48-FL", "\\path\.txt", False, ""

RM-48-FL is the query that contains the data that needs to be exported as a text file.

We need to add a line "ADD WTS" at the start.

So for example, the txt file should be;

ADD WTS
1, 2, 3, 4, 5, 6, 7, 8
1, 2, 3, 4, 5, 6, 7, 8
1, 2, 3, 4, 5, 6, 7, 8

If anyone can help as I am stuck!

 

[The_Doc_Man]

If this is a .TXT file output, i.e. Notepad format, then see this link:

http://www.msofficegurus.com/post/How-to-append-text-files-using-VBA.aspx

You would:

1. Export the query to a file as you did, but it would not be the "final" name.
2. Open the desired filename for output as your #2 file.
3. Use a Print statement to write your header to the #2 file.
4. Open the #1 file for input. This will use the name created by step 1.
5. As shown in the link, using a While Not EOF #1 loop, COPY the records of the #1 file to the #2 file, one line at a time.
6. After the EOF in step 5 (for which the link shows explicit code), close both files.
7. Delete the file created in #1 because now you are done with it. VBA has a KILL verb that will do the trick.

Further hint: DO THIS ON A TEST VERSION FIRST.

 

[alistercruickshanks]

It works, thank you so much.

But this has now generated a new question!

The addresses contain new line characters which are splitting the rows. But actually only the first line is probably needed as it looks like Royal Mail auto fills the town.

Will a loop and using left work between these lines.

Do Until EOF(2)
Line Input #2, Data


' ... and append (write) the data into file number 1 (#1)
Write #1, Data
Loop


Thank you

 

[The_Doc_Man]

If you do a LINE input then each read will get everything between line delimiters regardless of any other punctuation. That should preserve the characteristics of the remainder of the file. Note that without the LINE INPUT, your input statements would break at each comma. But with it, they break at each line break.

The only oddity that MIGHT occur is if there is an extraneous non-standard type of line break in the output of the export step, which would cause the LINE INPUT to strip the odd break. Then the WRITE would replace the oddball break with a traditional one. I don't think Access ever does that so I didn't suggest trying to guard against it.

Note that if your file was generated by another source, I wouldn't be 100% certain of the normality of line breaks.

Was that your question or did I misread it?