Manually building text files

[UniqueTII]

I have a TransferText macro setup that builds a text file that I need (fixed width) but I need to add a header and footer of the same width, but with information that doesn't fit into the fields. Is there any way to do this?

 

[The_Doc_Man]

You could possibly do this:

Create the file via TransferText but not with the final name.

Then write some VBA code to

1. Open the final file with the correct attributes. (OPEN statement in the Help files)

2. Write the header you want. (WRITE statement in the help files)

3. Open the transferred-text file.

4. In a loop, read the text file (Input Line) and write each line. Set a trap for End of File on input. INPUT LINE statement, ONERROR statement in help files.

5. Write the footer you want.

6. Close the final file.

7. Kill the intermediate file. (Help keyword is 'KILL Statement')

Now, if you were comfortable with traversing recordsets and the required formatting issues, then you wouldn't have to use the TransferText method at all. You could just loop through the recordset of the query/table you wanted, write each line (in step 4 in the above list), and dispense with the intermediate text file entirely.

 

[UniqueTII]

Ok, I did what you said, except for the EOF trap. I couldn't figure out how to code it for the end of the file, so I wrote some cheater code. Here's what I have:

Private Sub Command9_Click()
On Error GoTo Err_Command9_Click
Open "H:\sfina\groups\FINAID\SCHL_DOC\ICSAC\gs001869temp.txt" For Input As #1
Open "c:\ICSAC\gs001869.txt" For Output As #2
Print #2, "@H001869         'header shortened   "
Do While 1 = 1
Line Input #1, x
Print #2, x
Loop
Err_Command9_Click:
    Print #2, "@T001869        'footer shortened       "
    Close #1
    Close #2
    End Sub

What it does is it throws an error when it gets to the end of the file, so that's when I have the footer put in. It's a terrible way to code, so I am hoping someone could show me a better way.

 

[The_Doc_Man]

Well....

Actually, if this isn't something you have to run very often, your approach isn't as ugly as you might think. Even if it IS run with some degree of regularity, you might leave it close to this if there is nothing you could do about any other file error when it occurs.

The only thing I would have done different is that I would have done a resume to a common exit label rather than a direct exit from the error handler. And that, my friend, is a matter of style, not a big technical issue. (Unless your real name is Nicklaus Wirth, perhaps.)

The "real" way to do this only would involve having the error handler code test the Err.Number property to see if it is the end of file condition. I don't recall that number offhand. You could also look at the Err.Description property to get the text equivalent.

The catch is that if you would have stopped on ANY error, why bother to find out which error it is? Perhaps just log the event in a different file using Err.Description for the text of your log record. Put CStr( Now()) in the line, plus the error description, and you have a rudimentary log file that you could check when this code exists.

 

[UniqueTII]

It's only going to be run once a month at most, and I can double check it for the proper number of records, so I'll probably just leave it as is.

Thanks for the help.

BTW, input past end of file is error number 62. I think I will fix it after all.