VBA Exact text position Text File

[tsampson]

Is there any way to write a macro that saves a spreadsheet as a text file so that the text file format is extremely precise.

Example:

Line 1 will have 5 total charachters spaces. The first charachter will always be a "W" and the remaining four will be numbers (0000 to 9999).
I.E.
W1234

Line 2 will have 200 charachter spaces. The first charachter will always be a "Z". Charachters 2-21 will be a and ID #. Charachter space 22 will be a "J". Charachter 23-34 will be the Customer Number. Charachters 35-42 will be the customer Ship-To. And so on...
I.E.
Z15433465...........J14564853....MAIN2133

(Without the periods though. I had to use those to show the number of spaces between specific areas)
Both of these together would have a text file like this....

W1234
Z15433465...........J14564853....MAIN2133


There is much more that goes into this, but just getting this format off the ground would be a huge help.
Thanks

 

[John_w]

Use the Format function with user-defined string or numeric formats.

The @ character formats a character or a space. I've used the String function to create a string of @'s of the required field length. The '!' character left-justifies the field. Without this, the field is right-justified.

Search for format function in VB Help for more.

Option Explicit
Sub Test()
    Dim dataOutputFile As String
    Dim fileNum As Integer
    Dim dataLine As String
    
    dataOutputFile = "C:\temp\excel\data.txt"
    fileNum = FreeFile
    Open dataOutputFile For Output As #fileNum
    
    dataLine = "W" & Format(1234, "0000")
    Print #fileNum, dataLine

    dataLine = "Z" & Format("15433465", "!" & String(20, "@")) & _
               "J" & Format("14564853", "!" & String(10, "@")) & _
               Format("MAIN2133", "!" & String(8, "@"))
    Print #fileNum, dataLine
               
    Close #fileNum
End Sub