Carriage Return Not Needed

[mdrollins83]

I am attempting to write to a txt file in excel from two separate ranges in 2 different sheets. Below is the code that works, but it leaves a carriage return at the end of the txt file that I cannot have when importing into our software. Could anyone be able to help a VBA beginner out with this?

Sub WriteToTextFile()
  Dim fs As New FileSystemObject
  Dim rng1 As Range
  Dim ws As Worksheet
  Set ws = Sheets("1099 Import Header")
  Set txtfile = fs.CreateTextFile(ThisWorkbook.Path & "\1099 Import.txt")
  Set rng1 = ws.Range("List_1099H")
  For Each r In rng1.Cells
      For Each cel In r
         txtfile.Write cel.Value
      Next
      txtfile.Write vbNewLine
  Next
  Dim rng2 As Range
  Dim wr As Worksheet
  Set wr = Sheets("1099 Import Detail")
  Set rng2 = wr.Range("List_1099D")
  For Each r In rng2.Cells
      For Each cel In r
         txtfile.Write cel.Value
      Next
      txtfile.Write vbNewLine
  Next
End Sub

 

[JLGWhiz]

You can try this modification to the code.

For Each r In rng1.Cells
      For Each cel In r
         txtfile.Write cel.Value
      Next
     [COLOR=#FF0000] If Not cel Is Nothing Then[/COLOR]
          txtfile.Write vbNewLine
      [COLOR=#FF0000]End If[/COLOR]
Next

 

[Kenneth Hobson]

Welcome to the forum!

An If() can skip adding the extra newline. e.g.

If r <> rng2.Cells(rng2.Rows.Count, rng2.Columns.Count) Then _
        txtfile.Write vbNewLine
  Next
End Sub

Alternavely, if your ranges are in a column, transpose is needed.

Sub WriteToTextFile()
  Dim fs As New FileSystemObject, txtfile As Object
  Dim a, b, r As Range, fn$
  
  fn = ThisWorkbook.Path & "\1099 Import.txt"
  Set txtfile = fs.CreateTextFile(fn)
  Set r = Sheets("1099 Import Header").Range("List_1099H")
  a = Application.Transpose(r.Value)
  Set r = Sheets("1099 Import Detail").Range("List_1099D")
  b = Application.Transpose(r.Value)
  txtfile.Write Join(a, vbCrLf) & Join(b, vbCrLf)
  txtfile.Close
  Shell "cmd /c " & """" & fn & """", vbNormalFocus
End Sub

 

[mdrollins83]

Kenneth,

I tried your alternate code since it seems to run much faster, but when the two ranges are combined, it puts the last line of "a" and the first line of "b" on the same line.

 

[Kenneth Hobson]

You can concatenate a vbCrLF between the 2 Join()'s to fix that. There are other methods to join 2 arrays rather than just concatenation. The best method depends on how much data you have. Transpose would not be appropriate had the ranges been 2 dimensional for example.