I am using a macro to copy cells from an Excel worksheet to a new text file. The last row of the text file is empty and I can't remove this. The code looks like this:
Dim r As Long
Dim arr As Variant
Dim file As Variant
Set file = CreateObject("Scripting.FileSystemObject").CreateTextFile("c:\temp\returförslag" + " test.exp", True, True)
With ActiveSheet
arr = .Range(.[A1], .UsedRange.Cells(.UsedRange.Cells.Count))
For r = 1 To UBound(arr)
strfile = Join(WorksheetFunction.Index(arr, r), vbTab)
strfile = Replace(strfile, Chr(13), "")
strfile = Replace(strfile, Chr(10), "")
strfile = Replace(strfile, vbcrlf"")
'file.WriteLine Join(WorksheetFunction.Index(arr, r), vbTab)
file.WriteLine strfile
Next
End With
I have tried to use the replace function to remove the carriage return but wunfortunately with no success. Can you please help me out with this annoying problem?
Dim r As Long
Dim arr As Variant
Dim file As Variant
Set file = CreateObject("Scripting.FileSystemObject").CreateTextFile("c:\temp\returförslag" + " test.exp", True, True)
With ActiveSheet
arr = .Range(.[A1], .UsedRange.Cells(.UsedRange.Cells.Count))
For r = 1 To UBound(arr)
strfile = Join(WorksheetFunction.Index(arr, r), vbTab)
strfile = Replace(strfile, Chr(13), "")
strfile = Replace(strfile, Chr(10), "")
strfile = Replace(strfile, vbcrlf"")
'file.WriteLine Join(WorksheetFunction.Index(arr, r), vbTab)
file.WriteLine strfile
Next
End With
I have tried to use the replace function to remove the carriage return but wunfortunately with no success. Can you please help me out with this annoying problem?