using vbNewLine when writing a file and split one line into two lines-CodePudding

I need some help with a fixed width format file. Trying to create a file format that has two alternating rows - record type AA and record type BB

input file looks like this:

input file

sample output file:

AA000022018110112345678D9SDRWSANON8602P              000005000ADSK      SRO     PN  0001080000000                                                                                                      
BB0000220181101                        ARPI                                                                                                                                                            
AA000032018110112345678D9BDRWSANON8602H              000005000ADSK      SRR     PN  0001080000000                                                                                                      
BB0000320181101

the code that writes the file is as follows; trying to figure out where to put the & vbNewLine & code so that it splits up the file into two rows.

Sub exportBD()
 Dim UsedRows As Long
 Dim UsedColumns As Long
 Dim lRow As Long
 Dim i As Long, j As Long
 Dim rng As Range
 
 Open Environ("userprofile") & "\Dfile" & Format(Date, "mmddyy") & "." & Format(TIME, "hhmm") & ".txt" For Output As #1
 
 With shtBD
 
 'Header
 
 lRow = .Range("A" & .Rows.Count).End(xlUp).row
 Set rng = .Range(.Cells(lRow, 1), .Cells(lRow, 2))
  
 UsedRows = .UsedRange.Rows.Count
 UsedColumns = .UsedRange.Columns.Count
 
Set Header = shtBD.Range("A2:F2")

Print #1, Join(Array(shtBD.Range("A2"), shtBD.Range("B2"), shtBD.Range("C2"), shtBD.Range("D2"), shtBD.Range("E2"), shtBD.Range("F2")))
 
 For i = 3 To UsedRows
     For j = 1 To UsedColumns - 1
        Print #1, .Cells(i, j); " ";
    Next j
 Print #1, .Cells(i, UsedColumns)
 Next i
 End With
 
Print #1, "UTRL     " & UsedRows - 2
 
  Close #1
  
 MsgBox "Finished...", vbInformation
End Sub

where would i put in the & vbNewLine so that output shows up in two lines? i know it's somewhere in and around the 'for..next' loops towards the bottom, but not quite sure.

CodePudding user response：

For i = 3 To UsedRows
    For j = 1 To UsedColumns - 1
        ' <-- you would either add it here, or within-, or after the next line:
        Print #1, .Cells(i, j); " ";
    Next j
    Print #1, .Cells(i, UsedColumns)
Next i

But most likely a neater approach would be define a separator variable, near the top of your function (where all the other Dim statements are):

Dim sColumnSep as String

And then doing something like

For i = 3 To UsedRows
    For j = 1 To UsedColumns - 1
        If j = 99999 Then
            sColumnSep = VbCrLf;
        Else
            sColumnSep = " ";
        End If
        Print #1, .Cells(i, j) & sColumnSep;
    Next j
    Print #1, .Cells(i, UsedColumns)
Next i

Where 99999 should be the number for the last column prior to the newline (not sure what the right value is, most likely 5).

CodePudding user response：

thanks for your help. was able to fix the code by using:

 For i = 3 To UsedRows
     For j = 1 To UsedColumns - 1
        If j = 39 Then
            sColumnSep = vbNewLine
        Else
            sColumnSep = " "
        End If
        
        Print #1, .Cells(i, j) & sColumnSep;
           
     Next j
         
    Print #1, .Cells(i, UsedColumns) & " "

 Next i