VBA Save Range to Text Issue:

[jamesmev]

I am using a VBA macro to save a range as text.
It was working, but when i expanded my range it started saving the end of the range at the front as well as at the end. I am hoping for some direction on this. If someone can help with the xldown on the range too that would be amazing.

Sub save_CRM()
    Dim filename As String, lineText As String
    Dim myrng As Range, i, j
    Set Cell = Worksheets("Form").Range("D15")
    filename = ThisWorkbook.Path & "\CRM Upload-" & Format(Now, "mm_dd_yy- hh_mm_ss") & Cell.Value & ".txt"
    
    Open filename For Output As #1
    
    Set myrng = Sheets("Sheet1").Range("AY3:CX9999") ' need to fix for xldown 
    
    
    For i = 1 To myrng.Rows.Count
        For j = 1 To myrng.Columns.Count
            lineText = IIf(j = 1, "", lineText & vbTab) & myrng.Cells(i, j)
        Next j
        Print #1, lineText
    Next i
    
    Close #1
    Sheets("Form").Activate
    Application.ScreenUpdating = True
     MsgBox "Completed - Please check your file location"
End Sub

example of results:
SHOULD BE: (JUST EXAMPLE)
1 2 3 4 5 6 7 8 9 10
What I am getting:
9 10 1 2 3 4 5 6 7 8 9 10

 

[Marc L]

A VBA demonstration for starters :​

Sub Demo1()
      Const S = "Sheet1!AY"
        Dim F%, Rw As Range
    With Sheets("Form")
        F = FreeFile
        Open ThisWorkbook.Path & "\CRM Upload-" & Format(Now, "mm_dd_yy- hh_mm_ss") & .[D15].Text & ".txt" For Output As #F
    For Each Rw In Range(S & "3:CX" & Range(S & Rows.Count).End(xlUp).Row).Rows
        Print #F, Join(Application.Index(Rw.Value2, 1, 0), vbTab)
    Next
        Close #F
       .Activate
    End With
        MsgBox "Completed - Please check your file location"
End Sub

 

[JEC]

Also

Sub jec()
   Dim c00 As String, ar As Variant, i As Long
   c00 = ThisWorkbook.Path & "\CRM Upload-" & Format(Now, "mm_dd_yy- hh_mm_ss") & Sheets("Form").[D15].Value & ".txt"
   ar = Sheets("Sheet1").Range("AY3:CX" & Sheets("Sheet1").Range("AY" & Rows.Count).End(xlUp).Row)
   
   With CreateObject("scripting.filesystemobject").createtextfile(c00)
     For i = 1 To UBound(ar)
       .write Join(Application.Index(ar, i, 0), vbTab) & vbCrLf
     Next
   End With
End Sub

 

[jamesmev]

@JEC and @Marc L. I tested both versions - Firstly thank you both for fixing the XLdown issue.
However, I'm still getting the carry over from the final columns in my text file.
The final 2 columns on my sheet are appearing as the first items on my txt file and also as the last.

 

[JEC]

Weird thing, could you post some sample data?

 

[jamesmev]

Weird thing, could you post some sample data?

The width is limited here - when pasting from the data it doesn't show fully. The data from sheet one has 52 Columns.
When exporting. Column 51 and 52 are showing in the correct place.
However, they are also showing as if they are on column 1 and 2 in the output. I initially thought it was due to max character. However, it wouldn't show 51/52 correctly at all if that were the issue. .

 

[JEC]

That could be the limit of the textfile (width)
What if you run the code like this?

Sub jec()
   Dim c00 As String, ar As Variant, i As Long
   c00 = ThisWorkbook.Path & "\CRM Upload-" & Format(Now, "mm_dd_yy- hh_mm_ss") & Sheets("Form").[D15].Value & ".txt"
   ar = Sheets("Sheet1").Range("AY3:CX" & Sheets("Sheet1").Range("AY" & Rows.Count).End(xlUp).Row)
   
   With CreateObject("scripting.filesystemobject").createtextfile(c00)
     For i = 1 To UBound(ar)
       .write Join(Application.Index(ar, i, 0)) & vbCrLf
     Next
   End With
End Sub

 

[Marc L]

The final 2 columns on my sheet are appearing as the first items on my txt file and also as the last.

Issue from your data as our demonstrations do not duplicate any column !​

Check at least the columns range.​

You can link your workbook on a files host website like Dropbox …​

​

Another point : how many rows ?​

'Cause the way JEC uses the worksheet function INDEX : the more rows, the slower. (Ok with my way …)​