Fixed width txt file with dynamic header and footer

[craig1988]

Hi all

I found a link that has done what I needed to get done.

http://www.access-programmers.co.uk/...d.php?t=168031

One last problem, that Im sure is fixable is that I need to pad out certain columns with trailing spaces. See below.

What I am currently getting.
30151942788001 010011100 BLUESTONE SALES &DISTRIBUTION LTD.26 OAKTREE BUSINESS PARK
30151971666001 010011100 GmchequesUNIT 1 & 2
30151919069001 010011100 MR. BRIAN MURRAY M.D.23A MOYLE ROAD

What I need to get
30151942788001 010011100 BLUESTONE SALES &DISTRIBUTION 26 OAKTREE BUSINESS PARK
30151971666001 010011100 Gmcheques UNIT 1 & 2
30151919069001 010011100 MR. BRIAN MURRAY M.D. 23A MOYLE ROAD

The print method I am using appears to print to txt file in a delimited style which I dont want.

Any help would be greatly appreciated

Here is the code I have so far...

Private Sub btnRunOrders_Click()
    Dim StrFileName, StrPath As String
    
    StrFileName = "Bray.txt"
    
    StrPath = "U:\"
    
    Open StrPath & StrFileName For Output As #1
    
    Print #1, "This is a dynamic header" & "9999999"
    
    Dim Rs As DAO.Recordset
    Set Rs = CurrentDb.OpenRecordset("tblBrayOrder")
        Do Until Rs.EOF
        Print #1, Rs("SortCode") & Rs("AccNo") & Rs("Static1") & Rs("Static2") & Rs("Static3") & Rs("Style") & Rs("Static2") & Rs("Space1") & Rs("Address1") & Rs("Address2")
        Rs.MoveNext
        Loop
        Close #1
        Rs.Close
        Set Rs = Nothing
End Sub

[rpeare]

What you're showing is not fixed width, fixed width means all columns use the same number of spaces so the next column always starts on the same column.

i.e.:

30151942788001  010011100  BLUESTONE SALES &DISTRIBUTION         26 OAKTREE BUSINESS PARK
30151971666001  010011100  Gmcheques                             UNIT 1 & 2
30151919069001  010011100  MR. BRIAN MURRAY M.D.                 23A MOYLE ROAD

If this is what you actually want you will want to define the column width for each field then pad it with spaces:

Private Sub btnRunOrders_Click()
    Dim StrFileName, StrPath As String
    
    StrFileName = "Bray.txt"
    
    StrPath = "U:\"
    
    Open StrPath & StrFileName For Output As #1
    
    Print #1, "This is a dynamic header" & "9999999"
    
    Dim Rs As DAO.Recordset
    Set Rs = CurrentDb.OpenRecordset("tblBrayOrder")
        Do Until Rs.EOF
        Print #1, left(Rs("SortCode") & string(16, " "), 16) & left(Rs("AccNo") & string(11, " "), 11) & left(Rs("Static1")  & string(32, " "), 32) >>>----> and so on
        Rs.MoveNext
        Loop
        Close #1
        Rs.Close
        Set Rs = Nothing
End Sub

[craig1988]

Hi rpeare

Kudos on solving my problem despite my incorrect description of my problem!! That is exactly what I needed. I have one more issue before I can put this into action.

I have two fields in the above that are serial numbers. I have them formatted as "000000" in their table to give the serial number its leading zeros. When I use the print method it does not seem to take that formatting with it. I presume I have to format them through VB. I am unsure how to do this. Any ideas??

Thanks for your help to date!!

[rpeare]

use the format command in your code as well

... & left(format(Rs("SerialNumber"), "000000") & string(11, " "), 11) & ....