Is there a way to Print a range in a for output that is a string variable?

Question

I'm attempting to export this section of an excel sheet into a txt file. n_select determines how many rows have data and need to be exported. I've tried making a Range from n_select, but then it gives me a type mismatch error.

Private Sub Exporter_Click()
    Dim n_personne As Integer
    Dim n_select As String
    Dim user As String
    Dim fileName As String

    n_personne = 1
    user = Environ("username")
    fileName = "Résultats Pêchés"
    
    'DETERMINER LE NOMBRE DE RANGS DONNERS
    Do Until IsEmpty(Worksheets("output").Cells(n_personne, 1).Value) = True
        n_personne = n_personne + 1
    Loop

    'DETERMINER L'INTERVALLE A EXPORTER
    n_select = "A1:" + Cells(n_personne, 5).Address(RowAbsolute:=False, ColumnAbsolute:=False)
    
    Open "C:\Users\" + user + "\Desktop\" + fileName + ".txt" For Output As #1
    Print #1, Range(n_select)
    Close #1
    Call Shell("Explorer.exe ""C:\Users\" + user + "\Desktop\" + fileName + ".txt")
End Sub

Siddharth Rout · Answer 1 · 2020-11-27T16:12:50.123

Few Suggestions

Use Variables, Objects. Makes your life easier. :)
Instead of +, use & for concatenation.
To Bulk write a range to text file, store it in an array and transpose it. This way no loop will be required.
Avoid the loop to find the last row. You may want to see How to find the last row.
When working with rows, avoid the use of Integer. Use Long.

Is this what you are trying?

Option Explicit

Private Sub Exporter_Click()
    Dim ws As Worksheet
    Dim lRow As Long
    Dim rng As Range
    Dim FilePath As String
    Dim fileName As String
    
    '~~> Construct your path and filename
    FilePath = "C:\Users\" & Environ("username") & "\Desktop\"
    fileName = FilePath & "Résultats Pêchés.txt"
    
    '~~> Set your worksheet
    Set ws = ThisWorkbook.Sheets("Output")
    
    With ws
        '~~> Find last row in Col A
        lRow = .Range("A" & .Rows.Count).End(xlUp).Row
        
        '~~> Identify the range that you want to write to text
        Set rng = .Range("A1:A" & lRow)
    End With
    
    Dim rngLines() As Variant
    Dim output As Variant

    '~~> Transfer the range value to a 2D array and join
    rngLines = Application.Transpose(rng.Value)
    output = Join(rngLines, vbCrLf)
   
    '~~> Write to text file
    Open fileName For Output As #1
    Print #1, output
    Close #1
    
    '~~> Launch the file
    Call Shell("Explorer.exe " & Chr(34) & fileName & Chr(34))
End Sub

EDIT

Thank you so much, I really appreciate it. How would I go about copying multiple columns? The data is going to be in the range of A1 to E last row. When I change the variable rng Set rng = .Range("A1:E" & lRow) to that, it gives me an error that there is an invalid procedure or argument. – WhiteStrips 6 mins ago

Is this what you want?

Option Explicit

Private Sub Exporter_Click()
    Dim ws As Worksheet
    Dim lRow As Long
    Dim rng As Range
    Dim FilePath As String
    Dim fileName As String
    
    '~~> Construct your path and filename
    FilePath = "C:\Users\" & Environ("username") & "\Desktop\"
    fileName = FilePath & "Résultats Pêchés.txt"
    
    '~~> Set your worksheet
    Set ws = ThisWorkbook.Sheets("Output")
    
    With ws
        '~~> Find last row in Col A
        lRow = .Range("A" & .Rows.Count).End(xlUp).Row
        
        '~~> Identify the range that you want to write to text
        Set rng = .Range("A1:E" & lRow)
    End With
    
    Dim rw As Variant, col As Variant
    Dim output As String
    
    For Each rw In rng.Rows
        For Each col In rng.Cells
            output = output & col.Value & ","
        Next col
        output = output & vbNewLine
    Next rw
   
    '~~> Write to text file
    Open fileName For Output As #1
    Print #1, output
    Close #1
    
    '~~> Launch the file
    Call Shell("Explorer.exe " & Chr(34) & fileName & Chr(34))
End Sub

Thank you so much, I really appreciate it. How would I go about copying multiple columns? The data is going to be in the range of A1 to E last row. When I change the variable rng `Set rng = .Range("A1:E" & lRow)` to that, it gives me an error that there is an invalid procedure or argument. — WhiteStrips, Nov 27 '20 at 16:04
Yes since you have multiple columns you will get that error on `output = Join(rngLines, vbCrLf)` Updated the above post for multiple columns. YOu may want to refresh it to see it. BTW, you know you can save it as CSV file which is nothing but a text file? — Siddharth Rout, Nov 27 '20 at 16:13
That puts it in a text file, but it puts everything on the same line, then copies the whole line for each row being copied from. I tried using ' For Each rw In rng.Rows For Each col In rng.Cells output = output & col.Value & "," Next col output = output & vbNewLine Next rw' Except that didnt' change anything. Does it have to do with vbNewLine? — WhiteStrips, Nov 27 '20 at 17:48
Use `For Each col In rng.Cells` first and then `For Each rw In rng.Rows` Experiment with it and if you are unsuccessful, let me know and I will update the answer. But I want you to give it a try first... :) — Siddharth Rout, Nov 27 '20 at 20:50
I feel like it's so close, I'm just not able to completely finish it. I managed to make it export each piece of data only once. However, I want to make a new column after every 5 pieces of data. Here is what I changed for the For Each section: ` For Each rw In rng.Cells output = output & rw.Value & "," output = output & vbNewLine Next rw` Would I use transpose for this case? — WhiteStrips, Nov 27 '20 at 23:49
I managed to make it split the first row after the first set of 5 pieces of data: `For Each rw In rng.Cells output = output & rw.Value & " " output = output & vbNewColumn cycle = cycle + 1 If cycle = 5 Then output = output & vbNewLine End If Next rw` I'm sure there's a more efficient way that doesn't need to be manually put in like that to make a new line after every 5 pieces of data though. — WhiteStrips, Nov 28 '20 at 00:05

Is there a way to Print a range in a for output that is a string variable?

1 Answers1